SQL Interview Questions – Ads Performance

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

Solution –

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