Course · Languages & query
SQL
SQL is the core language of data work: querying, transforming and modelling data in warehouses, lakehouses and Spark. Start here before any other tool.
- Lessons
- 23
- Interview questions
- 13
- Projects & case studies
- 11
- Reading time
- ~8 h
About this course
SQL is where most Data Engineering work starts and ends. You use it to explore data, build transformations in a warehouse, define models and validate pipeline output. The same ideas carry over to Spark SQL, Snowflake, BigQuery and Delta Lake, so time spent here pays off across the whole stack.
Work through the lessons in order: query basics, operators and functions, then aggregation, set operations and joins, then subqueries, reshaping and window functions, and finally the time-series, deduplication and slowly changing dimension patterns that appear in almost every pipeline. After that, learn to read a query plan so you can explain why a query is slow.
Your progress
Saved in this browser onlyPractise
- InterviewSQL interview questionsThe full list with difficulty, type and a box to tick off each one.
- Cheat sheetSQL Data Engineering Cheat SheetA quick SQL reference for Data Engineers: join types, aggregation, window functions, deduplication, upserts and the mistakes that change row counts.
- InterviewAll interview questionsEvery question across all topics in one filterable list.
Course structure
Lessons
Work through the lessons in order. Completed lessons show a tick; lessons you have opened are outlined.
Start here
The complete overview of the course in one read.
Beginner
Core concepts you will use every day.
- SQL Operators, NULL Handling and CASE ExpressionsWrite correct SQL filters: comparison and logical operators, IN, BETWEEN and LIKE, three-valued NULL logic with COALESCE and NULLIF, and CASE WHEN expressions.
- SQL Functions: Strings, Dates, Type Casting and ArithmeticClean and convert data in SQL: string functions, date arithmetic, CAST and safe casting, integer division and rounding, with names for each major SQL dialect.
- SQL Aggregations: GROUP BY, HAVING and Conditional AggregationHow SUM, AVG, MIN, MAX and COUNT treat NULLs, how GROUP BY sets the grain, WHERE versus HAVING, and conditional aggregation for metrics built in one pass.
- SQL Set Operations: UNION, UNION ALL, INTERSECT and EXCEPTCombine and compare query results with UNION, UNION ALL, INTERSECT and EXCEPT: duplicate handling, NULL behaviour, column matching and data reconciliation patterns.
- SQL Joins: INNER, LEFT, RIGHT, FULL, Self and Cross JoinsPredict the row count of any SQL join: INNER, LEFT, RIGHT and FULL OUTER joins, self joins, cross joins and multi-table joins, plus the filtering and fan-out traps.
Intermediate
Patterns used in production pipelines.
- Semi-Joins, Anti-Joins and LATERAL Joins in SQLFilter with EXISTS and NOT EXISTS instead of joins that duplicate rows, avoid the NOT IN NULL trap, and use LATERAL or CROSS APPLY for per-row top-N and unnesting.
- SQL Subqueries and CTEs: Derived Tables, Correlated Queries and Temporary TablesWrite subqueries in WHERE, FROM and SELECT, understand correlated subqueries, structure logic with chained CTEs, and know when a temporary table is the better choice.
- PIVOT, UNPIVOT, GROUPING SETS, ROLLUP and CUBEReshape data in SQL: pivot rows to columns, unpivot columns to rows, handle dynamic pivot columns, and compute subtotals with GROUPING SETS, ROLLUP and CUBE.
- SQL Window Functions: Ranking, LAG/LEAD and FIRST_VALUELearn SQL window functions: OVER and PARTITION BY, ROW_NUMBER, RANK and DENSE_RANK with ties, NTILE buckets, LAG and LEAD comparisons, and FIRST_VALUE and LAST_VALUE.
- Window Frames: Running Totals, Moving Averages and PercentilesMaster SQL window frames: running totals, moving averages with ROWS and RANGE, conditional window aggregates, percent of total, CUME_DIST and median with PERCENTILE_CONT.
- Time-Series SQL: Date Buckets, Calendars, YoY, MoM and Rolling MetricsBuild time-series metrics in SQL: bucket dates, generate calendars to fill gaps, and compute month-over-month, year-over-year and rolling 7 and 30 day figures.
- Top-N per Group, Deduplication and SCD QueriesSolve three everyday pipeline problems in SQL: top N rows per group with ties, deduplicating to the latest record per key, and querying Type 2 slowly changing dimensions.
Advanced
Performance, internals and edge cases.
- Recursive CTEs: Hierarchies and Graph Traversal in SQLLearn how recursive CTEs work step by step, then use them to walk org charts, roll up hierarchies and traverse graphs safely without infinite loops.
- Gaps and Islands, Streaks and Sessionization in SQLSolve gaps-and-islands problems with window functions: find missing values, group consecutive rows, measure streaks and split clickstreams into sessions.
- Product Analytics SQL: Retention, Cohorts, Funnels and AttributionWrite the product analytics queries interviewers ask for: day-N retention, cohort matrices, ordered funnels, first and last touch attribution and market basket lift.
- Semi-Structured SQL: JSON, Arrays and Regular ExpressionsParse JSON, flatten nested arrays and extract text with regular expressions in SQL, with PostgreSQL examples and the Snowflake and BigQuery equivalents.
- SQL Query Optimization and Execution PlansA practical process for speeding up slow SQL: read EXPLAIN plans, avoid full table scans, rewrite queries for performance and spot common anti-patterns.
- SQL Indexes: B-tree, Composite, Covering and Bitmap IndexesHow database indexes work and how to design them: B-tree and composite column order, covering indexes, selectivity and cardinality, and bitmap indexing.
- Partitioning, Materialized Views, Columnar Storage and ShardingDesign table partitioning and check pruning in query plans, use materialized views safely, compare row and columnar storage, and choose a sharding key.
- Inside the Query Optimizer: Join Algorithms, Statistics and MemoryHow the optimiser chooses a plan: nested loop, hash and merge joins, table statistics, cardinality estimation, CTE materialisation and spilling to disk.
- SQL at Scale: Window Performance, Skew and Approximate DistinctKeep big queries fast: tune window functions, handle skewed keys in aggregations with two-phase aggregation and salting, and count distinct values with HyperLogLog.
- Transactions, Isolation Levels, Locking and MVCCUnderstand ACID transactions, isolation levels and the anomalies they allow, row locks and deadlocks, and how MVCC lets readers and writers work at the same time.
Projects and case studies
Apply what you learned and prepare material to discuss in interviews.
Projects
- AdvancedChange Data Capture PipelineReplicate an operational PostgreSQL table into a lakehouse table within minutes, including updates and deletes, so analysts query current data without touching the production database.
- BeginnerCSV to Data Warehouse PipelineA small online shop exports orders as daily CSV files. Build a pipeline that loads them into a star schema so that sales can be reported reliably, even when files are resent or contain bad rows.
- IntermediateE-commerce Analytics Data PlatformAn online store has orders, customers, products and web sessions in separate systems. Build an ELT platform that models them into trusted marts for revenue, retention and product performance.
- AdvancedFraud Detection Data PipelineBuild the data side of a fraud-detection system: compute per-card behavioural features from a transaction stream, flag suspicious transactions with transparent rules, and maintain a feature table that a model could use.
- AdvancedReal-Time Analytics PipelineBuild a pipeline that turns a stream of order events into per-minute revenue and order counts by category, visible on a dashboard within a minute, and correct even when events arrive late.
System design case studies
- AdvancedDesign an A/B Testing Data PipelineDesign the data pipeline behind a company's experimentation platform: record which users saw which variant, join that to behavioural and business events, and produce daily, statistically sound results for hundreds of concurrent experiments.
- AdvancedDesign an E-Commerce Inventory Sync SystemDesign a system that keeps product availability consistent across a retailer's website, mobile app, physical stores and third-party marketplaces, using stock data from several warehouse management systems, so that customers rarely see items that cannot be fulfilled and the business does not hide stock it could sell.
- AdvancedDesign a Financial Reconciliation PipelineDesign a daily batch pipeline that reconciles the company's internal payment ledger with settlement files from payment service providers (PSPs) and statements from banks, so finance can prove every transaction was received, settled and paid out, and can investigate every difference.
- AdvancedDesign a Marketing Attribution PipelineDesign a pipeline that credits conversions (sign-ups, purchases) to the marketing touchpoints that preceded them, joins that to ad spend from each advertising platform, and gives the marketing team daily return-on-spend by channel and campaign.
- AdvancedDesign an Order-Events Processing SystemDesign the event backbone for an e-commerce order lifecycle (created, paid, packed, shipped, delivered, cancelled, refunded) so that downstream services and analytics see every state change reliably and in order, stuck orders are detected, and a correct current state and full history of each order are always available.
- AdvancedDesign a Payment Events Pipeline with Exactly-Once ProcessingDesign the pipeline that carries payment events (authorised, captured, refunded, charged back) from the payments service and payment provider webhooks to a double-entry ledger, merchant balances and analytics, so that every payment is recorded exactly once, no money is created or lost by retries, and every number can be reconciled with the provider.
Resources
Cheat sheets
Related courses
- PythonPython glues pipelines together: ingestion, validation, orchestration and PySpark jobs. Focus on functions, generators, error handling and testable code.
- PySparkPySpark is the Python API for Apache Spark. Learn DataFrames, joins, window functions and how partitions and shuffles decide performance.
- Data modelingData modeling and warehousing: star schemas and grain, fact and dimension design, SCDs, Data Vault and other methods, dbt, semantic layers and incremental models.