Unbroken runs of months
hardUnbroken runs of months
Shopify SQL Interview Question
Shopify counts a merchant who left and came back as two relationships rather than one, because the second behaves differently: shorter, more price sensitive, more likely to leave again. The billing history is a row per month a merchant was active, with nothing recorded for the months they were gone.
Return one row per unbroken run of active months: the merchant_id, the first month of the run as run_start, the last month as run_end, and the number of months in it as months_active. Sort by merchant_id and then by run_start.
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 | run_start | run_end | months_active |
|---|---|---|---|
| M01 | 2024-01-01 | 2024-03-01 | 3 |
| M01 | 2024-06-01 | 2024-08-01 | 3 |
| M04 | 2024-01-01 | 2024-04-01 | 4 |
Explanation
M01 paid for three months, left for two, and came back for three more. That is two runs rather than one relationship of six months. M04 paid for four months without a break, so it has a single run.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Shopify
Difficulty
hard
Topic
window functions
Language
SQL
Your query
Starting the SQL engine...