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.