SQL-DataCamp-Analyzing Business Data in SQL
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
LEFT JOIN user_monthly_activity AS current
-- 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 asFriday 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
fromtablefunc
. - 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.7 Report readability
4.8 Executive report
Instruction 1:
- Fill in the format string that formats
2018-06-01
asQ2 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";