Two LEFT JOIN aggregation with ratio computation. Practice computing CTR (orders / views × 100) with NULLIF guard, restricting to active listings, and producing the top-N output the listing-merchandising team uses for promotional sponsorship.
Listing optimization sprint kickoff. Top 10 active listings by view count, with CTR (orders / views × 100, rounded to 2 decimals). Five columns: listing_id, product_title, views (count of fact_listing_views), orders (count of fact_orders), ctr_pct. Sort views descending, listing_id ascending. The bottom-CTR listings in the top-views set are the prime optimization targets — high traffic, low conversion.
| Column | Type | Key |
|---|---|---|
| listing_id | INT | PK |
| marketplace_id | INT | FK → dim_marketplaces |
| asin_or_listing_id | TEXT | |
| product_title | TEXT | |
| category | TEXT | |
| list_price | REAL | |
| current_status | TEXT |
| Column | Type | Key |
|---|---|---|
| view_id | INT | PK |
| listing_id | INT | FK → dim_listings |
| viewed_at | TEXT | |
| source | TEXT |
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| listing_id | INT | FK → dim_listings |
| marketplace_id | INT | FK → dim_marketplaces |
| order_ts | TEXT | |
| units | INT | |
| gross_amount | REAL | |
| refunded | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two LEFT JOINs: dim_listings → fact_listing_views (for view count) AND dim_listings → fact_orders (for order count).
Filter dim_listings WHERE current_status = 'active'.
GROUP BY listing_id, product_title.
ctr_pct = ROUND(100.0 * COUNT(DISTINCT order_id) / NULLIF(COUNT(view_id), 0), 2). NULLIF prevents div-by-zero on listings with zero views.