Advanced SQL Basics

9 examples to get you started with advanced SQL - 6 basic and 3 intermediate.

Prerequisites

Comfort with JOIN and GROUP BY from SQL Querying section.

# Local dev with Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Basic Examples

1. CTE Pipeline

Name intermediate steps.

WITH recent AS (
  SELECT * FROM app.orders WHERE created_at > now() - interval '7 days'
)
SELECT account_id, count(*) FROM recent GROUP BY 1;

• Improves readability
• Planner may inline or materialize
• Not a performance magic wand

2. Window Row Number

Rank without GROUP BY collapse.

SELECT id, account_id,
  row_number() OVER (PARTITION BY account_id ORDER BY created_at DESC) AS rn
FROM app.orders;

• PARTITION BY defines groups
• ORDER BY in OVER defines ranking
• Filter rn in outer query

3. LAG for Previous Row

Compare to prior event per partition.

SELECT id, total,
  lag(total) OVER (PARTITION BY account_id ORDER BY id) AS prev_total
FROM app.orders;

• LAG/LEAD common in analytics
• Default frame is RANGE UNBOUNDED PRECEDING
• NULL when no prior row

4. FILTER in Aggregates

Conditional counts in one pass.

SELECT account_id,
  count(*) FILTER (WHERE total > 100) AS big_orders
FROM app.orders GROUP BY 1;

• Cleaner than SUM(CASE)
• Works with many aggregates
• Same query scans once

5. Readable Formatting

Align CTEs and JOINs.

WITH open_orders AS (
  SELECT * FROM app.orders WHERE status = 'open'
)
SELECT a.name, o.total
FROM open_orders o
JOIN app.accounts a ON a.id = o.account_id;

• Reviewers catch fan-out early
• Keep lines under team width
• Store in .sql files

6. Comment Intent

SQL comments for non-obvious business rules.

-- Revenue recognized only for shipped orders
SELECT sum(total) FROM app.orders WHERE status = 'shipped';

• Comments survive in migration history
• Do not comment obvious SQL
• Link ticket ids sparingly

Intermediate Examples

7. EXPLAIN Complex Query

Baseline before tuning.

EXPLAIN (ANALYZE, BUFFERS)
WITH x AS (SELECT account_id, sum(total) s FROM app.orders GROUP BY 1)
SELECT * FROM x WHERE s > 1000;

• Check sort and hash aggregate nodes
• Work_mem spills to disk if too low
• Compare after index changes

8. LATERAL Preview

Correlated subquery per row.

SELECT a.id, latest.total
FROM app.accounts a
JOIN LATERAL (
  SELECT total FROM app.orders o
  WHERE o.account_id = a.id ORDER BY created_at DESC LIMIT 1
) latest ON true;

• LATERAL is powerful and sharp
• Needs index on (account_id, created_at)
• See dedicated LATERAL article

9. Pivot with FILTER

Conditional aggregation pivot.

SELECT account_id,
  sum(total) FILTER (WHERE status='open') AS open_total,
  sum(total) FILTER (WHERE status='closed') AS closed_total
FROM app.orders GROUP BY 1;

• Avoids crosstab for few columns
• Dynamic columns need dynamic SQL or crosstab
• See pivot article

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.