Last updated on October 9th, 2024 at 10:00 pm

This Leetcode problem Ads Performance LeetCode Solution is done in SQL.

List of all LeetCode Solution

Ads Performance LeetCode Solution

Problem Statement

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

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

1. Ads Performance LeetCode Solution 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;