Ads Performance - Solution & Explanation

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.