Course · Languages & query
SQL for Data Engineers
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
- 30
- Projects & case studies
- 13
- Reading time
- ~8 h
Your progress
Saved in this browser onlyCourse structure
- 1 lessonStart hereThe complete overview of the course in one read.
- 5 lessonsBeginnerCore concepts you will use every day.
- 7 lessonsIntermediatePatterns used in production pipelines.
- 10 lessonsAdvancedPerformance, internals and edge cases.
Practise
- 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.
Practice
25 problems in 2 topics, from basic to advanced. 0 of 25 done.
Core SQL interview questions0/9
- Explain INNER JOIN vs LEFT JOIN with a practical example.EasyDataDank only
- Find customers who never placed an order, and explain NOT IN vs NOT EXISTS.EasyLeetCode (opens in a new tab)
- How do window functions differ from GROUP BY?EasyDataDank only
- Find the second-highest salary without using a simple MAX approach.MediumLeetCode (opens in a new tab)
- How would you detect and remove duplicate records safely?MediumLeetCode (opens in a new tab)
- Return the top three salaries in each department, including ties.MediumLeetCode (opens in a new tab)
- Revenue doubled after you joined a second table. What happened and how do you fix it?MediumDataDank only
- How would you optimize a slow analytical SQL query?HardDataDank only
- Write an SCD Type 2 upsert in SQL that closes old versions and inserts new ones.HardDataDank only
Business case studies0/16
- Average Order ValueMediumDataDank only
- Customer Lifetime ValueMediumDataDank only
- Daily Active UsersMediumDataDank only
- Lead ScoringMediumDataDank only
- New vs Returning CustomersMediumDataDank only
- Refund AnalysisMediumDataDank only
- Session DurationMediumDataDank only
- Subscription RenewalsMediumDataDank only
- Top Selling ProductsMediumDataDank only
- A/B Test ResultsHardDataDank only
- Churn RateHardDataDank only
- Conversion FunnelHardDataDank only
- Employee HierarchyHardDataDank only
- Fraud Pattern DetectionHardDataDank only
- Inventory TurnoverHardDataDank only
- Monthly RevenueHardDataDank only
Ticks are saved in this browser only and match the ticks on the interview pages.
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 EXCEPTSQL UNION, UNION ALL, INTERSECT and EXCEPT explained: how duplicates and NULLs are handled, column matching rules and data reconciliation patterns.
- SQL Joins: INNER, LEFT, RIGHT, FULL, Self and Cross JoinsSQL joins explained: INNER, LEFT, RIGHT and FULL OUTER, self and cross joins, multi-table joins, and how to predict row counts and avoid fan-out traps.
Intermediate
Patterns used in production pipelines.
- Semi-Joins, Anti-Joins and LATERAL Joins in SQLSQL semi and anti joins: use EXISTS and NOT EXISTS to avoid duplicate rows, dodge the NOT IN NULL trap, and use LATERAL or CROSS APPLY for per-row top-N.
- SQL Subqueries and CTEs: Derived Tables, Correlated Queries and Temporary TablesSQL CTEs and subqueries: write them in WHERE, FROM and SELECT, understand correlated subqueries, chain CTEs, and know when a temp 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_VALUESQL window functions: OVER and PARTITION BY, ROW_NUMBER, RANK and DENSE_RANK with ties, NTILE buckets, LAG and LEAD, and FIRST_VALUE and LAST_VALUE.
- Window Frames: Running Totals, Moving Averages and PercentilesSQL window frames: running totals, moving averages with ROWS and RANGE, conditional window aggregates, percent of total, CUME_DIST and PERCENTILE_CONT median.
- 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 QueriesThree SQL pipeline patterns: 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 AttributionSQL retention and cohort analysis: day-N retention, cohort matrices, ordered funnels, first and last touch attribution and market basket lift queries.
- 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 DistinctSQL performance at scale: tune window functions, fix skewed keys with two-phase aggregation and salting, and count distinct values with HyperLogLog.
- Transactions, Isolation Levels, Locking and MVCCSQL transactions and isolation levels: ACID, the anomalies each level allows, row locks and deadlocks, and how MVCC lets readers and writers run together.
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, and prove that replays, duplicates and out-of-order delivery cannot corrupt the copy.
- 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, corrected or contain bad rows.
- IntermediateE-commerce Analytics Data PlatformAn online store has orders, customers, products, refunds and web sessions in separate systems. Build an ELT platform that models them into trusted marts for revenue, conversion and repeat customers, with every metric defined once and tested.
- AdvancedFraud Detection Data PipelineBuild the data side of a fraud-detection system: compute per-card behavioural features from a transaction stream using only past data, flag suspicious transactions with transparent rules, keep a point-in-time-correct feature table that a model could train on, and backtest the rules honestly.
- 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, correct when events arrive late or twice, and reconciled with a batch recount.
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 Churn Prediction Data PipelineDesign the data pipeline behind a churn prediction model for a subscription business: define churn labels, build leak-free features from product usage, billing and support data, produce reproducible training datasets, score every active customer daily, and deliver the scores to the customer-success team's tools.
- 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.
- AdvancedDesign a Self-Serve Analytics PlatformDesign a platform that lets 1,000 employees across product, marketing, finance and operations find trustworthy data, answer their own questions with SQL or a BI tool, and build their own dashboards and models, without a central data team writing every query, and without losing control of data quality, access to sensitive data or cost.
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, dbt, semantic layers and incremental models.