Measure Pirate-Theme Usage and Revenue Growth Over Time
Company: Shopify
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
theme_usage
+-----------+------------+-------------+
| user_id | theme_name | created_at |
+-----------+------------+-------------+
| 101 | pirate | 2024-01-05 |
| 102 | pirate | 2024-01-06 |
| 101 | pirate | 2024-02-14 |
| 110 | pirate | 2024-02-20 |
| 103 | pirate | 2024-03-01 |
+-----------+------------+-------------+
theme_transactions
+-----------+------------+--------+
| order_id | created_at | amount |
+-----------+------------+--------+
| 1 | 2024-01-05 | 2.99 |
| 2 | 2024-01-06 | 2.99 |
| 3 | 2024-02-14 | 2.99 |
| 4 | 2024-02-20 | 2.99 |
| 5 | 2024-03-01 | 2.99 |
+-----------+------------+--------+
##### Scenario
Mobile personalization platform tracks several tables (installs, purchases, etc.) for a "pirate" theme and wants to understand whether its usage and revenue are growing over time.
##### Question
Define an appropriate metric to measure pirate-theme usage growth and write the SQL that produces that metric by calendar month. 2. Write SQL that shows monthly revenue for the pirate theme (not cumulative).
##### Hints
Think in calendar months; group by DATE_TRUNC('month', …). Treat growth as either active users or installations month-over-month.
Overview: This question evaluates the ability to design time-based usage metrics and compute monthly revenue through data manipulation using SQL or Python, focusing on aggregating event and transaction records by calendar month.
You are given two tables that track usage and purchases for a mobile personalization "pirate" theme.
1) Use **Monthly Active Users (MAU)**, defined as the distinct count of users in `theme_usage` per calendar month for the pirate theme, as the metric of usage. Write SQL that returns, by calendar month, the number of active users and the month-over-month growth rate in active users.
2) Assume `theme_transactions` contains only pirate-theme transactions. Write SQL that returns, by calendar month, the non-cumulative revenue for the pirate theme.
3) Combine these into a single query that, by calendar month, reports: `month`, `active_users`, `mom_growth_rate`, and `monthly_revenue` for the pirate theme.
Tables
theme_usage(user_id INTEGER, theme_name VARCHAR, created_at DATE)
theme_transactions(order_id INTEGER, created_at DATE, amount DECIMAL(10,2))
Hints
- Think in calendar months; use DATE_TRUNC('month', created_at) to bucket dates.
- Measure usage as distinct users per month (MAU) and compute month-over-month growth with the LAG window function.