Problem

Table: Ads

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ad_id         | int     |
| user_id       | int     |
| action        | enum    |
+---------------+---------+
(ad_id, user_id) is the primary key (combination of columns with unique values) 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 (category) 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:

$$ CTR = \begin{cases} 0 & \text{if Ad total clicks + Ad total views = 0} \\ \frac{\text{Ad total clicks}}{\text{Ad total clicks + Ad total views}} x 100 & \text{, otherwise} x < 0 \end{cases} $$

Write a solution 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 result format is in the following example.

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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 |
| 1     | 4       | Viewed  |
| 2     | 11      | Viewed  |
| 1     | 2       | Clicked |
+-------+---------+---------+
Output: 
+-------+-------+
| ad_id | ctr   |
+-------+-------+
| 1     | 66.67 |
| 3     | 50.00 |
| 2     | 33.33 |
| 5     | 0.00  |
+-------+-------+
Explanation: 
for ad_id = 1, ctr = (2/(2+1)) * 100 = 66.67
for ad_id = 2, ctr = (1/(1+2)) * 100 = 33.33
for ad_id = 3, ctr = (1/(1+1)) * 100 = 50.00
for ad_id = 5, ctr = 0.00, Note that ad_id = 5 has no clicks or views.
Note that we do not care about Ignored Ads.

Solution

Method 1 – Using Conditional Aggregation

Intuition

We need to calculate the Click-Through Rate (CTR) for each ad. The CTR is the percentage of clicks out of the total number of clicks and views for each ad. We ignore “Ignored” actions. By grouping by ad_id and using conditional aggregation, we can count clicks and views efficiently.

Approach

  1. Group by ad_id: Aggregate data for each ad.
  2. Count Clicks and Views: Use SUM(CASE WHEN ...) to count “Clicked” and “Viewed” actions separately.
  3. Calculate CTR:
    • If total clicks + views is 0, CTR is 0.
    • Otherwise, CTR = (clicks / (clicks + views)) * 100.
  4. Round CTR: Use ROUND(..., 2) to round to two decimal places.
  5. Order Results: Sort by ctr descending, then by ad_id ascending.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
  ad_id,
  ROUND(
    CASE
      WHEN SUM(action = 'Clicked') + SUM(action = 'Viewed') = 0 THEN 0
      ELSE SUM(action = 'Clicked') / (SUM(action = 'Clicked') + SUM(action = 'Viewed')) * 100
    END
  , 2) AS ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id ASC;

Complexity

  • ⏰ Time complexity: O(n) — Each row is scanned once for aggregation.
  • 🧺 Space complexity: O(k) — Where k is the number of unique ad_id values.