sql¶

1.1 Clause execution order¶

FROM, JOIN, ON - Take in all source tables. Join them to create a single large table.
WHERE - Filter the large combined table to remove unnecessary rows.
GROUP BY
HAVING
Window functions, SELECT, distinct() - Select required columns from the table. Apply window functions if required.
ORDER BY
LIMIT
Return the data as result.

1.2 Gaps and islands¶

Island = run of consecutive rows. Gap = missing range between islands.

Pattern A — row_number difference (consecutive ints/dates) value - ROW_NUMBER() is constant inside an island.

WITH g AS (
  SELECT user_id, login_date,
         DATE_SUB(login_date,
           INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM (SELECT DISTINCT user_id, login_date FROM logins) d
)
SELECT user_id, MIN(login_date) start_dt, MAX(login_date) end_dt, COUNT(*) streak
FROM g GROUP BY user_id, grp;

Q: longest login streak, consecutive days of sales, consecutive free seats.

Pattern B — LAG change flag + running sum (value-based islands)

WITH f AS (
  SELECT *, CASE WHEN status = LAG(status) OVER (PARTITION BY id ORDER BY ts)
                 THEN 0 ELSE 1 END AS chg
  FROM events
), g AS (
  SELECT *, SUM(chg) OVER (PARTITION BY id ORDER BY ts) AS grp FROM f
)
SELECT id, status, MIN(ts) start_ts, MAX(ts) end_ts
FROM g GROUP BY id, status, grp;

Q: collapse repeated statuses, machine uptime blocks, price-up streaks.

Pattern C — sessionization (gap threshold)

chg = CASE WHEN LAG(ts) OVER w IS NULL
            OR ts > LAG(ts) OVER w + INTERVAL '30' MINUTE THEN 1 ELSE 0 END
session_id = SUM(chg) OVER (PARTITION BY user_id ORDER BY ts)

Pattern D — gaps via LEAD

SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (SELECT id, LEAD(id) OVER (ORDER BY id) next_id FROM t) x
WHERE next_id - id > 1;

Q: missing invoice numbers, free meeting slots, calendar gaps.

Pattern E — overlapping interval merge

WITH m AS (
  SELECT *, MAX(end_ts) OVER (PARTITION BY id ORDER BY start_ts
              ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max
  FROM intervals
), f AS (
  SELECT *, CASE WHEN prev_max >= start_ts THEN 0 ELSE 1 END AS chg FROM m
)
SELECT id, MIN(start_ts), MAX(end_ts)
FROM (SELECT *, SUM(chg) OVER (PARTITION BY id ORDER BY start_ts) grp FROM f) g
GROUP BY id, grp;

Gotchas: dedupe before Pattern A; ties break row_number; use DENSE_RANK if duplicates allowed.