Leetcode Problem 1322. Ads Performance

1322. Ads Performance

Leetcode Solutions

Calculating Click-Through Rate (CTR) for Ads

Core Logic Steps for Code Implementation

  1. Group by ad_id: Aggregate the data by ad_id to calculate metrics for each ad.
  2. Count Clicks and Views: Use conditional aggregation to count the number of 'Clicked' and 'Viewed' actions using SUM(action = 'Clicked') and SUM(action = 'Viewed').
  3. Calculate CTR: Apply the CTR formula, handling division by zero using IFNULL to replace NULL with 0.
  4. Round CTR: Use the ROUND function to round the CTR to two decimal places.
  5. Order Results: Sort the results by CTR in descending order and by ad_id in ascending order in case of a tie.
  6. Select and Output: Select the ad_id and the calculated ctr for the final output.

erDiagram
    Ads {
        int ad_id
        int user_id
        enum action
        ad_id user_id PK
    }

Calculating Click-Through Rate (CTR) for Ads Using JOIN

Ask Question

Programming Language
image/screenshot of info(optional)
Full Screen
Loading...

Suggested Answer

Answer
Full Screen
Copy Answer Code
Loading...