1. Revenue, Cost, and Profit
1.1 Revenue (video)
1.2 Revenue per customer
Instruction:
Write the expression for revenue. Keep only the records of user ID 15.

``````-- Calculate revenue
SELECT SUM(meal_price * order_quantity) AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
-- Keep only the records of customer ID 15
WHERE user_id = 15;
``````

1.3 Revenue per week
Instruction:
Write the expression for revenue. Keep only the records of June 2018.

``````SELECT DATE_TRUNC('week', order_date) :: DATE AS delivr_week,
-- Calculate revenue
SUM(meal_price * order_quantity) AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
-- Keep only the records in June 2018
WHERE DATE_TRUNC('month', order_date) :: DATE = '2018-06-01'
GROUP BY delivr_week
ORDER BY delivr_week ASC;
``````

1.4 Cost and Common Table Expressions (CTEs) (video)
1.5 Total cost
1.6 Top meals by cost
Instruction:
Calculate cost per meal ID.Set the `LIMIT` to 5.

``````SELECT
-- Calculate cost per meal ID
meals.meal_id,
SUM(meal_cost * stocked_quantity) AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY meals.meal_id
ORDER BY cost DESC
-- Only the top 5 meal IDs by purchase cost
LIMIT 5;
``````

1.7 Using CTEs
Instruction 1:
Calculate cost per month.

``````SELECT
-- Calculate cost
DATE_TRUNC('month', stocking_date)::DATE AS delivr_month,
SUM(meal_cost * stocked_quantity) AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY delivr_month
ORDER BY delivr_month ASC;
``````

Instruction 2:
Wrap the query you just wrote in a CTE named `monthly_cost`.

``````-- Declare a CTE named monthly_cost
WITH monthly_cost AS (
SELECT
DATE_TRUNC('month', stocking_date)::DATE AS delivr_month,
SUM(meal_cost * stocked_quantity) AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY delivr_month)

SELECT *
FROM monthly_cost;
``````

Instruction 3:
Now that you’ve set up the `monthly_cost` CTE, find the average cost incurred before September 2018.

``````-- Declare a CTE named monthly_cost
WITH monthly_cost AS (
SELECT
DATE_TRUNC('month', stocking_date)::DATE AS delivr_month,
SUM(meal_cost * stocked_quantity) AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY delivr_month)

SELECT
-- Calculate the average monthly cost before September
AVG(cost)
FROM monthly_cost
WHERE delivr_month < '2018-09-01';
``````

1.8 Profit (video)
1.9 Profit per eatery
Instruction:
Calculate revenue per eatery in the `revenue` CTE. Calculate cost per eatery in the `cost` CTE. Join the two CTEs and calculate profit per eatery.

``````WITH revenue AS (
-- Calculate revenue per eatery
SELECT eatery,
SUM(meal_price * order_quantity) AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY eatery),

cost AS (
-- Calculate cost per eatery
SELECT eatery,
SUM(meal_cost * stocked_quantity) AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY eatery)

-- Calculate profit per eatery
SELECT revenue.eatery,
revenue - cost AS profit
FROM revenue
JOIN cost ON revenue.eatery = cost.eatery
ORDER BY profit DESC;
``````

1.10 Profit per month
Instruction:
Calculate revenue per month in the revenue CTE. Calculate cost per month in the cost CTE. Join the two CTEs and calculate profit per month.

``````-- Set up the revenue CTE
WITH revenue AS (
SELECT
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
SUM(meal_price * order_quantity) AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY delivr_month),
-- Set up the cost CTE
cost AS (
SELECT
DATE_TRUNC('month', stocking_date) :: DATE AS delivr_month,
SUM(meal_cost * stocked_quantity) AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY delivr_month)
-- Calculate profit by joining the CTEs
SELECT
revenue.delivr_month,
revenue - cost AS profit
FROM revenue
JOIN cost ON revenue.delivr_month = cost.delivr_month
ORDER BY revenue.delivr_month ASC;
``````

2. User-centric KPIs
2.1 Registrations and active users (video)
2.2 Registrations by month
Instruction 1:
Return a table of user IDs and their registration dates. Order by `user_id` in ascending order.

``````SELECT
-- Get the earliest (minimum) order date by user ID
user_id,
MIN(order_date) AS reg_date
FROM orders
GROUP BY user_id
-- Order by user ID
ORDER BY user_id ASC;
``````

Instruction 2:
Wrap the query you just wrote in a CTE named `reg_dates`. Using `reg_dates`, return a table of registrations by month.

``````-- Wrap the query you wrote in a CTE named reg_dates
WITH reg_dates AS (
SELECT
user_id,
MIN(order_date) AS reg_date
FROM orders
GROUP BY user_id)

SELECT
-- Count the unique user IDs by registration month
DATE_TRUNC('month', reg_date) :: DATE AS delivr_month,
COUNT(DISTINCT user_id) AS regs
FROM reg_dates
GROUP BY delivr_month
ORDER BY delivr_month ASC;
``````

2.3 Monthly active users (MAU)
Instruction:
Select the month by truncating the order dates. Calculate MAU by counting the users for every month. Order by month in ascending order.

``````SELECT
-- Truncate the order date to the nearest month
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
-- Count the unique user IDs
COUNT(DISTINCT user_id) AS mau
FROM orders
GROUP BY delivr_month
-- Order by month
ORDER BY delivr_month ASC;
``````

2.4 Window functions (video)
2.5 Registrations running total
Instruction 1:
Select the month and the registrations in each month. Order by month in ascending order.

``````WITH reg_dates AS (
SELECT
user_id,
MIN(order_date) AS reg_date
FROM orders
GROUP BY user_id)

SELECT
-- Select the month and the registrations
DATE_TRUNC('month', reg_date)  :: DATE AS delivr_month,
COUNT(DISTINCT user_id) AS regs
FROM reg_dates
GROUP BY delivr_month
-- Order by month in ascending order
ORDER BY delivr_month;
``````

Instruction 2:
Return a table of the registrations running total by month. Order by month in ascending order.

``````WITH reg_dates AS (
SELECT
user_id,
MIN(order_date) AS reg_date
FROM orders
GROUP BY user_id),

regs AS (
SELECT
DATE_TRUNC('month', reg_date) :: DATE AS delivr_month,
COUNT(DISTINCT user_id) AS regs
FROM reg_dates
GROUP BY delivr_month)

SELECT
-- Calculate the registrations running total by month
delivr_month,
SUM(regs) OVER (ORDER BY delivr_month ASC) AS regs_rt
FROM regs
-- Order by month in ascending order
ORDER BY delivr_month ASC;
``````

2.6 MAU monitor (1)
Instruction:
Select the month and the MAU. Fetch the previous month’s MAU. Order by month in ascending order.

``````WITH mau AS (
SELECT
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
COUNT(DISTINCT user_id) AS mau
FROM orders
GROUP BY delivr_month)

SELECT
-- Select the month and the MAU
delivr_month,
mau,
COALESCE(
LAG(mau) OVER (ORDER BY delivr_month ASC),
0)  AS last_mau
FROM mau
-- Order by month in ascending order
ORDER BY delivr_month ASC;
``````

2.7 Growth (video)
2.8 MAU monitor (2)
Instruction:
Fetch the previous month’s MAU in the `mau_with_lag` CTE…Select the month and the delta between its MAU and the previous month’s MAU. Order by month in ascending order.

``````WITH mau AS (
SELECT
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
COUNT(DISTINCT user_id) AS mau
FROM orders
GROUP BY delivr_month),

mau_with_lag AS (
SELECT
delivr_month,
mau,
-- Fetch the previous month's MAU
COALESCE(
LAG(mau) OVER (ORDER BY delivr_month ASC),
0) AS last_mau
FROM mau)

SELECT
-- Calculate each month's delta of MAUs
delivr_month,
mau - last_mau AS mau_delta
FROM mau_with_lag
-- Order by month in ascending order
ORDER BY delivr_month;
``````

2.9 MAU monitor (3)
Instruction:
Select the month and its MoM MAU growth rate. Order by month in ascending order.

``````WITH mau AS (
SELECT
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
COUNT(DISTINCT user_id) AS mau
FROM orders
GROUP BY delivr_month),

mau_with_lag AS (
SELECT
delivr_month,
mau,
GREATEST(
LAG(mau) OVER (ORDER BY delivr_month ASC),
1) AS last_mau
FROM mau)

SELECT
-- Calculate the MoM MAU growth rates
delivr_month,
ROUND(
(mau - last_mau) :: NUMERIC/last_mau,
2) AS growth
FROM mau_with_lag
-- Order by month in ascending order
ORDER BY delivr_month;
``````

2.10 Order growth rate
Instruction:
Count the unique orders per month. Fetch each month’s previous and current orders. Return a table of MoM order growth rates.

``````WITH orders AS (
SELECT
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
--  Count the unique order IDs
COUNT(DISTINCT order_id) AS orders
FROM orders
GROUP BY delivr_month),

orders_with_lag AS (
SELECT
delivr_month,
-- Fetch each month's current and previous orders
orders,
COALESCE(
LAG(orders) OVER (ORDER BY delivr_month ASC),
1) AS last_orders
FROM orders)

SELECT
delivr_month,
-- Calculate the MoM order growth rate
ROUND(
(orders - last_orders) :: NUMERIC/last_orders,
2) AS growth
FROM orders_with_lag
ORDER BY delivr_month ASC;
``````

2.11 Retention (video)
2.12 New, retained, and resurrected users
2.13 Retention rate
Instruction:
Select the month column from `user_monthly_activity`, and calculate the MoM user retention rates. Join `user_monthly_activity` to itself on the user ID and the month, pushed forward one month.

``````WITH user_monthly_activity AS (
SELECT DISTINCT
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
user_id
FROM orders)

SELECT
-- Calculate the MoM retention rates
previous.delivr_month,
ROUND(
COUNT(DISTINCT current.user_id) :: NUMERIC /
GREATEST(COUNT(DISTINCT previous.user_id), 1),
2) AS retention_rate
FROM user_monthly_activity AS previous
-- Fill in the user and month join conditions
ON previous.user_id = current.user_id
AND previous.delivr_month = (current.delivr_month - INTERVAL '1 month')
GROUP BY previous.delivr_month
ORDER BY previous.delivr_month ASC;
``````

3. ARPU, Histograms, and Percentiles
3.1 Unit economics (video)
3.2 Average revenue per user
Instruction 1:
Return a table of user IDs and the revenue each user generated.

``````SELECT
-- Select the user ID and calculate revenue
user_id,
SUM(meal_price * order_quantity) AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id;
``````

Instruction 2:
Wrap the previous query in a CTE named `kpi`. Return the average revenue per user (ARPU).

``````-- Create a CTE named kpi
WITH kpi AS (
SELECT
-- Select the user ID and calculate revenue
user_id,
SUM(m.meal_price * o.order_quantity) AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id)
-- Calculate ARPU
SELECT ROUND(AVG(revenue) :: NUMERIC,2) AS arpu
FROM kpi;
``````

3.3 ARPU per week
Instruction:
Store revenue and the number of unique active users by week in the `kpi` CTE. Calculate ARPU by dividing the revenue by the number of users. Order the results by week in ascending order.

``````WITH kpi AS (
SELECT
-- Select the week, revenue, and count of users
DATE_TRUNC('week', order_date) :: DATE AS delivr_week,
SUM(meal_price * order_quantity) AS revenue,
COUNT(DISTINCT user_id) AS users
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY delivr_week)

SELECT
delivr_week,
-- Calculate ARPU
ROUND(revenue :: NUMERIC/GREATEST(users, 1),2) AS arpu
FROM kpi
-- Order by week in ascending order
ORDER BY delivr_week ASC;
``````

3.4 Average orders per user
Instruction :
Store the count of distinct orders and distinct users in the `kpi` CTE. Calculate the average orders per user.

``````WITH kpi AS (
SELECT
-- Select the count of orders and users
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT user_id) AS users
FROM orders)

SELECT
-- Calculate the average orders per user
ROUND(
orders :: NUMERIC/GREATEST(users, 1),
2) AS arpu
FROM kpi;
``````

3.5 Histograms (video)
3.6 Histograms of revenue
Instruction:
Store each user ID and the revenue Delivr generates from it in the `user_revenues` CTE. Return a frequency table of revenues rounded to the nearest hundred and the users generating those revenues.

``````WITH user_revenues AS (
SELECT
-- Select the user ID and revenue
user_id,
SUM(meal_price * order_quantity) AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id)

SELECT
-- Return the frequency table of revenues by user
ROUND(revenue :: NUMERIC, -2) AS revenue_100,
COUNT(DISTINCT user_id) AS users
FROM user_revenues
GROUP BY revenue_100
ORDER BY revenue_100 ASC;
``````

3.7 Histograms of orders
Instruction 1:
Set up the frequency tables query by getting each user’s count of orders.

``````SELECT
-- Select the user ID and the count of orders
user_id,
COUNT(DISTINCT order_id) AS orders
FROM orders
GROUP BY user_id
ORDER BY user_id ASC
LIMIT 5;
``````

Instruction 2:
Return a frequency table of orders and the count of users with those orders.

``````WITH user_orders AS (
SELECT
user_id,
COUNT(DISTINCT order_id) AS orders
FROM orders
GROUP BY user_id)

SELECT
-- Return the frequency table of orders by user
orders,
COUNT(DISTINCT user_id) AS users
FROM user_orders
GROUP BY orders
ORDER BY orders ASC;
``````

3.8 Bucketing (video)
3.9 Bucketing users by revenue
Instruction:
Store each user ID and the revenue it generates in the `user_revenues` CTE. Return a table of the revenue groups and the count of users in each group.

``````WITH user_revenues AS (
SELECT
-- Select the user IDs and the revenues they generate
user_id,
SUM(meal_price * order_quantity) AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id)

SELECT
-- Fill in the bucketing conditions
CASE
WHEN revenue < 150  THEN 'Low-revenue users'
WHEN revenue < 300 THEN 'Mid-revenue users'
ELSE 'High-revenue users'
END AS revenue_group,
COUNT(DISTINCT user_id) AS users
FROM user_revenues
GROUP BY revenue_group;
``````

3.10 Bucketing users by orders
Instruction:
Store each user ID and its count of orders in a CTE named `user_orders`. Set the cut-off point for the low-orders bucket to 8 orders, and set the cut-off point for the mid-orders bucket to 15 orders. Count the distinct users in each bucket.

``````-- Store each user's count of orders in a CTE named user_orders
WITH user_orders AS (
SELECT
user_id,
COUNT(DISTINCT order_id) AS orders
FROM orders
GROUP BY user_id)

SELECT
-- Write the conditions for the three buckets
CASE
WHEN orders < 8 THEN 'Low-orders users'
WHEN orders < 15 THEN 'Mid-orders users'
ELSE 'High-orders users'
END AS order_group,
-- Count the distinct users in each bucket
COUNT(DISTINCT user_id) AS users
FROM user_orders
GROUP BY order_group;
``````

3.11 Percentiles (video)
3.12 Revenue quartiles
Instruction:
Store each user ID and the revenue Delivr generates from it in the `user_revenues` CTE. Calculate the first, second, and third revenue quartile. Calculate the average revenue.

``````WITH user_revenues AS (
-- Select the user IDs and their revenues
SELECT
user_id,
SUM(meal_price * order_quantity) AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id)

SELECT
-- Calculate the first, second, and third quartile
ROUND(
PERCENTILE_CONT(0.25) WITHIN GROUP
(ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p25,
ROUND(
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p50,
ROUND(
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p75,
-- Calculate the average
ROUND(AVG(revenue) :: NUMERIC, 2) AS avg_revenue
FROM user_revenues;
``````

3.13 Interquartile range
Instruction 1:
Return a table of user IDs and generated revenues for each user.

``````SELECT
-- Select user_id and calculate revenue by user
user_id,
SUM(meal_price * order_quantity) AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id;
``````

Instruction 2:
Wrap the previous query in a CTE named user_revenues. Calculate the first and third revenue quartiles.

``````-- Create a CTE named user_revenues
WITH user_revenues AS (
SELECT
-- Select user_id and calculate revenue by user
user_id,
SUM(m.meal_price * o.order_quantity) AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id)

SELECT
-- Calculate the first and third revenue quartiles
ROUND(
PERCENTILE_CONT(0.25) WITHIN GROUP
(ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p25,
ROUND(
PERCENTILE_CONT(0.75) WITHIN GROUP
(ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p75
FROM user_revenues;
``````

Instruction 3:
Count the number of distinct users. Filter out all users outside the IQR.

``````WITH user_revenues AS (
SELECT
-- Select user_id and calculate revenue by user
user_id,
SUM(m.meal_price * o.order_quantity) AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id),

quartiles AS (
SELECT
-- Calculate the first and third revenue quartiles
ROUND(
PERCENTILE_CONT(0.25) WITHIN GROUP
(ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p25,
ROUND(
PERCENTILE_CONT(0.75) WITHIN GROUP
(ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p75
FROM user_revenues)

SELECT
-- Count the number of users in the IQR
COUNT(DISTINCT user_id) AS users
FROM user_revenues
CROSS JOIN quartiles
-- Only keep users with revenues in the IQR range
WHERE revenue :: NUMERIC >= revenue_p25
AND revenue :: NUMERIC <= revenue_p75;
``````

4. Generating an Executive Report
4.1 Survey of useful functions (video)
4.2 Formatting dates
Instruction:
Select the order date. Format the order date so that `2018-06-01` is formatted as `Friday 01, June 2018`.

``````SELECT DISTINCT
-- Select the order date
order_date,
-- Format the order date
TO_CHAR(order_date, 'FMDay DD, FMMonth YYYY') AS format_order_date
FROM orders
ORDER BY order_date ASC
LIMIT 3;
``````

4.3 Rank users by their count of orders
Instruction 1:
Keep only the orders in August 2018.

``````SELECT
user_id,
COUNT(DISTINCT order_id) AS count_orders
FROM orders
-- Only keep orders in August 2018
WHERE DATE_TRUNC('month', order_date) = '2018-08-01'
GROUP BY user_id;
``````

Instruction 2:
Wrap the previous query in a CTE named `user_count_orders`. Select the user ID and rank all user IDs by the count of orders in descending order. Only keep the top 3 users by their count of orders.

``````-- Set up the user_count_orders CTE
WITH user_count_orders AS (
SELECT
user_id,
COUNT(DISTINCT order_id) AS count_orders
FROM orders
-- Only keep orders in August 2018
WHERE DATE_TRUNC('month', order_date) = '2018-08-01'
GROUP BY user_id)

SELECT
-- Select user ID, and rank user ID by count_orders
user_id,
RANK() OVER (ORDER BY count_orders DESC) AS count_orders_rank
FROM user_count_orders
ORDER BY count_orders_rank ASC
-- Limit the user IDs selected to 3
LIMIT 3;
``````

4.4 Pivoting (video)
4.5 Pivoting user revenues by month
Instruction 1:
Enable `CROSSTAB()` from tablefunc.
Declare the new pivot table’s columns, user ID and the first three months of operation.

``````-- Import tablefunc
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB(\$\$
SELECT
user_id,
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
SUM(meal_price * order_quantity) :: FLOAT AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
WHERE user_id IN (0, 1, 2, 3, 4)
AND order_date < '2018-09-01'
GROUP BY user_id, delivr_month
ORDER BY user_id, delivr_month;
\$\$)
-- Select user ID and the months from June to August 2018
AS ct (user_id INT,
"2018-06-01" FLOAT,
"2018-07-01" FLOAT,
"2018-08-01" FLOAT)
ORDER BY user_id ASC;
``````

4.6 Costs
Instruction 1:
Select the eatery and calculate total cost per eatery. Keep only the records after October 2018.

``````SELECT
-- Select eatery and calculate total cost
eatery,
DATE_TRUNC('month', stocking_date) :: DATE AS delivr_month,
SUM(meal_cost * stocked_quantity):: FLOAT AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
-- Keep only the records after October 2018
WHERE stocking_date	 > '2018-10-01'
GROUP BY eatery, delivr_month
ORDER BY eatery, delivr_month;
``````

Instruction 2:
Enable `CROSSTAB` from `tablefunc`. Declare the new pivot table’s columns, the eatery and the last two months of operation.

``````-- Import tablefunc
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB(\$\$
SELECT
-- Select eatery and calculate total cost
eatery,
DATE_TRUNC('month', stocking_date) :: DATE AS delivr_month,
SUM(meal_cost * stocked_quantity) :: FLOAT AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
-- Keep only the records after October 2018
WHERE DATE_TRUNC('month', stocking_date) > '2018-10-01'
GROUP BY eatery, delivr_month
ORDER BY eatery, delivr_month;
\$\$)

-- Select the eatery and November and December 2018 as columns
AS ct (eatery TEXT,
"2018-11-01" FLOAT,
"2018-12-01" FLOAT)
ORDER BY eatery ASC;
``````

4.6 Producing executive reports (video)
4.8 Executive report
Instruction 1:
Fill in the format string that formats `2018-06-01` as `Q2 2018`. Count the ordering users by eatery and by quarter.

``````SELECT
eatery,
-- Format the order date so "2018-06-01" becomes "Q2 2018"
TO_CHAR(order_date, '"Q"Q YYYY') AS delivr_quarter,
-- Count unique users
COUNT(DISTINCT user_id) AS users
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY eatery, delivr_quarter
ORDER BY delivr_quarter, users;
``````

Instruction 2:
Select the eatery and the quarter from the CTE. Assign a rank to each row, with the top-most rank going to the row with the highest orders.

``````WITH eatery_users AS  (
SELECT
eatery,
-- Format the order date so "2018-06-01" becomes "Q2 2018"
TO_CHAR(order_date, '"Q"Q YYYY') AS delivr_quarter,
-- Count unique users
COUNT(DISTINCT user_id) AS users
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY eatery, delivr_quarter
ORDER BY delivr_quarter, users)

SELECT
-- Select eatery and quarter
eatery,
delivr_quarter,
-- Rank rows, partition by quarter and order by users
RANK() OVER
(PARTITION BY delivr_quarter
ORDER BY users DESC) :: INT AS users_rank
FROM eatery_users
ORDER BY delivr_quarter, users_rank;
``````

Instruction 3:
Import the `tablefunc` extension. Pivot the table by quarter. Select the new columns from the pivoted table.

``````-- Import tablefunc
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Pivot the previous query by quarter
SELECT * FROM CROSSTAB (\$\$
WITH eatery_users AS  (
SELECT
eatery,
-- Format the order date so "2018-06-01" becomes "Q2 2018"
TO_CHAR(order_date, '"Q"Q YYYY') AS delivr_quarter,
-- Count unique users
COUNT(DISTINCT user_id) AS users
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY eatery, delivr_quarter
ORDER BY delivr_quarter, users)

SELECT
-- Select eatery and quarter
eatery,
delivr_quarter,
-- Rank rows, partition by quarter and order by users
RANK() OVER
(PARTITION BY delivr_quarter
ORDER BY users DESC) :: INT AS users_rank
FROM eatery_users
ORDER BY eatery, delivr_quarter;
\$\$)
-- Select the columns of the pivoted table
AS  ct (eatery TEXT,
"Q2 2018" INT,
"Q3 2018" INT,
"Q4 2018" INT)
ORDER BY "Q4 2018";
``````

4.9 Course recap