Revenue quartiles
Dave is wrapping up his study, and wants to calculate a few more figures. He wants to find out the first, second, and third revenue quartiles. He also wants to find the average to see in which direction the data is skewed.
Calculate the first, second, and third revenue quartiles, as well as the average.
Note: You can calculate the 30th percentile for a column named column_a
by using PERCENTILE_CONT(0.30) WITHIN GROUP (ORDER BY column_a ASC)
.
This exercise is part of the course
Analyzing Business Data in SQL
Exercise instructions
- 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.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH user_revenues AS (
-- Select the user IDs and their revenues
SELECT
___,
___ 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(
___ :: ___,
2) AS revenue_p25,
ROUND(
___ :: ___,
2) AS revenue_p50,
ROUND(
___ :: ___,
2) AS revenue_p75,
-- Calculate the average
ROUND(___ :: ___, 2) AS avg_revenue
FROM user_revenues;