Last updated on March 10th, 2025 at 10:40 pm

Here, we see an Ads Performance LeetCode Solution. This Leetcode problem is solved using MySQL and Pandas.

List of all LeetCode Solution

Level of Question

Easy

Ads Performance LeetCode Solution

1. Problem Statement

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:
CTR = 0, if Ad total clicks + Ad total views = 0
CTR = Ad total clicks / (Ad total clicks + Ad total views) * 100, otherwise.

Write an SQL query to find the ctr of each Ad. Round ctr to 2 decimal points. Order the result table 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:

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 don’t care about Ignored Ads.
Result table is ordered by the ctr. in case of a tie we order them by ad_id

2. Code Implementation in Different Languages

2.1 Ads Performance MySQL

select 
  ad_id, 
  round(
    if(
      clicks + views = 0, 
      0, 
      clicks / (clicks + views) * 100
    ), 
    2
  ) as ctr 
from 
  (
    select 
      ad_id, 
      sum(
        if(action = 'Clicked', 1, 0)
      ) as clicks, 
      sum(
        if(action = 'Viewed', 1, 0)
      ) as views 
    from 
      Ads 
    group by 
      ad_id
  ) as a 
order by 
  ctr desc, 
  ad_id asc;