Determine Growth of Pirated Theme Installations Over Years

merchants

+-------------+------------+
| merchant_id | join_date  |
+-------------+------------+
| 1           | 2020-01-02 |
| 2           | 2020-03-11 |
| 3           | 2021-05-20 |
| 4           | 2021-07-15 |
| 5           | 2022-02-22 |
+-------------+------------+

themes

+----------+------------+
| theme_id | is_pirated |
+----------+------------+
| 101      | TRUE       |
| 102      | FALSE      |
| 103      | TRUE       |
| 104      | FALSE      |
| 105      | TRUE       |
+----------+------------+

merchant_themes

+-------------+----------+------------+
| merchant_id | theme_id | install_dt |
+-------------+----------+------------+
| 1           | 101      | 2020-01-02 |
| 1           | 102      | 2020-06-01 |
| 2           | 103      | 2021-02-10 |
| 3           | 104      | 2021-07-01 |
| 4           | 105      | 2022-03-11 |
+-------------+----------+------------+

Scenario

Shopify wants to understand if the proportion of merchants installing pirated storefront themes is growing over time.

Question

Write a SQL query that returns, for each calendar year, the percentage of active merchants who installed at least one pirated theme. From this result, determine whether adoption of pirated themes is increasing year-over-year.

Hints

Join theme installations to theme attributes, aggregate distinct merchant_ids by year, then compare year-over-year percentages.

Yearly Percentage of Merchants Installing Pirated Themes

Using the tables below, write a SQL query that returns, for each calendar year, the percentage of active merchants (those with at least one theme installation that year) who installed at least one pirated theme. Then, based on the result, determine whether adoption of pirated themes is increasing year-over-year.

Table merchants

column	type	constraints
merchant_id	INTEGER	PRIMARY KEY, NOT NULL
join_date	DATE	NOT NULL

Sample data:

join_date	merchant_id
2020-01-02	1
2020-03-11	2
2021-05-20	3
2021-07-15	4
2022-02-22	5

Table themes

column	type	constraints
theme_id	INTEGER	PRIMARY KEY, NOT NULL
is_pirated	BOOLEAN	NOT NULL

Sample data:

is_pirated	theme_id
true	101
false	102
true	103
false	104
true	105

Table merchant_themes

column	type	constraints
merchant_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES merchants(merchant_id)
theme_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES themes(theme_id)
install_dt	DATE	NOT NULL

Sample data:

install_dt	merchant_id	theme_id
2020-01-02	1	101
2020-06-01	1	102
2021-02-10	2	103
2021-07-01	3	104
2022-03-11	4	105

Expected output

Active merchants are defined as those with at least one theme installation in the given year. For each year, compute the percentage as: (distinct merchants with ≥1 pirated install that year) / (distinct merchants with any install that year) * 100. From the sample data: in 2020, merchant 1 installs a pirated theme (100%); in 2021, merchants 2 and 3 are active but only merchant 2 installs a pirated theme (50%); in 2022, merchant 4 is active and installs a pirated theme (100%). Adoption is not consistently increasing year-over-year; it decreases from 2020 to 2021, then increases again in 2022.

active_merchants	merchants_with_pirated_install	pct_pirated	year
1	1	100	2020
2	1	50	2021
1	1	100	2022

Hints

1. Join theme installations (merchant_themes) to theme attributes (themes) to identify pirated installs.
2. Aggregate to a per-merchant-per-year level to avoid double-counting merchants with multiple installs in a year.
3. Compute the yearly percentage as merchants_with_pirated_install divided by active_merchants.