Cumulative revenue per member by join month

SQL data engineering interview problem. Difficulty: advanced. Pattern: Cohort. About 24 minutes. Part of the Pro drill bank.

cohort_members has each member's joined_on date. cohort_orders has the orders they placed, with order_date and amount. Group members into cohorts by the month they joined (cohort_month, the first day of that month). month_number counts calendar months from the joining month: 0 is the joining month, 1 the month after, 2 the month after that. For every cohort and every month_number 0, 1 and 2 for which the calendar month (cohort_month plus month_number months) is April 2024 or earlier, return revenue_per_member: all revenue from the cohort's members from the joining month up to and including that month, divided by the number of members in the cohort, rounded to 2 decimals. Members who never ordered count in the cohort size. A month with no orders still gets a row. Columns: cohort_month, month_number, revenue_per_member. Order by cohort_month, month_number.

Requirements

• Return the columns cohort_month, month_number, revenue_per_member.
• Leave out rows whose calendar month (cohort month plus month number) is after April 2024.
• Order by cohort_month, month_number.

Constraints

• Months are calendar months.
• amount is a whole number.
• Data covers orders up to 30 April 2024.

Examples

Input: cohort_members member_id | joined_on 1 | 2024-01-05 2 | 2024-01-20 3 | 2024-01-28 4 | 2024-01-31 5 | 2024-02-03 6 | 2024-02-14 7 | 2024-02-29 8 | 2024-03-01 9 | 2024-03-15 10 | 2024-04-10 cohort_orders order_id | member_id | order_date | amount 1 | 1 | 2024-01-06 | 50 2 | 1 | 2024-02-10 | 30 3 | 1 | 2024-03-03 | 20 4 | 2 | 2024-01-20 | 100 5 | 3 | 2024-02-01 | 40 6 | 3 | 2024-02-02 | 10 7 | 5 | 2024-02-03 | 60 8 | 5 | 2024-04-04 | 25 9 | 6 | 2024-03-30 | 15 10 | 7 | 2024-02-29 | 70 11 | 8 | 2024-03-01 | 80 12 | 8 | 2024-04-01 | 20 Output: cohort_month | month_number | revenue_per_member 2024-01-01 | 0 | 37.5 2024-01-01 | 1 | 57.5 2024-01-01 | 2 | 62.5 2024-02-01 | 0 | 43.33 2024-02-01 | 1 | 48.33 2024-02-01 | 2 | 56.67 2024-03-01 | 0 | 40 2024-03-01 | 1 | 50 2024-04-01 | 0 | 0 Why this passes: January has four members. They spend 150 in January, 80 more in February and 20 more in March, so the running totals are 150, 230 and 250, each shared over all four members (37.5, 57.5, 62.5). The April cohort has not ordered yet, so its cell is 0.

Input: cohort_members member_id | joined_on 1 | 2024-03-05 2 | 2024-03-09 cohort_orders order_id | member_id | order_date | amount 1 | 1 | 2024-03-06 | 10 2 | 2 | 2024-03-06 | 20 3 | 1 | 2024-04-20 | 30 Output: cohort_month | month_number | revenue_per_member 2024-03-01 | 0 | 15 2024-03-01 | 1 | 30 Why this passes: March totals 30 over 2 members (15 per member); the April order brings the running total to 60, or 30 per member.

Topics: lakebench, sql, cohort, revenue, running total, grid.

More SQL interview questions · All interview problems · Learn data engineering