SQL Aggregate Functions Practice

-- Same conditional count, 2 ways. The output rows are identical.
-- The query plan often isn't.

-- FILTER (Postgres, DuckDB, ANSI SQL since 2003):
SELECT
  department,
  COUNT(*) FILTER (WHERE status = 'active')  AS active_n,
  COUNT(*) FILTER (WHERE status = 'churned') AS churned_n,
  COUNT(*) FILTER (WHERE status = 'active' AND tier = 'pro') AS pro_active_n
FROM users
GROUP BY department;

-- CASE WHEN (universal; works on Snowflake, BigQuery, MySQL, every Postgres):
SELECT
  department,
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END)  AS active_n,
  SUM(CASE WHEN status = 'churned' THEN 1 ELSE 0 END) AS churned_n,
  SUM(CASE WHEN status = 'active' AND tier = 'pro' THEN 1 ELSE 0 END) AS pro_active_n
FROM users
GROUP BY department;

-- Postgres planner notes:
-- FILTER variant: HashAggregate (1 pass, internal filter predicates)
-- CASE  variant: HashAggregate (1 pass, internal CASE evaluation)
-- Output: identical. Cost: FILTER ~3-5% lower on average.

Both queries produce identical output. FILTER is cleaner on Postgres; CASE is universal across engines.

-- The double-counting trap: aggregating after a JOIN inflates the metric.

-- WRONG. Joining orders (1 row per order) to order_items (3-7 rows per order)
-- before averaging makes AVG(o.total) average each order N times where
-- N is its item count.
SELECT o.customer_id, AVG(o.total) AS avg_order
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.customer_id;

-- RIGHT, option A. Aggregate orders first, then join.
WITH per_customer AS (
  SELECT customer_id, AVG(total) AS avg_order
  FROM orders
  GROUP BY customer_id
)
SELECT pc.customer_id, pc.avg_order, COUNT(DISTINCT i.item_id) AS items
FROM per_customer pc
LEFT JOIN orders o ON o.customer_id = pc.customer_id
LEFT JOIN order_items i ON i.order_id = o.order_id
GROUP BY pc.customer_id, pc.avg_order;

-- RIGHT, option B. Use COUNT(DISTINCT) inside the AVG to deduplicate.
-- Useful when the JOIN is unavoidable.
SELECT o.customer_id,
       SUM(o.total) / COUNT(DISTINCT o.order_id) AS avg_order
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.customer_id;

The WRONG version inflates AVG by the average item count per order. Both RIGHT versions return the correct per-customer average.