Task
Meta Ads analysts want to compare campaign performance using the median click-through rate (CTR) across daily delivery records. Write a PostgreSQL query to return the median CTR for each ad campaign using window functions.
CTR for a row is defined as clicks::numeric / impressions. Rows with impressions = 0 or impressions IS NULL should be excluded from the median calculation.
Requirements
- Compute CTR for each valid row in
ad_delivery.
- For each
campaign_id, calculate the median CTR using window functions rather than PERCENTILE_CONT.
- If a campaign has an odd number of valid CTR rows, return the middle CTR.
- If a campaign has an even number of valid CTR rows, return the average of the two middle CTR values.
- Output
campaign_id, campaign_name, and median_ctr, ordered by campaign_id.
Table Definitions
meta_campaigns
| column | type | description |
|---|
| campaign_id | INT | Unique campaign identifier |
| campaign_name | VARCHAR(100) | Campaign name in Meta Ads Manager |
| objective | VARCHAR(50) | Campaign objective |
| | |
ad_delivery
| column | type | description |
|---|
| delivery_id | INT | Unique delivery record |
| campaign_id | INT | Campaign identifier |
| delivery_date | DATE | Daily delivery date |
| impressions | INT | Number of impressions served |
| clicks | INT | Number of clicks received |
| | |
Sample Data
meta_campaigns
| campaign_id | campaign_name | objective |
|---|
| 103 | Reels Retargeting | CONVERSIONS |
| 101 | Feed Prospecting | TRAFFIC |
| 104 | Shops Awareness | AWARENESS |
| 102 | Stories Install Push | APP_INSTALLS |
| 105 | Messenger Leads | LEADS |
| 106 | Advantage+ Catalog | SALES |
| 107 | IG Explore Test | TRAFFIC |
| 108 | Video View Booster | ENGAGEMENT |
| | |
ad_delivery
| delivery_id | campaign_id | delivery_date | impressions | clicks |
|---|
| 11 | 102 | 2024-06-03 | 100 | 4 |
| 3 | 101 | 2024-06-02 | 200 | 10 |
| 8 | 102 | 2024-06-01 | 100 | 1 |
| 1 | 101 | 2024-06-01 | 100 | 2 |
| 15 | 103 | 2024-06-04 | 100 | 7 |
| 18 | 104 | 2024-06-03 | 100 | 2 |
| 5 | 101 | 2024-06-04 | 100 | 8 |
| 20 | 105 | 2024-06-01 | 100 | 0 |
| 24 | 106 | 2024-06-02 | 100 | 6 |
| 27 | 108 | 2024-06-01 | 100 | 9 |
| | | | |
Expected Output
| campaign_id | campaign_name | median_ctr |
|---|
| 101 | Feed Prospecting | 0.0500 |
| 102 | Stories Install Push | 0.0400 |
| 103 | Reels Retargeting | 0.0600 |
| 104 | Shops Awareness | 0.0200 |
| 105 | Messenger Leads | 0.0000 |
| 106 | Advantage+ Catalog | 0.0600 |
