leetcode - 1322. Ads Performance

난이도 : Easy

Table: Ads

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ad_id         | int     |
| user_id       | int     |
| action        | enum    |
+---------------+---------+
(ad_id, user_id) is the primary key for this table.
Each row of this table contains the ID of an Ad, the ID of a user, and the action taken by this user regarding this Ad.
The action column is an ENUM type of ('Clicked', 'Viewed', 'Ignored').

 

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.

 

 

SELECT ad_id
     , IFNULL(ROUND(SUM(IF(action = 'Clicked', 1, 0)) * 100 / SUM(IF(action = 'Clicked' OR action = 'Viewed', 1, 0)), 2), 0.00) ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id

Accepted (87.70%)