Sql : Leet code Problem Solved : Category: Easy : Write a SQL query to find Performance of the Ad Click-Through Rate (CTR): Problem 2

In Continuation of the earlier post this is the Problem 2 we will try to tackle today —

Sql : Leet code Problem Solved : Category: Easy : Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor have cooperated with the director at least 3 times: Problem 1 | by Sanjit Khasnobis | Sep, 2022 | Medium

As earlier discussed, we will divide all our problems in 4 sections as below —

Section 1: Talks about the Problem

Section 2: Prepare your data

Section 3: Analyse the Problem and find the most optimized solution

Section 4: Write the sql query for the problem

Let us jump into the problem -

Section 1: Talks about the Problem

— 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 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 query result format is in the following example:

— 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 |
— + — — — -+ — — — — -+ — — — — -+
— Result table:
— + — — — -+ — — — -+
— | ad_id | ctr |
— + — — — -+ — — — -+
— | 1 | 50.00 |
— | 3 | 50.00 |
— | 2 | 33.33 |
— | 5 | 0.00 |
— + — — — -+ — — — -+
— for ad_id = 1, ctr = (2/(4)) * 100 = 50.00
— 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

Section 2: Prepare your data

Create Table

create table Ads
(
ad_id int,
user_id int,
action varchar (100)
)

Insert Table

insert into Ads
values
(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’)

Section 3: Analyse the Problem and find the most optimized solution

if we just look into the data now we can clearly understand that we have to take the percentage of Click Action over Sum of All Actions group by each ad_id.

We will take 2 key steps to resolve it -

1. We will convert the action data to numeric Flag 1/0. 1 Means user ‘Clicked’. Any action other than that will be treated as 0.
2. Once we get that data in place, we will run avg function top upon that data for each ad_id group. This will give us the desired result because average in that case is nothing but as below -

Average for each ad_id = Total No of Ad Clicked (Sum of 1s in data) / Total No of Actions taken.

Input Data

Section 4: Write the sql query for the problem

Hope you have already figured out the query by now. In Case you did not please have a look at below sql query.

select
ad_id,round(avg(case when action=’Clicked’ then 1 else 0 end)*100,2) ctr
from Ads
group by ad_id
order by ad_id,ctr desc

Required Data Output

Hope you have enjoyed the article.

Happy Reading!! Happy Coding.

Please follow the github link for download and practicing the code.

sql_leetcode_solved/Easy at main · sanjitkhasnobis/sql_leetcode_solved