`ad_stats` holds hourly impression/click counts per campaign, but some hours were loaded more than once: for the same (campaign_id, stat_hour) keep only the row with the HIGHEST `stat_id` (latest load). After de-duplication, total impressions and clicks per campaign; only campaigns with at least 100 impressions qualify. CTR = 100.0 * clicks / impressions. Rank qualifying campaigns within each advertiser by CTR (highest first) with DENSE_RANK and keep ranks 1 and 2. Return `advertiser`, `campaign_name`, `impressions`, `clicks`, `ctr_pct` (ROUND 2), `rnk`. Order by `advertiser`, `rnk`, `campaign_name`.
| Column | Type |
|---|---|
| campaign_id | INTEGER |
| advertiser | TEXT |
| campaign_name | TEXT |
| campaign_id | advertiser | campaign_name |
|---|---|---|
| 1 | Acme | Acme Shoes |
| 2 | Acme | Acme Socks |
| 3 | Acme | Acme Hats |
| 4 | Acme | Acme Belts |
| 5 | Beta | Beta Phones |
| 6 | Beta | Beta Cases |
| 7 | Beta | Beta Cables |
| Column | Type |
|---|---|
| stat_id | INTEGER |
| campaign_id | INTEGER |
| stat_hour | TIMESTAMP |
| impressions | INTEGER |
| clicks | INTEGER |
| stat_id | campaign_id | stat_hour | impressions | clicks |
|---|---|---|---|---|
| 1 | 1 | 2026-10-01 09:00:00 | 100 | 5 |
| 2 | 1 | 2026-10-01 10:00:00 | 100 | 3 |
| 3 | 2 | 2026-10-01 09:00:00 | 50 | 1 |
| 4 | 3 | 2026-10-01 09:00:00 | 120 | 6 |
| 5 | 3 | 2026-10-01 09:00:00 | 120 | 9 |
| 6 | 4 | 2026-10-01 09:00:00 | 300 | 12 |
| 7 | 5 | 2026-10-01 09:00:00 | 400 | 8 |
| 8 | 6 | 2026-10-01 09:00:00 | 100 | 1 |
| advertiser | campaign_name | impressions | clicks | ctr_pct | rnk |
|---|---|---|---|---|---|
| Acme | Acme Hats | 120 | 9 | 7.5 | 1 |
| Acme | Acme Belts | 300 | 12 | 4 | 2 |
| Acme | Acme Shoes | 200 | 8 | 4 | 2 |
| Beta | Beta Phones | 400 | 8 | 2 | 1 |
| Beta | Beta Cases | 100 | 1 | 1 | 2 |
Solve it in the live editor on this page — run on sample data, then submit against hidden tests.
‹ Azure Cost Roll-up Through the Resource Tree · Clicks per Normalized Domain ›
{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.
Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.
{{ cur.statement }}
No submissions yet. Press Evaluate to submit your query.
{{ solutionSql }}
{{ out.error }}
You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.