Ads Performance

Problem

Table Ads(ad_id, user_id, action) where action is an enum of 'Clicked', 'Viewed', or 'Ignored', and (ad_id, user_id) is the primary key. For each ad, compute its click-through rate:

ctr = 0                              if clicks + views = 0
ctr = clicks * 100 / (clicks + views) otherwise

Round to two decimals, and order by ctr DESC, ad_id ASC.

Mechanism

Each SUM(CASE WHEN action = 'X' THEN 1 ELSE 0 END) turns a row-level enum into a per-group counter, so one GROUP BY ad_id pass pivots the action column into as many tallies as you write CASE arms — here, one for clicks and one for clicks+views — and the ratio of those two tallies is the CTR.

SUM(CASE) is a pivot, not a filter

The key idea: a CASE inside an aggregate maps each row to a number, and the aggregate folds those numbers per group. SUM(CASE WHEN cond THEN 1 ELSE 0 END) is therefore a conditional count — it counts rows where cond holds, but unlike WHERE, every row stays in the group. That is what lets you count clicks and count clicks+views in the same scan, side by side, without two subqueries or a self-join. Add another CASE arm and you have another column. This is the bread-and-butter SQL pivot.

Solution

SELECT ad_id,
       IFNULL(
         ROUND(
           SUM(CASE WHEN action = 'Clicked' THEN 1 ELSE 0 END)
           / SUM(CASE WHEN action IN ('Clicked','Viewed') THEN 1 ELSE 0 END)
           * 100,
         2),
       0) AS ctr
FROM   Ads
GROUP  BY ad_id
ORDER  BY ctr DESC, ad_id ASC;

The numerator counts clicks; the denominator counts clicks+views; their ratio times 100 is the CTR. ROUND(..., 2) meets the two-decimal requirement. IFNULL(..., 0) handles the all-ignored ad. The ORDER BY sorts by rate descending, breaking ties by ad_id ascending.

Worked trace

Take this input:

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

Step 1 — conditional counts per group. One scan, two tallies:

Step 2 — ratio * 100, rounded. Note ad 5's denominator is 0:

Step 3 — order. By ctr DESC, ad_id ASC: 1 (66.67), 3 (50.00), 2 (33.33), 5 (0.00).

ad_id | ctr
  1   | 66.67
  3   | 50.00
  2   | 33.33
  5   |  0.00

The part that's actually engine-specific

For ad 5 the denominator is 0, so the expression is 0 / 0. The whole solution leans on one fact: in MySQL, division by zero yields NULL rather than raising an error. ROUND(NULL, 2) stays NULL, and only then does IFNULL(..., 0) have something to catch — it converts that NULL to 0.00. So IFNULL is not guarding against "the value is 0"; it is guarding against "the division produced NULL."

This is not portable. PostgreSQL raises division by zero and aborts the query — IFNULL never runs, and Postgres doesn't even have IFNULL (use COALESCE). On Postgres you must prevent the division, e.g. with NULLIF(denominator, 0) so the divisor becomes NULL (and the division yields NULL safely), then COALESCE(..., 0):

-- Portable form (Postgres, and works on MySQL too)
SELECT ad_id,
       COALESCE(
         ROUND(
           SUM(CASE WHEN action = 'Clicked' THEN 1 ELSE 0 END) * 100.0
           / NULLIF(SUM(CASE WHEN action IN ('Clicked','Viewed') THEN 1 ELSE 0 END), 0),
         2),
       0) AS ctr
FROM   Ads
GROUP  BY ad_id
ORDER  BY ctr DESC, ad_id ASC;

Here NULLIF(denom, 0) turns a 0 denominator into NULL before the engine attempts the divide, so no engine ever sees a literal x/0. This is the version to reach for in production.

Pitfalls

• Integer division truncating to 0. If clicks and the total are both integers, clicks / total may do integer division in some engines (and 1/3 becomes 0, not 0.333). Multiplying by 100 first — clicks * 100 / total — keeps the numerator large, but the robust fix is to force float, e.g. * 100.0, so the divide is floating-point. The MySQL solution above survives only because MySQL's / is always floating-point; on engines where / truncates, you'd get 0 / 100 / 33 instead of the real rates.
• Counting with WHERE instead of CASE. A WHERE action='Clicked' drops the viewed and ignored rows, so you can no longer compute the denominator in the same query. Conditional aggregation keeps all rows in the group; that is the whole point.
• Assuming x/0 = NULL everywhere. As above, that holds on MySQL but Postgres/Oracle/SQL Server error out. Code written against LeetCode (MySQL) breaks silently when ported. Prefer NULLIF(denom, 0).
• Ads with zero rows. An ad that appears in no row of Ads won't appear in the output at all — GROUP BY only produces groups for rows that exist. This problem only requires CTR for ads present in the table, so that's fine here, but it's a real gap if you expect every ad in some master list.
• Tie-break order forgotten. Omitting , ad_id ASC leaves ties (e.g. two ads at 50.00) in engine-defined order, which fails exact-match graders.

Takeaways

• SUM(CASE WHEN cond THEN 1 ELSE 0 END) is a conditional count — it pivots a column into several per-group tallies in a single scan, where WHERE could only give you one.
• This solution's zero-denominator handling is two-stage: the engine turns 0/0 into NULL, and IFNULL/COALESCE turns that NULL into 0 — IFNULL alone does nothing without the NULL.
• That x/0 → NULL behavior is MySQL-specific; write NULLIF(denom, 0) and COALESCE for code that ports to Postgres and friends.
• Force floating-point division (* 100.0) so integer truncation can't quietly zero out your rates.

Problem from LeetCode 1322 "Ads Performance." Division-by-zero semantics per the MySQL Reference Manual (arithmetic operators return NULL on divide-by-zero) and the PostgreSQL manual (raises division_by_zero); NULLIF/COALESCE usage per the SQL standard. Re-authored and deepened for this guide: added the SUM(CASE)-as-pivot mechanism, a three-step worked trace (66.67 / 50.00 / 33.33 / 0.00), the engine-specific divide-by-zero analysis, and a portable Postgres-safe variant.