Ads Performance - Problem

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
Ads Performance - CTR Calculation INPUT: Ads Table ad_id action user_id 1 Clicked 1 2 Clicked 2 3 Viewed 3 5 Ignored 5 1 Viewed 4 2 Viewed 5 1 Clicked 2 Action Types: Clicked Viewed Ignored Ignored actions excluded from CTR calculation ALGORITHM STEPS 1 Group by ad_id Aggregate clicks and views for each advertisement 2 Count Actions SUM(CASE WHEN action= 'Clicked'...) as clicks 3 Calculate CTR CTR = clicks / (clicks + views) * 100 4 Sort Results ORDER BY ctr DESC, ad_id ASC ROUND(100 * clicks / (clicks + views), 2) FINAL RESULT ad_id ctr 1 66.67 2 50.00 3 0.00 5 NULL Calculation Breakdown: Ad 1: 2 clicks, 1 view CTR = 2/(2+1)*100 = 66.67 Ad 2: 1 click, 1 view CTR = 1/(1+1)*100 = 50.00 Ad 3: 0 clicks, 1 view CTR = 0/(0+1)*100 = 0.00 Key Insight: CTR measures ad effectiveness by tracking user engagement. Ignored actions are excluded because they represent users who saw the ad but took no meaningful action. Handle NULL case when clicks + views = 0 (only ignored actions). Use IFNULL or COALESCE to return 0 for ads with no valid interactions. ROUND to 2 decimals for clean output. TutorialsPoint - Ads Performance | Optimal Solution

Related Problems

Asked in
Meta 3 Amazon 2
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