Ads Performance

Ads Performance

What is this problem about?

The Ads Performance interview question is a SQL-based challenge focusing on digital marketing metrics. You are given an Ads table containing ad_id, user_id, and action (which can be 'Clicked', 'Viewed', or 'Ignored'). You need to calculate the Click-Through Rate (CTR) for each ad.

The CTR formula is: $\text{CTR} = \frac{\text{Total Clicks}}{\text{Total Clicks} + \text{Total Views}} \times 100$.

Why is this asked in interviews?

Meta (Facebook) asks this because it mirrors their actual business logic. It evaluates a candidate's ability to handle Conditional Aggregation (counting only specific rows), avoid division-by-zero errors, and format percentages correctly.

Algorithmic pattern used

This follows the Database interview pattern of using CASE statements combined with SUM() or COUNT(). To handle the math properly:

1. Filter out 'Ignored' actions for the denominator.
2. Use IFNULL or COALESCE to handle cases where an ad has no clicks or views.
3. Group by ad_id and sort the results as specified (usually by CTR descending, then ad_id ascending).

Example explanation

If Ad 1 has:

• 2 Clicks
• 3 Views
• 5 Ignored The CTR is $\frac{2}{2+3} \times 100 = 40.00\%$. If Ad 2 has only "Ignored" actions, the denominator is 0. In this case, the CTR should be defined as 0.00%.

Common mistakes candidates make

• Division by Zero: Simply dividing clicks by (clicks + views) without checking if the sum is zero.
• Integer Division: In many SQL dialects, 2/5 is 0. You must multiply by 100.0 or cast to a float to get a decimal result.
• Rounding: Forgetting to round to exactly two decimal places as requested in the problem description.

Interview preparation tip

Practice the CASE WHEN action = 'Clicked' THEN 1 ELSE 0 END pattern. It is the most robust way to perform counts based on specific string values in SQL.