Problem Description –
A company is running Ads and wants to calculate the performance of each Ad.
Performance of the Ad is measured using Click-Through Rate (CTR) where:
Write an SQL query to find the
ctr of each Ad. Round
ctr to two decimal points.
Return the result table ordered by
ctr in descending order and by
ad_id in ascending order in case of a tie.
The query result format is in the following example.
Difficulty Level – Easy
Problem Link – Ads Performance
SELECT ad_id, ROUND(IFNULL(SUM(CASE WHEN action='Clicked' THEN 1 ELSE 0 END) * 100 / (SUM(CASE WHEN action='Clicked' THEN 1 ELSE 0 END) + SUM(CASE WHEN action='Viewed' THEN 1 ELSE 0 END)), 0), 2) as ctr FROM Ads GROUP BY 1 ORDER BY 2 DESC, 1