You are analyzing theme piracy on an e-commerce platform. Assume the analysis window is **2023-01-01 through 2023-12-31**, all timestamps are stored in **UTC**, and all monthly buckets should be computed by **calendar month in UTC**. ### Tables 1. **shops** - `shop_id BIGINT` - `created_at TIMESTAMP` 2. **themes** - `theme_id BIGINT` - `theme_name STRING` - `is_pirated BOOLEAN` 3. **pirated_theme_map** - `pirated_theme_id BIGINT` - `legitimate_theme_id BIGINT` - Each pirated theme maps to the legitimate theme it copies. 4. **theme_pricing** - `theme_id BIGINT` - `monthly_license_fee_usd NUMERIC(10,2)` 5. **theme_installations** - `install_id BIGINT` - `shop_id BIGINT` - `theme_id BIGINT` - `installed_at TIMESTAMP` - `valid_from DATE` - `valid_to DATE NULL` - Each row represents one continuous period during which a theme is active on a shop. 6. **calendar_months** - `month_start DATE` - One row per month in the analysis window. ### Key relationships - `theme_installations.shop_id -> shops.shop_id` - `theme_installations.theme_id -> themes.theme_id` - `pirated_theme_map.pirated_theme_id -> themes.theme_id` - `pirated_theme_map.legitimate_theme_id -> theme_pricing.theme_id` ### Definitions - A **pirated installation** is any row in `theme_installations` whose `theme_id` appears in `pirated_theme_map.pirated_theme_id`. - For monthly activity logic, treat an installation as active in a month if its active interval overlaps that month. - If `valid_to` is `NULL`, treat the installation as active through **2023-12-31**. - Estimated **revenue loss** for a pirated installation equals the `monthly_license_fee_usd` of the mapped legitimate theme for every month in which the pirated installation is active for at least one day. ### Tasks 1. Compute the monthly **pirated install rate**, defined as: - numerator = distinct `shop_id` values that installed at least one pirated theme during that month - denominator = distinct `shop_id` values that installed any theme during that month Required output columns: - `month_start` - `pirated_install_shops` - `all_install_shops` - `pirated_install_rate` 2. Compute the monthly **active pirated usage rate**, defined as: - numerator = distinct `shop_id` values with at least one pirated theme active at any point during that month - denominator = distinct `shop_id` values with at least one theme active at any point during that month Required output columns: - `month_start` - `active_pirated_shops` - `active_theme_shops` - `active_pirated_rate` 3. Compute **revenue loss over time** by month. Because the business asks for the loss "over time," return both: - monthly estimated loss in that month - cumulative estimated loss from the start of the analysis window up to that month Required output columns: - `month_start` - `monthly_revenue_loss_usd` - `cumulative_revenue_loss_usd` Write SQL only.