Skip to main content
Talentd

Ads Performance - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min readAsked at: Meta
Practice this problem

Problem Statement

Table: Ads

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ad_id         | int     |
| user_id       | int     |
| action        | enum    |
+---------------+---------+
(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:

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:
+-------+---------+---------+
| 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 do not care about Ignored Ads.

Approach Overview

Problem Overview: The Ads Performance problem asks you to compute the click-through rate (CTR) for each advertisement. CTR is defined as Clicks / (Clicks + Views) * 100. Each row records an action (Clicked, Viewed, or Ignored). You must group results by ad_id, calculate CTR rounded to two decimal places, and sort by CTR descending and ad_id ascending.

Approach 1: Subquery Aggregation (O(n) time, O(n) space)

This approach first aggregates the number of Clicked and Viewed actions per ad using a grouped subquery. The outer query then computes the CTR using the formula clicked / (clicked + viewed). Handling division by zero is necessary when an ad has no clicks or views. In MySQL, you can use IFNULL or a conditional expression to return 0 when the denominator is zero. The query scans the table once for aggregation, giving O(n) time complexity, while the grouped results require O(n) space in the result set. This approach is readable and useful when you want to separate aggregation from final calculations.

Approach 2: Conditional Aggregation (O(n) time, O(1) extra space)

Conditional aggregation computes everything in a single grouped query. Instead of separate subqueries, use expressions such as SUM(action = 'Clicked') and SUM(action = 'Viewed'). MySQL treats boolean expressions as 1 or 0, so summing them counts matching rows. After calculating the counts, compute CTR using ROUND(100 * clicked / (clicked + viewed), 2), and return 0 when both counts are zero. This technique performs one scan of the table and avoids intermediate result sets, resulting in O(n) time and O(1) additional space beyond the grouped output.

This problem primarily tests your understanding of SQL aggregation and conditional counting. Familiarity with database queries, SQL, and aggregation patterns is enough to solve it quickly.

Recommended for interviews: The conditional aggregation approach is what interviewers expect. It demonstrates that you know how to count categories directly within a GROUP BY query using boolean expressions or CASE WHEN. The subquery approach is still valid and shows clear thinking, but the single-query aggregation is cleaner and more idiomatic SQL.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Subquery AggregationO(n)O(n)When you want clearer separation between counting actions and computing the CTR formula
Conditional Aggregation with SUMO(n)O(1)Preferred SQL pattern for counting categories directly inside GROUP BY queries

Video Solution

LeetCode 1322 "Ads Performance" Facebook (Meta) Interview SQL Question with Detailed Explanation • Everyday Data Science • 2,739 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Ads Performance easy or hard?
Ads Performance is considered an easy SQL problem. It mainly tests GROUP BY, conditional counting, and numeric calculations such as computing ratios and rounding results.
Ads Performance Python/Java solution
This is a database problem, so the primary solution is written in SQL (typically MySQL on LeetCode). Python or Java would only be used to execute the query through a database connector, while the CTR calculation logic stays inside the SQL query.
How to solve Ads Performance in O(n)?
Group rows by ad_id and use conditional aggregation to count Clicked and Viewed actions. Compute CTR with ROUND(100 * clicks / (clicks + views), 2) and return 0 when the denominator is zero. Since the query scans the table once, the complexity remains O(n).
What is the best approach for Ads Performance?
The best approach uses conditional aggregation in SQL. Count clicks with SUM(action = 'Clicked') and views with SUM(action = 'Viewed'), then compute CTR as 100 * clicks / (clicks + views). This runs in O(n) time with a single GROUP BY query and avoids unnecessary subqueries.
Is Ads Performance asked at Google/Amazon/Meta?
SQL aggregation problems like Ads Performance are common in data and backend interviews at companies such as Amazon, Google, and Meta. Candidates are often expected to demonstrate GROUP BY usage, conditional counting, and ratio calculations like CTR.
What data structure is used in Ads Performance?
The solution relies on relational database grouping rather than traditional data structures. SQL uses GROUP BY internally to organize rows by ad_id and aggregation functions like SUM or COUNT to compute metrics.
What is the time complexity of Ads Performance?
The time complexity is O(n), where n is the number of rows in the Ads table. The database scans the table once to aggregate counts by ad_id. Space usage is minimal because only grouped results are stored.

Ready to solve this problem?

Practice Ads Performance with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary