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
- 10
- Interview questions
- 9
- Projects & case studies
- 8
- Reading time
- ~1 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.
Learn joins first, then aggregation and window functions. 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 Joins Explained for Data EngineersUnderstand INNER, LEFT, FULL and CROSS joins, why row counts change after a join, and the filtering mistakes that silently break pipeline results.
- SQL Aggregations, GROUP BY and HAVINGHow GROUP BY, aggregate functions and HAVING work, how NULLs change COUNT and AVG, and the conditional aggregation patterns used in reporting pipelines.
Intermediate
Patterns used in production pipelines.
- SQL Window Functions: PARTITION BY, ORDER BY and FramesLearn how SQL window functions compute rankings, running totals and row-to-row comparisons without collapsing rows, including ties and frame behaviour.
- CTEs vs Subqueries vs Temporary TablesWhen to use a common table expression, a subquery or a temporary table in SQL pipelines, with readability, reuse and performance trade-offs explained.
Advanced
Performance, internals and edge cases.
- SQL Query Optimization FundamentalsA practical process for speeding up slow SQL: read the query plan, reduce the data scanned, make filters sargable, fix joins and verify the result is unchanged.
- 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.
- 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.
- 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.
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 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.
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.