Analyze Pirated Theme Usage Impact
ShopifyData Scientist · Technical Screen
Last updated
This question evaluates a candidate's ability to perform data manipulation and analytics with SQL and Python to detect license compliance, construct month-level time windows, and quantify revenue impact from pirated theme usage.
An e-commerce platform lets shops install marketplace themes. Some themes are paid, and a shop should have a valid purchase/license before using a paid theme. Some shops may be using pirated copies of paid themes.
Write SQL to analyze pirated theme usage, monthly trends, and revenue impact. Assume all dates and timestamps are in UTC. Analyze the window from 2023-01-01 through 2023-12-31, inclusive. Monthly fields use the first day of the month.
Tables
shops
| Column | Type | Description |
|---|---|---|
shop_id | BIGINT | Primary key for a shop. |
created_at | TIMESTAMP | Shop creation timestamp. |
country | VARCHAR | Shop country. |
plan | VARCHAR | Subscription plan, such as basic, standard, or plus. |
vertical | VARCHAR | Merchant business vertical. |
themes
| Column | Type | Description |
|---|---|---|
theme_id | BIGINT | Primary key for a theme. |
theme_name | VARCHAR | Theme name. |
developer_id | BIGINT | Theme developer identifier. |
is_paid | BOOLEAN | Whether the theme requires a paid license. |
list_price_usd | NUMERIC(10,2) | Official theme price in USD. |
launched_at | TIMESTAMP | Theme launch timestamp. |
shop_theme_usage
| Column | Type | Description |
|---|---|---|
shop_id | BIGINT | Foreign key to shops.shop_id. |
theme_id | BIGINT | Foreign key to themes.theme_id. |
start_month | DATE | First month the shop used the theme. Always the first day of a month. |
end_month | DATE | Last month the shop used the theme. Always the first day of a month. NULL means still active. |
A shop-theme usage row is considered active in month m if m >= start_month and m <= COALESCE(end_month, DATE '9999-12-01').
theme_purchases
| Column | Type | Description |
|---|---|---|
order_id | BIGINT | Primary key for a theme purchase. |
shop_id | BIGINT | Foreign key to shops.shop_id. |
theme_id | BIGINT | Foreign key to themes.theme_id. |
purchased_at | TIMESTAMP | Purchase timestamp. |
amount_usd | NUMERIC(10,2) | Amount paid in USD. |
refunded_at | TIMESTAMP | Refund timestamp. NULL means not refunded. |
A shop has a valid license for a paid theme in month m if it purchased that theme before the first day of the next month and the purchase was not refunded before the first day of the next month.
shop_monthly_revenue
| Column | Type | Description |
|---|---|---|
shop_id | BIGINT | Foreign key to shops.shop_id. |
month | DATE | Revenue month, first day of month. |
gross_merchandise_volume_usd | NUMERIC(18,2) | Shop GMV in that month. |
platform_revenue_usd | NUMERIC(18,2) | Revenue earned by the platform from that shop in that month. |
Definition of pirated usage
A pirated usage is a shop-theme-month where:
- The theme is paid:
themes.is_paid = TRUE. - The shop is actively using the theme in that month.
- The shop does not have a valid license for that theme in that month.
Tasks
- Create a monthly usage trend table with one row per month and the following columns:
monthtotal_active_paid_theme_usagespirated_active_theme_usagespirating_shopspiracy_rate, defined aspirated_active_theme_usages / total_active_paid_theme_usages
- Create a monthly revenue impact table with one row per month and the following columns:
monthpotential_theme_revenue_lost_usd, defined as the sum ofthemes.list_price_usdacross pirated shop-theme-month rowsplatform_revenue_from_pirating_shops_usd, counting each pirating shop's monthly platform revenue only once per month even if it uses multiple pirated themesavg_platform_revenue_per_pirating_shop_usdpct_platform_revenue_from_pirating_shops, defined as revenue from pirating shops divided by total platform revenue that month
- Create a prioritization table to inform product strategy. Return the top 10 combinations of
theme_id,theme_name,plan, andverticalbypotential_theme_revenue_lost_usd, with these columns:
theme_idtheme_nameplanverticalpirated_active_theme_usagespirating_shopspotential_theme_revenue_lost_usdplatform_revenue_from_pirating_shops_usd
Be careful to avoid incorrect joins that duplicate revenue when a shop has multiple theme usage rows or multiple purchase rows.
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.