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 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.7 Report readability
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
Read More:
- R-DataCamp-Data Manipulation with dplyr in R
- This function has none of deterministic, no SQL, or reads SQL data in its error records
- Solution of error converting data type varchar to datetime in SQL Server
- Spring data JAP SQL error:17059 SQL State:99999
- Datacamp course: database design
- Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax； check the
- spark SQL Export Data to Kafka error [How to Solve]
- SQL Error (3621): String or binary data would be truncated The statement has been terminated. */
- Django + jQuery get data in the form + Ajax send data
- No data: data: get host by name failed in TCP_ Connect() error resolution
- [MySQL] [serialize] [error record] after modifying data, no data will be returned (in fact, MySQL does not support it)
- How to Fix Sklearn ValueError: This solver needs samples of at least 2 classes in the data, but the data
- “Invalid month” in SQL query
- Inconsistency between adapter data and UI data after dragging recyclerview (data disorder)
- This (code, message, data: null) still exists after importing spring cloud project into Lombok; the data in the project is unrecognized
- SAP SQL error “SQL code: -10692“ occurred while accessing table “ZTXXXX“.
- Usage of NVL in SQL
- circuit_breaking_exception,“reason“:“[parent] Data too large, data for [＜http_request＞]
- In the SQL query statement, add the column that does not exist in the table and set the fixed value of the column
- DB2 SQL ERROR: SQLCODE=-803, SQLSTATE=23505, SQL