Advanced Patterns

Pivoting, gap detection, and query optimization basics.

How it works

These are the patterns that come up when you're doing serious work with SQL — transforming data shapes, cleaning messy data, and understanding why a query is slow.

SELECT
  user_id,
  SUM(CASE WHEN action = 'view'    THEN 1 ELSE 0 END) AS views,
  SUM(CASE WHEN action = 'click'   THEN 1 ELSE 0 END) AS clicks,
  SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS purchases
FROM events
GROUP BY user_id;

-- Keep the latest row per user
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY created_at DESC
    ) AS rn
  FROM events
)
SELECT * FROM ranked WHERE rn = 1;

SELECT id + 1 AS gap_start
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM orders o2 WHERE o2.id = o.id + 1
)
ORDER BY gap_start;