Plan changes
mediumPlan changes
Shopify SQL Interview Question
An upgrade and a downgrade at Shopify go to different teams. An upgrade is a success story the account team follows up on, and a downgrade is a churn risk somebody should call this week. Both are invisible in a billing table until each month is read beside the month before it.
Return every month a merchant was on a different plan than the record before: the merchant_id, the active_month the change took effect, the plan they were on before as previous_plan, and the plan they moved to as new_plan. 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 | previous_plan | new_plan |
|---|---|---|---|
| M01 | 2024-03-01 | Basic | Shopify |
| M01 | 2024-07-01 | Shopify | Advanced |
Explanation
M01 moved up from Basic in March and up again in July. Their first month is not a change, since there is no earlier plan to have moved from. M04 stayed on one plan throughout and never appears.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Shopify
Difficulty
medium
Topic
window functions
Language
SQL
Your query
Starting the SQL engine...