Analyze Pirate Theme Usage Growth Over Time

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.

Pirate Theme Revenue Growth by Month

Table themes

column	type	constraints
id	INTEGER	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
is_pirate	BOOLEAN	NOT NULL

Sample data:

id	is_pirate	name
1	true	Pirate Red
2	false	Classic Blue
3	true	Pirate Gold

Table theme_usage

column	type	constraints
usage_id	INTEGER	PRIMARY KEY, NOT NULL
date	DATE	NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY -> users(user_id)
theme_id	INTEGER	NOT NULL, FOREIGN KEY -> themes(id)

Sample data:

date	theme_id	usage_id	user_id
2024-04-01	1	101	501
2024-04-02	1	102	502
2024-04-02	3	103	503

Table revenue

column	type	constraints
usage_id	INTEGER	PRIMARY KEY, NOT NULL, FOREIGN KEY -> theme_usage(usage_id)
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	usage_id
4.99	101
4.99	102
6.99	103

Table users

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	user_id
US	501
CA	502
UK	503

Table calendar

column	type	constraints
date	DATE	PRIMARY KEY, NOT NULL
month	VARCHAR(7)	NOT NULL

Sample data:

date	month
2024-04-01	2024-04
2024-04-02	2024-04
2024-05-01	2024-05

Expected output

A month-by-month time series for Pirate themes showing monthly revenue and the month-over-month (MoM) revenue growth percentage. Revenue is a direct proxy for Pirate theme traction: increasing revenue over time implies more adoption and/or higher monetization of Pirate themes.

month	pirate_revenue	revenue_mom_growth_pct
2024-04	16.97	NULL
2024-05	0	-100

Hints

1. JOIN theme_usage to themes and filter is_pirate = TRUE.
2. JOIN calendar on date to bucket events into months.
3. JOIN revenue on usage_id to sum Pirate revenue.
4. GROUP BY month to compute monthly aggregates.
5. Use LAG over month order to compute month-over-month growth.
6. LEFT JOIN from a months calendar to include months with zero usage so charts show continuous time.