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.