LeetCode Problem 1322

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

This problem asks us to calculate the Click-Through Rate, abbreviated as CTR, for every advertisement stored in the Ads table.

The table contains three columns:

Column	Meaning
ad_id	Identifier of the advertisement
user_id	Identifier of the user interacting with the ad
action	The interaction type, either Clicked, Viewed, or Ignored

The CTR formula is:

$$CTR = \frac{\text{number of clicks}}{\text{number of clicks} + \text{number of views}} \times 100$$

The important detail is that Ignored actions are completely excluded from the denominator. Only Clicked and Viewed actions contribute to the CTR calculation.

The output should contain:

Column	Meaning
ad_id	The advertisement ID
ctr	The click-through rate rounded to two decimal places

The final result must be sorted by:

1. ctr in descending order
2. ad_id in ascending order if two ads have the same CTR

A critical edge case occurs when an advertisement has only Ignored actions. In that situation:

• clicks = 0
• views = 0
• denominator = 0

The problem specifies that the CTR should become 0.00 instead of causing a division-by-zero error.

The input guarantees that (ad_id, user_id) is unique, meaning the same user cannot have multiple actions recorded for the same ad.

Since this is a database problem, the primary task is aggregation. We need to group rows by ad_id, count different action types, compute a percentage, round it properly, and sort the results.

Approaches

Brute Force Approach

A brute-force approach would process each advertisement independently.

For every unique ad_id, we could scan the entire table again and count:

• how many rows have action = 'Clicked'
• how many rows have action = 'Viewed'

After collecting these counts, we compute:

$$CTR = \frac{clicked}{clicked + viewed} \times 100$$

If the denominator is zero, we return 0.

This approach is correct because it explicitly computes the counts needed for each advertisement. However, it is inefficient because for every advertisement we repeatedly scan the whole table.

If there are:

• n rows
• k unique ads

then the complexity becomes approximately O(n × k).

Optimal Approach

The key observation is that all required information can be computed in a single grouped aggregation.

Instead of rescanning the table for every ad, we group rows by ad_id and use conditional aggregation:

• count clicks using a conditional expression
• count views using a conditional expression
• compute CTR directly from those aggregates

SQL databases are optimized for grouping and aggregation, so this solution is both simpler and significantly more efficient.

We also use:

• ROUND(..., 2) to keep two decimal places
• IFNULL or conditional logic to avoid division by zero

Approach Comparison

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n × k)	O(1)	Repeatedly scans the table for each ad
Optimal	O(n)	O(k)	Single grouped aggregation using SQL

Algorithm Walkthrough

1. Group all rows by ad_id.

This allows us to process every advertisement independently while scanning the table only once logically. 2. Count the number of clicks for each advertisement.

We use conditional aggregation such as:

SUM(action = 'Clicked')

Every matching row contributes 1, and all others contribute 0. 3. Count the number of views for each advertisement.

Similarly, we use:

SUM(action = 'Viewed')

4. Compute the denominator.

The denominator is:

clicks + views

Ignored actions are intentionally excluded. 5. Handle division-by-zero cases.

Some ads may contain only ignored actions. In that case:

clicks + views = 0

We return 0.00 instead of dividing by zero. 6. Compute the CTR percentage.

The formula becomes:

clicks * 100 / (clicks + views)

7. Round the result to two decimal places.

We use:

ROUND(value, 2)

8. Sort the results.

The output must be ordered by:

• ctr DESC
• ad_id ASC

Why it works

The algorithm works because CTR depends only on two quantities for each advertisement:

• total clicks
• total views

Grouping by ad_id guarantees that all rows for the same advertisement are aggregated together. Conditional sums correctly count only the relevant actions. Since ignored actions are excluded from both numerator and denominator, the computed formula exactly matches the definition given in the problem statement.

Python Solution

# Write your MySQL query statement below

SELECT
    ad_id,
    ROUND(
        IFNULL(
            SUM(action = 'Clicked') * 100.0 /
            (SUM(action = 'Clicked') + SUM(action = 'Viewed')),
            0
        ),
        2
    ) AS ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id ASC;

The query begins by grouping all records using GROUP BY ad_id. This ensures each advertisement appears exactly once in the final result.

The expressions:

SUM(action = 'Clicked')

and

SUM(action = 'Viewed')

perform conditional aggregation. In MySQL, boolean expressions evaluate to 1 for true and 0 for false, so summing them gives the count of matching rows.

The CTR formula multiplies clicks by 100.0 to force floating-point division instead of integer division.

The IFNULL wrapper handles the case where the denominator becomes zero. Without this safeguard, division by zero would return NULL. The query converts such cases into 0.

Finally, ROUND(..., 2) ensures the result matches the required formatting.

Go Solution

// There is no Go implementation for SQL database problems.
// The correct submission is the SQL query below.

/*
SELECT
    ad_id,
    ROUND(
        IFNULL(
            SUM(action = 'Clicked') * 100.0 /
            (SUM(action = 'Clicked') + SUM(action = 'Viewed')),
            0
        ),
        2
    ) AS ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id ASC;
*/

Database problems on LeetCode are solved using SQL rather than a traditional programming language implementation. Therefore, the same SQL query is the intended solution regardless of language selection.

Worked Examples

Example 1

Input 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

Step 1: Group by ad_id

We separate rows into groups.

Ad 1

action
Clicked
Ignored
Viewed
Clicked

Counts:

• Clicked = 2
• Viewed = 1
• Ignored = 1

CTR:

$$\frac{2}{2+1} \times 100 = 66.67$$

Ad 2

action
Clicked
Viewed
Viewed

Counts:

• Clicked = 1
• Viewed = 2

CTR:

$$\frac{1}{1+2} \times 100 = 33.33$$

Ad 3

action
Viewed
Clicked

Counts:

• Clicked = 1
• Viewed = 1

CTR:

$$\frac{1}{1+1} \times 100 = 50.00$$

Ad 5

action
Ignored

Counts:

• Clicked = 0
• Viewed = 0

CTR:

$$0.00$$

Step 2: Sort results

Sorted by descending CTR:

ad_id	ctr
1	66.67
3	50.00
2	33.33
5	0.00

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n)	Each row is processed once during aggregation
Space	O(k)	Database grouping stores aggregates for each unique ad

The database engine scans the table once and maintains aggregated counts for every distinct ad_id. If there are k unique advertisements, the grouping structure requires proportional storage.

Test Cases

# Example case from the problem statement
# Validates normal CTR computation and ordering

# ad 1 -> 66.67
# ad 3 -> 50.00
# ad 2 -> 33.33
# ad 5 -> 0.00

# Ad with only ignored actions
# Ensures division-by-zero becomes 0.00

# Input:
# [(1, 1, 'Ignored')]
# Expected:
# [(1, 0.00)]

# Ad with only clicks
# CTR should become 100.00

# Input:
# [(1, 1, 'Clicked'),
#  (1, 2, 'Clicked')]
# Expected:
# [(1, 100.00)]

# Ad with only views
# CTR should become 0.00

# Input:
# [(1, 1, 'Viewed'),
#  (1, 2, 'Viewed')]
# Expected:
# [(1, 0.00)]

# Multiple ads with same CTR
# Validates secondary ordering by ad_id ascending

# ad 1 -> 50.00
# ad 2 -> 50.00
# Output order should be:
# 1 before 2

# Mixed ignored/viewed/clicked records
# Ensures ignored rows do not affect denominator

# Input:
# [(1, 1, 'Clicked'),
#  (1, 2, 'Ignored'),
#  (1, 3, 'Viewed')]
# Expected CTR:
# 50.00

Test Summary

Test	Why
Problem example	Validates core functionality
Only ignored actions	Tests division-by-zero handling
Only clicks	Ensures 100% CTR works correctly
Only views	Ensures zero-click ads return 0
Equal CTR values	Validates tie-breaking order
Mixed actions	Ensures ignored actions are excluded

Edge Cases

Advertisements With Only Ignored Actions

This is the most important edge case in the problem. If an advertisement contains only ignored interactions, both clicks and views become zero. A naive implementation might attempt to divide by zero and produce an error or NULL.

The solution handles this safely using IFNULL, converting the undefined result into 0.00.

Advertisements With No Views But Some Clicks

An advertisement may contain only clicked actions. In that case:

$$CTR = \frac{clicks}{clicks} \times 100 = 100$$

Some incorrect implementations mistakenly divide by total rows including ignored actions. The query correctly excludes ignored actions and produces 100.00.

Advertisements With Equal CTR Values

Two advertisements can have identical CTR values after rounding. The problem requires ties to be resolved using ascending ad_id.

The query explicitly includes:

ORDER BY ctr DESC, ad_id ASC

which guarantees deterministic and correct ordering.

Floating-Point Precision

CTR values often produce repeating decimals such as 66.6666.... Without explicit rounding, outputs may not match the expected format exactly.

The implementation uses:

ROUND(value, 2)

to ensure every result contains exactly two decimal places as required.