Description
Write a SQL query to find the click-through rate (CTR) for each ad. CTR = clicks / (clicks + views) * 100. **Output columns (in order):** ad_id, ctr
Table:
ads| Column | Type |
|---|---|
| ad_id | INT |
| user_id | INT |
| action | TEXT |
Examples
Input:
CREATE TABLE ads (ad_id INT, user_id INT, action TEXT); INSERT INTO ads VALUES (1, 1, 'Clicked'), (1, 2, 'Clicked'), (1, 3, 'Viewed'), (2, 1, 'Viewed'), (2, 2, 'Viewed');Output:
1|66.67
2|0.0Explanation:
Ad 1 has 2 clicks and 1 view. CTR = 2/(2+1)*100 = 66.67%. Ad 2 has 0 clicks and 2 views. CTR = 0/(0+2)*100 = 0.0%.
Input:
CREATE TABLE ads (ad_id INT, user_id INT, action TEXT); INSERT INTO ads VALUES (3, 101, 'Clicked'), (3, 102, 'Clicked'), (3, 103, 'Clicked'), (3, 104, 'Clicked'), (4, 201, 'Viewed'), (4, 202, 'Viewed'), (4, 203, 'Viewed');Output:
3|100.00
4|0.00Explanation:
Ad 3 has 4 clicks and 0 views, so CTR = 4/(4+0)*100 = 100.00%. Ad 4 has 0 clicks and 3 views, so CTR = 0/(0+3)*100 = 0.00%. This demonstrates the edge cases of maximum CTR (all clicks) and minimum CTR (no clicks).
Input:
CREATE TABLE ads (ad_id INT, user_id INT, action TEXT); INSERT INTO ads VALUES (5, 301, 'Clicked'), (5, 302, 'Viewed'), (5, 303, 'Viewed'), (5, 304, 'Viewed'), (5, 305, 'Viewed'), (6, 401, 'Clicked'), (6, 402, 'Clicked'), (6, 403, 'Clicked'), (6, 404, 'Viewed'), (6, 405, 'Viewed'), (6, 406, 'Viewed');Output:
5|20.00
6|50.00Explanation:
Ad 5 has 1 click and 4 views, so CTR = 1/(1+4)*100 = 20.00%. Ad 6 has 3 clicks and 3 views, so CTR = 3/(3+3)*100 = 50.00%. This shows different CTR scenarios with varying click-to-view ratios.
Constraints
- •
Round to 2 decimal places