Revenue to date per merchant
mediumRevenue to date per merchant
Shopify SQL Interview Question
Shopify spends to acquire a merchant and earns it back one month at a time. The question that sets the marketing budget is how many months that payback takes. Answering it needs the money a merchant has paid so far at each month of their life, not their lifetime total.
Return the merchant_id, the active_month, that month's subscription revenue as mrr, and everything the merchant has paid up to and including that month as revenue_to_date. Sort by merchant_id and then by active_month.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
shopify_subscriptionsTable26 rows
| Column Name | Type |
|---|---|
| record_id | BIGINT |
| merchant_id | VARCHAR |
| active_month | DATE |
| plan | VARCHAR |
| mrr | DOUBLE |
shopify_subscriptionsExample Input
| record_id | merchant_id | active_month | plan | mrr |
|---|---|---|---|---|
| 1101 | M01 | 2024-01-01 | Basic | 29 |
| 1102 | M01 | 2024-02-01 | Basic | 29 |
| 1103 | M01 | 2024-03-01 | Shopify | 79 |
| 1104 | M01 | 2024-06-01 | Shopify | 79 |
| 1105 | M01 | 2024-07-01 | Advanced | 299 |
| 1106 | M01 | 2024-08-01 | Advanced | 299 |
| 1119 | M04 | 2024-01-01 | Advanced | 299 |
| 1120 | M04 | 2024-02-01 | Advanced | 299 |
| 1121 | M04 | 2024-03-01 | Advanced | 299 |
| 1122 | M04 | 2024-04-01 | Advanced | 299 |
Example Output
| merchant_id | active_month | mrr | revenue_to_date |
|---|---|---|---|
| M01 | 2024-01-01 | 29 | 29 |
| M01 | 2024-02-01 | 29 | 58 |
| M01 | 2024-03-01 | 79 | 137 |
| M01 | 2024-06-01 | 79 | 216 |
| M01 | 2024-07-01 | 299 | 515 |
| M01 | 2024-08-01 | 299 | 814 |
| M04 | 2024-01-01 | 299 | 299 |
| M04 | 2024-02-01 | 299 | 598 |
| M04 | 2024-03-01 | 299 | 897 |
| M04 | 2024-04-01 | 299 | 1196 |
Explanation
M01 pays 29 in its first month, so the running figure starts at 29, reaches 58 after the second month and 137 after the third. The two months it was gone are simply absent, and the total carries straight across the gap.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Shopify
Difficulty
medium
Topic
running totals
Language
SQL
Your query
Starting the SQL engine...