Revenue by plan and month
mediumRevenue by plan and month
Shopify Pandas Interview Question
Shopify's subscription revenue comes from merchants on three tiers, and which tier they sit on matters more than how many of them there are. A merchant moving from Advanced down to Basic takes nine tenths of its revenue with it. Finance tracks what each plan billed each month, so a tier losing ground is visible before the total moves.
Using the subscriptions DataFrame, return one row per month: keep active_month as a column, then one column per plan named Advanced, Basic and Shopify, holding the subscription revenue billed on that plan that month. A plan with nothing in a month shows 0. Assign the answer to result.
Asked of
- Data Analyst
- Business Analyst
- Product Analyst
- Analytics Engineer
- Data Scientist
subscriptionsDataFrame26 rows
| Column Name | Type |
|---|---|
| record_id | int64 |
| merchant_id | str |
| active_month | str |
| plan | str |
| mrr | float64 |
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 |
| 1107 | M02 | 2024-01-01 | Shopify | 79 |
| 1108 | M02 | 2024-02-01 | Shopify | 79 |
| 1109 | M02 | 2024-03-01 | Shopify | 79 |
| 1110 | M02 | 2024-04-01 | Shopify | 79 |
| 1111 | M02 | 2024-05-01 | Basic | 29 |
| 1112 | M02 | 2024-06-01 | Basic | 29 |
| 1113 | M02 | 2024-07-01 | Basic | 29 |
| 1114 | M02 | 2024-08-01 | Basic | 29 |
Example Output
| active_month | Advanced | Basic | Shopify |
|---|---|---|---|
| 2024-01-01 | 0 | 29 | 79 |
| 2024-02-01 | 0 | 29 | 79 |
| 2024-03-01 | 0 | 0 | 158 |
| 2024-04-01 | 0 | 0 | 79 |
| 2024-05-01 | 0 | 29 | 0 |
| 2024-06-01 | 0 | 29 | 79 |
| 2024-07-01 | 299 | 29 | 0 |
| 2024-08-01 | 299 | 29 | 0 |
Explanation
In March both merchants in the example were on the Shopify plan, the middle tier at 79 a month, so that column holds 158 and the other two hold 0. Nobody on a plan is 0 revenue for that month, not a missing number.
The example above is a small slice of the data. Your code runs against the full DataFrames.
Company
Shopify
Difficulty
medium
Topic
reshaping
Language
Pandas
Your code
Loading Python in the background. You can start writing.