Compare WHERE vs HAVING with aggregates

Filter groups based on an aggregate and explain WHERE vs HAVING. Provide a query that returns merchants with chargeback_rate > 0.5% in the last 30 days using HAVING. Clarify why WHERE cannot reference aggregates, how HAVING works after GROUP BY, dialect differences (e.g., MySQL allowing expressions in HAVING without GROUP BY), and performance considerations.

Clarifying Questions to Ask

• Clarify SQL dialect or Python library versions, date/time semantics, duplicate handling, and null handling.
• Define the grain of each intermediate result before aggregating.
• State expected output columns and ordering explicitly.

What a Strong Answer Covers

• A query or pandas plan that matches the requested output grain.
• Correct joins, filters, grouping, window functions, and treatment of NULLs or duplicates.
• A brief explanation of why the result is correct and how it handles edge cases.
• Performance notes, indexes/partitioning, and validation queries when relevant.

Follow-up Questions

• How would you test the query on a tiny hand-built dataset?
• What changes if duplicate events or late-arriving data are present?
• Which indexes, clustering, or partitions would help at production scale?

Solution

Solution Alignment

WHERE filters rows before grouping; HAVING filters groups after aggregate values are computed.

SELECT
    merchant_id,
    COUNT(*) AS total_transactions,
    SUM(CASE WHEN is_chargeback THEN 1 ELSE 0 END) AS chargebacks,
    SUM(CASE WHEN is_chargeback THEN 1 ELSE 0 END)::numeric / NULLIF(COUNT(*), 0) AS chargeback_rate
FROM transactions
WHERE transaction_time >= CURRENT_DATE - INTERVAL '30 day'
GROUP BY merchant_id
HAVING SUM(CASE WHEN is_chargeback THEN 1 ELSE 0 END)::numeric / NULLIF(COUNT(*), 0) > 0.005
ORDER BY chargeback_rate DESC;

The time filter belongs in WHERE because it applies to raw rows. The chargeback-rate filter belongs in HAVING because it depends on grouped aggregates. Some dialects allow aliases in HAVING; repeating the expression is more portable.