Ads Performance - Practice Coding Problems

Ads Performance - Problem

Database Easy

A company is running ads and wants to calculate the performance of each ad using Click-Through Rate (CTR).

The Ads table contains:

ad_id - ID of the advertisement
user_id - ID of the user who interacted with the ad
action - User action: 'Clicked', 'Viewed', or 'Ignored'

Click-Through Rate (CTR) is calculated as:

CTR = (Number of Clicks) / (Number of Clicks + Number of Views) × 100

Write a SQL query to find the CTR of each ad. Round CTR to two decimal places. Return results ordered by CTR in descending order, and by ad_id in ascending order for ties.

Note: If an ad has no clicks or views, it should not appear in the result.

Table Schema

Ads

Column Name	Type	Description
`ad_id` PK	int	Advertisement ID
`user_id` PK	int	User ID who performed the action
`action`	enum	Action taken: 'Clicked', 'Viewed', or 'Ignored'

Primary Key: (ad_id, user_id)

Note: Each row represents one user's action on a specific ad. Only 'Clicked' and 'Viewed' actions count toward CTR calculation.

Input & Output

Example 1 — Basic CTR Calculation

Input Table:

ad_id	user_id	action
1	1	Clicked
1	2	Viewed
1	3	Ignored
2	1	Viewed
2	3	Ignored
3	1	Clicked
3	2	Clicked

Output:

ad_id	ctr
3	100
1	50
2	0

💡 Note:

Ad 1: 1 click + 1 view = CTR of (1/2) × 100 = 50.00%

Ad 2: 0 clicks + 1 view = CTR of (0/1) × 100 = 0.00%

Ad 3: 2 clicks + 0 views = CTR of (2/2) × 100 = 100.00%

Results ordered by CTR descending: Ad 3 (100.00), Ad 1 (50.00), Ad 2 (0.00)

Example 2 — Only Ignored Actions

Input Table:

ad_id	user_id	action
1	1	Ignored
1	2	Ignored
2	1	Clicked
2	2	Viewed

Output:

ad_id	ctr
2	50

💡 Note:

Ad 1: Only ignored actions, so it doesn't appear in results (no clicks or views)

Ad 2: 1 click + 1 view = CTR of (1/2) × 100 = 50.00%

Only ads with clicks or views are included in the output.

Constraints

1 ≤ ad_id, user_id ≤ 100
action is an enum of ('Clicked', 'Viewed', 'Ignored')
The number of rows in the table is between 1 and 100

Visualization

Tap to expand

Understanding the Visualization

Input

Ad interactions with actions

Group & Count

Group by ad_id, count clicks/views

Calculate

Compute CTR percentage

Key Takeaway

🎯 Key Insight: Use conditional aggregation to separate different action types and calculate performance metrics efficiently

Asked in

M Meta 3 A Amazon 2

Use GROUP BY with ad_id and conditional aggregation with SUM(CASE WHEN...) to count clicks and views separately. Calculate CTR as (clicks / (clicks + views)) * 100 and filter out ads with no engagement using HAVING.

Table Schema

Ads

Column Name	Type	Description
`ad_id` PK	int	Advertisement ID
`user_id` PK	int	User ID who performed the action
`action`	enum	Action taken: 'Clicked', 'Viewed', or 'Ignored'

Primary Key: (ad_id, user_id)

Note: Each row represents one user's action on a specific ad. Only 'Clicked' and 'Viewed' actions count toward CTR calculation.

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY with Conditional Aggregation	O(n)	O(k)	Group by ad_id and use conditional SUM to count clicks and views separately

GROUP BY with Conditional Aggregation — Algorithm Steps

Step 1: Group all records by ad_id
Step 2: Count clicks using SUM(CASE WHEN action = 'Clicked')
Step 3: Count views using SUM(CASE WHEN action = 'Viewed')
Step 4: Calculate CTR formula and filter out ads with no clicks/views

Visualization

Tap to expand

Step-by-Step Walkthrough

Group

Group by ad_id

Count

Count clicks and views

Calculate

Compute CTR percentage

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single scan with grouping

✓ Linear Growth

Space Complexity

O(k)

Space for k unique ads

✓ Linear Space

28.5K Views

Medium Frequency

~12 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

GROUP BY with Conditional Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler