SQL — Senior · Resources

1Advanced window functions

0/4▶

Senior SQL means fluent analytics: neighbours, frames, and multi-level aggregation.

Window mastery

LAG() / LEAD() — month-over-month growth, gaps between events.

Mode — LAG/LEAD.

Frame clauses (ROWS BETWEEN …) for moving averages / rolling windows.

PostgreSQL — frames.

NTILE, FIRST_VALUE/LAST_VALUE, cumulative distributions.

PostgreSQL — window functions.

Gaps-and-islands problems (sessionisation).

Mode — Window functions.

2Indexes & EXPLAIN

0/5▶

Often its own round. You must read a plan and know why a query is slow — and fix it.

High priority

How a B-tree index works; composite index column order — why (a,b) helps WHERE a=… but not WHERE b=… alone.

Use The Index, Luke.

EXPLAIN / EXPLAIN ANALYZE — spot a sequential scan on a big table and fix it.

PostgreSQL — Using EXPLAIN.

When indexes aren't used: functions on the column, leading LIKE '%x', type mismatch, low selectivity.

Use The Index, Luke — WHERE clause.

Covering indexes / index-only scans; SELECT * vs explicit columns.

Use The Index, Luke.

Partial & expression indexes; when they pay off.

PostgreSQL — Partial indexes.

3Transactions & concurrency

0/4▶

Correctness under concurrent writes — the senior differentiator in data work.

ACID in practice

ACID; isolation levels and the anomalies they prevent (dirty / non-repeatable / phantom).

PostgreSQL — Transaction isolation.

Optimistic vs pessimistic locking; SELECT … FOR UPDATE; deadlocks.

PostgreSQL — Explicit locking.

MVCC in one minute; why readers don't block writers in Postgres.

PostgreSQL — MVCC.

Parameterized queries to prevent SQL injection.

OWASP — SQL Injection.

4Optimisation traps

0/4▶

The classic reasons a query or app is slow — recognise them on sight.

Common slowdowns

The N+1 query problem and how to fix it (joins, batching, dataloader).

Stack Overflow — N+1.

Keyset (cursor) pagination — why OFFSET 100000 is slow.

Use The Index, Luke — No OFFSET.

Over-fetching & chatty queries; batching round-trips.

Use The Index, Luke.

Statistics/ANALYZE & why the planner picks a bad plan.

PostgreSQL — Planner stats.

5Partitioning (intro)

0/2▶

When one table gets huge — the first scaling lever before full sharding.

Split big tables

Table partitioning (range/list/hash) and pruning.

PostgreSQL — Partitioning.

Archiving/retention of old partitions; time-based partitioning.

PostgreSQL — Partitioning.