Analyze Pirate Theme Usage Growth Over Time
Company: Shopify
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
themes
+----+--------------+-----------+
| id | name | is_pirate |
+----+--------------+-----------+
| 1 | Pirate Red | true |
| 2 | Classic Blue | false |
| 3 | Pirate Gold | true |
+----+--------------+-----------+
theme_usage
+----------+------------+---------+---------+
| usage_id | date | user_id | theme_id|
+----------+------------+---------+---------+
| 101 | 2024-04-01 | 501 | 1 |
| 102 | 2024-04-02 | 502 | 1 |
| 103 | 2024-04-02 | 503 | 3 |
+----------+------------+---------+---------+
revenue
+----------+--------+
| usage_id | amount |
+----------+--------+
| 101 | 4.99 |
| 102 | 4.99 |
| 103 | 6.99 |
+----------+--------+
users
+---------+---------+
| user_id | country |
+---------+---------+
| 501 | US |
| 502 | CA |
| 503 | UK |
+---------+---------+
calendar
+------------+---------+
| date | month |
+------------+---------+
| 2024-04-01 | 2024-04 |
| 2024-04-02 | 2024-04 |
| 2024-05-01 | 2024-05 |
+------------+---------+
##### Scenario
A mobile app sells visual themes; five tables track themes, user activity, revenue, users, and a calendar. Leadership wants to know if Pirate-themed content is gaining traction.
##### Question
Write a SQL query that shows whether Pirate theme usage is growing over time. Define and compute your own growth metric (e.g., monthly revenue, monthly active users, cumulative installs, etc.).
2) Return a month-by-month time series that can be charted.
3) Briefly explain why the metric you chose captures growth.
##### Hints
JOIN theme_usage → themes to filter is_pirate, JOIN calendar for month buckets; GROUP BY month; use window functions or lag to compute growth rates.
Overview: This question evaluates a candidate's ability to perform SQL-based data manipulation and time-series analysis, emphasizing joins, aggregation, time-bucketing, and definition of growth metrics to measure feature adoption.
A mobile app sells visual themes; five tables track themes, user activity, revenue, users, and a calendar. Leadership wants to know if Pirate-themed content is gaining traction.
Write a SQL query that shows whether Pirate theme usage is growing over time by computing a month-by-month metric and its growth. Use Pirate theme revenue as the growth metric, and return a month-by-month time series suitable for charting.
Tables
themes(id INTEGER, name VARCHAR(50), is_pirate BOOLEAN)
theme_usage(usage_id INTEGER, date DATE, user_id INTEGER, theme_id INTEGER)
revenue(usage_id INTEGER, amount DECIMAL(10,2))
users(user_id INTEGER, country VARCHAR(2))
calendar(date DATE, month VARCHAR(7))
Hints
- JOIN theme_usage to themes and filter is_pirate = TRUE.
- JOIN calendar on date to bucket events into months.