Ads Performance - Problem
Digital Marketing Analytics Challenge
A marketing company is analyzing the performance of their digital advertising campaign. They need to calculate the Click-Through Rate (CTR) for each advertisement to determine which ads are most effective.
Problem: Given a table of user interactions with ads, calculate the CTR for each ad and rank them by performance.
CTR Formula:
Important Notes:
• Only 'Clicked' and 'Viewed' actions count toward CTR calculation
• 'Ignored' actions are excluded from the calculation
• If an ad has no clicks or views, CTR should be 0.00
• Results should be rounded to 2 decimal places
• Sort by CTR descending, then by ad_id ascending for ties
Table Structure:
where action ∈ {'Clicked', 'Viewed', 'Ignored'}
A marketing company is analyzing the performance of their digital advertising campaign. They need to calculate the Click-Through Rate (CTR) for each advertisement to determine which ads are most effective.
Problem: Given a table of user interactions with ads, calculate the CTR for each ad and rank them by performance.
CTR Formula:
CTR = (Number of Clicks / (Number of Clicks + Number of Views)) × 100Important Notes:
• Only 'Clicked' and 'Viewed' actions count toward CTR calculation
• 'Ignored' actions are excluded from the calculation
• If an ad has no clicks or views, CTR should be 0.00
• Results should be rounded to 2 decimal places
• Sort by CTR descending, then by ad_id ascending for ties
Table Structure:
Ads (ad_id, user_id, action)where action ∈ {'Clicked', 'Viewed', 'Ignored'}
Input & Output
example_1.sql — Basic CTR Calculation
$
Input:
Ads table:
+-------+---------+---------+
| ad_id | user_id | action |
+-------+---------+---------+
| 1 | 1 | Clicked |
| 2 | 2 | Clicked |
| 3 | 3 | Viewed |
| 5 | 5 | Ignored |
| 1 | 7 | Ignored |
| 2 | 7 | Viewed |
| 3 | 5 | Clicked |
+-------+---------+---------+
›
Output:
+-------+-------+
| ad_id | ctr |
+-------+-------+
| 1 | 100.00|
| 3 | 50.00 |
| 2 | 50.00 |
+-------+-------+
💡 Note:
Ad 1: 1 click, 0 views → CTR = 1/(1+0) = 100%. Ad 2: 1 click, 1 view → CTR = 1/(1+1) = 50%. Ad 3: 1 click, 1 view → CTR = 1/(1+1) = 50%. Ad 5 only has 'Ignored' actions, so it's excluded. Results sorted by CTR desc, then ad_id asc for ties.
example_2.sql — Edge Case with No Interactions
$
Input:
Ads table:
+-------+---------+---------+
| ad_id | user_id | action |
+-------+---------+---------+
| 1 | 1 | Ignored |
| 2 | 2 | Clicked |
| 2 | 3 | Viewed |
| 3 | 4 | Ignored |
| 3 | 5 | Ignored |
+-------+---------+---------+
›
Output:
+-------+-------+
| ad_id | ctr |
+-------+-------+
| 2 | 50.00 |
| 1 | 0.00 |
| 3 | 0.00 |
+-------+-------+
💡 Note:
Ad 2: 1 click, 1 view → CTR = 50%. Ad 1 and 3 have only 'Ignored' actions, so CTR = 0%. When CTRs are tied at 0.00, sort by ad_id ascending.
example_3.sql — All Action Types
$
Input:
Ads table:
+-------+---------+---------+
| ad_id | user_id | action |
+-------+---------+---------+
| 1 | 1 | Clicked |
| 1 | 2 | Clicked |
| 1 | 3 | Viewed |
| 1 | 4 | Ignored |
| 2 | 1 | Viewed |
| 2 | 2 | Viewed |
+-------+---------+---------+
›
Output:
+-------+-------+
| ad_id | ctr |
+-------+-------+
| 1 | 66.67 |
| 2 | 0.00 |
+-------+-------+
💡 Note:
Ad 1: 2 clicks, 1 view → CTR = 2/(2+1) × 100 = 66.67%. Ad 2: 0 clicks, 2 views → CTR = 0/(0+2) × 100 = 0%. The 'Ignored' action for Ad 1 doesn't count toward the CTR calculation.
Visualization
Tap to expand
Understanding the Visualization
1
Group Data by Ad
Collect all user interactions for each advertisement ID
2
Count Interactions
Separate clicks from views, ignore 'Ignored' actions
3
Apply CTR Formula
CTR = (Clicks / (Clicks + Views)) × 100
4
Rank by Performance
Sort by CTR descending, break ties by ad_id ascending
Key Takeaway
🎯 Key Insight: Use conditional aggregation with CASE statements to count clicks and views in a single table scan, making the solution both elegant and efficient.
Time & Space Complexity
Time Complexity
O(n)
Single pass through the table to group and aggregate
✓ Linear Growth
Space Complexity
O(k)
Space proportional to number of unique ad_ids
✓ Linear Space
Constraints
- 1 ≤ Number of rows in Ads ≤ 104
- 1 ≤ ad_id ≤ 109
- 1 ≤ user_id ≤ 109
- action ∈ {'Clicked', 'Viewed', 'Ignored'}
- CTR must be rounded to exactly 2 decimal places
- Results must be ordered by CTR DESC, then ad_id ASC
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code