1322 Ads Performance

Description

Table: Ads

• (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:

1CTR = 0 if ad total clicks + ad total views = 0
2CTR = (ad total clicks) / (ad total clicks + ad total views) * 100

Problem Statement

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.

Example 1:

Input:

• Ads table:

Output:

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

This is an aggregation problem where you would group by ad_id. In order to calculate the sum of Clicked action or Viewed action, you can use CASE WHEN action='Clicked' THEN 1 ELSE 0 END and perform sum over these records.

1SELECT
2    ad_id,
3    (
4        SUM(CASE WHEN action='Clicked' THEN 1 ELSE 0 END) /
5        SUM(CASE WHEN action='Clicked' OR action='Viewed' THEN 1 ELSE 0 END)
6    ) AS ctr
7    FROM Ads
8    GROUP BY ad_id
9    ORDER BY ctr DESC, ad_id;

Above query returns following result.

This kind of matches up with what we need except that the ctr is in decimals but we want in percentage (rounded to 2 digits). This could be done using following.

1ROUND((
2    SUM(CASE WHEN action='Clicked' THEN 1 ELSE 0 END) /
3    SUM(CASE WHEN action='Clicked' OR action='Viewed' THEN 1 ELSE 0 END)
4    ) * 100, 2) AS ctr

Another difference is that we have null value for ad_id = 5. These records should be replaced with 0. So, you can use IFNULL() function to do that.

1IFNULL(
2    ROUND((
3        SUM(CASE WHEN action='Clicked' THEN 1 ELSE 0 END) /
4        SUM(CASE WHEN action='Clicked' OR action='Viewed' THEN 1 ELSE 0 END)
5        ) * 100, 2), 0) AS ctr

The final query looks like this.

 1SELECT
 2    ad_id,
 3    IFNULL(
 4        ROUND((
 5            SUM(CASE WHEN action='Clicked' THEN 1 ELSE 0 END) /
 6            SUM(CASE WHEN action='Clicked' OR action='Viewed' THEN 1 ELSE 0 END)
 7            ) * 100, 2), 0) AS ctr
 8    FROM Ads
 9    GROUP BY ad_id
10    ORDER BY ctr DESC, ad_id;