Measure Pirate-Theme Usage and Revenue Growth Over Time

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.

Pirate Theme Monthly Usage Growth and Revenue

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.

Table theme_usage

column	type	constraints
user_id	INTEGER	NOT NULL
theme_name	VARCHAR	NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	theme_name	user_id
2024-01-05	pirate	101
2024-01-06	pirate	102
2024-02-14	pirate	101
2024-02-20	pirate	110
2024-03-01	pirate	103

Table theme_transactions

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
created_at	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	created_at	order_id
2.99	2024-01-05	1
2.99	2024-01-06	2
2.99	2024-02-14	3
2.99	2024-02-20	4
2.99	2024-03-01	5

Expected output

By calendar month, report Monthly Active Users (distinct users using the pirate theme), the month-over-month growth rate in active users, and the monthly (non-cumulative) revenue for the pirate theme.

active_users	mom_growth_rate	month	monthly_revenue
2	NULL	2024-01-01	5.98
2	0	2024-02-01	5.98
1	-0.5	2024-03-01	2.99

Hints

1. Think in calendar months; use DATE_TRUNC('month', created_at) to bucket dates.
2. Measure usage as distinct users per month (MAU) and compute month-over-month growth with the LAG window function.