Advanced query patterns

Advanced SQL is usually a combination of fundamentals rather than a new language. The key is to define the desired grain and tie-breaking rules explicitly.

Latest row per entity

WITH ranked AS (
  SELECT e.*,
         ROW_NUMBER() OVER (
           PARTITION BY entity_id
           ORDER BY occurred_at DESC, id DESC
         ) AS rn
  FROM events AS e
)
SELECT entity_id, status, occurred_at
FROM ranked
WHERE rn = 1
ORDER BY entity_id;

Run the query to see the result.

Always define a deterministic tie-breaker.

Detecting duplicates

SELECT email,
       COUNT(*) AS occurrences
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC, email;

Run the query to see the result.

A duplicate query is only meaningful when the business key is correctly defined.

Keeping one duplicate and identifying extras

WITH ranked AS (
  SELECT id,
         email,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY updated_at DESC, id DESC
         ) AS rn
  FROM users
  WHERE email IS NOT NULL
)
SELECT id, email
FROM ranked
WHERE rn > 1
ORDER BY email, id;

Run the query to see the result.

Do not delete duplicate-looking rows until the survivor rule and dependent relationships are understood.

Detecting sequence gaps

WITH sequenced AS (
  SELECT entity_id,
         sequence_no,
         LAG(sequence_no) OVER (
           PARTITION BY entity_id
           ORDER BY sequence_no
         ) AS previous_sequence
  FROM events
)
SELECT entity_id, previous_sequence, sequence_no
FROM sequenced
WHERE previous_sequence IS NOT NULL
  AND sequence_no <> previous_sequence + 1
ORDER BY entity_id, sequence_no;

Run the query to see the result.

This is useful for event streams, imported records, telemetry, and ordered processing pipelines.

Keyset pagination

OFFSET pagination asks the database to skip rows and becomes unstable when data changes between pages. Keyset pagination says “give me the next rows after the last key I saw.”

For descending (created_at, id), the next-page predicate compares against both values. Exact tuple-comparison syntax is engine-specific, but the core rule is to preserve the complete sort key.

Expected versus actual reconciliation

EXCEPT can identify missing/mismatched expected rows and the reverse EXCEPT can identify unexpected actual rows.

SELECT id, status, amount FROM expected
EXCEPT
SELECT id, status, amount FROM actual;

Run the query to see the result.

Running state from an event/ledger stream

Window SUM, LAG/LEAD, and cumulative counts let you derive state while retaining every event. This pattern appears in financial ledgers, monitoring, test execution history, and audit trails.

Relational division / “has all required things”

Problems such as “customers who bought every required category” can be solved with nested NOT EXISTS or grouping/count comparisons. The important step is to define the required set and prove there are no missing members.

Practice: find the latest event per user rather than per entity, then compare the result with latest per entity and explain why the grains differ.