Complete SQL Roadmap — Beginner to Data Engineer (2026)

Stage 0 — Setup (1 day)

• Install PostgreSQL locally (or SQLite for zero-setup).
• Optionally: pgAdmin, DBeaver, or TablePlus as a GUI.
• Even simpler: db-fiddle.com for browser-based practice.

Stage 1 — Basic SELECT (1 week)

• SELECT col1, col2 FROM table.
• WHERE predicates — =, <, >, <>, IN, BETWEEN, LIKE, IS NULL.
• ORDER BY, LIMIT, OFFSET.
• Aliases — AS.
• DISTINCT.

Practice: SQLBolt, Mode Analytics SQL tutorial, LeetCode "Easy" SQL.

Stage 2 — Aggregation (1 week)

• COUNT, SUM, AVG, MIN, MAX.
• GROUP BY + HAVING.
• COUNT(DISTINCT col).
• The classic mistake: WHERE filters BEFORE grouping, HAVING filters AFTER.

Stage 3 — Joins (2 weeks)

• INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.
• Multi-table joins.
• Self-joins (employees → manager).
• CROSS JOIN and when (not) to use it.

Practice: HackerRank "Advanced Select" and "Aggregations".

Stage 4 — Subqueries + CTEs (1 week)

• Scalar subqueries, correlated subqueries.
• IN, EXISTS, NOT EXISTS.
• Common Table Expressions (WITH … AS).
• Recursive CTEs (e.g. hierarchical queries).

Stage 5 — Window Functions (1 week — high ROI)

• ROW_NUMBER, RANK, DENSE_RANK.
• LAG, LEAD, FIRST_VALUE, LAST_VALUE.
• SUM/AVG/COUNT OVER (PARTITION BY … ORDER BY …).
• NTILE for percentiles.

These are the questions that separate junior from mid-level in interviews.

Stage 6 — DDL + Schema Design (2 weeks)

• CREATE TABLE, column types, NOT NULL, DEFAULT.
• Primary keys, unique keys, foreign keys, check constraints.
• ALTER TABLE.
• Normalisation — 1NF, 2NF, 3NF.
• When to denormalise.
• Indexes — B-tree, hash, partial, functional.

Stage 7 — Transactions + Concurrency (1 week)

• ACID.
• BEGIN / COMMIT / ROLLBACK / SAVEPOINT.
• Isolation levels.
• Locking — row vs table, shared vs exclusive.
• Deadlocks — detection and avoidance.

Stage 8 — Query Performance (2 weeks)

• EXPLAIN + EXPLAIN ANALYZE — read the plan.
• Index usage — when the optimiser picks it.
• Composite indexes + the left-prefix rule.
• N+1 queries + how to fix them.
• Pagination at scale — keyset (seek) pagination over LIMIT/OFFSET.
• Query rewrites — EXISTS vs IN, JOIN vs subquery.

Stage 9 — Advanced PostgreSQL (3 weeks)

• JSON / JSONB columns + GIN indexes.
• Full-text search (tsvector, tsquery, to_tsvector).
• Arrays + unnest.
• LATERAL joins.
• Materialised views.
• pg_stat_statements + auto_explain.
• Logical replication.

Stage 10 — Beyond Single-Server (ongoing)

• Read replicas — async copies for scaling reads.
• Sharding — split data across many DBs by a key.
• CDC (Change Data Capture) — Debezium, Kafka Connect.
• OLAP databases — ClickHouse, BigQuery, Snowflake, DuckDB.
• dbt for analytics modelling.

Stage 11 — Specialisation (pick one)

Analytics / Data Engineering

• Window functions everywhere.
• Cube / rollup / grouping sets.
• ETL with dbt.
• Airflow / Dagster for orchestration.
• Star / snowflake schemas.

Backend / OLTP

• Connection pooling — PgBouncer.
• Migrations — Alembic / Flyway / Atlas.
• ORM mastery — but knowing when to drop down to raw SQL.

DBA / Performance

• Vacuum + autovacuum tuning.
• pg_repack, pg_partman.
• Replication topologies.

Capstone Projects

• Build a leaderboard with window functions on real game data.
• A small data warehouse for an e-commerce site using dbt + Postgres.
• A recommendation engine query using SQL + materialised views.

Time investment

Full-time: 2-3 months to genuinely good.
Part-time (5 hr/week): 6-9 months.

SQL is the highest-ROI skill in tech. Almost every backend, data, and analytics job needs it.