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
- 22
- 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
21 problems in 2 topics, from basic to advanced. 0 of 21 done.
Core SQL interview questions0/5
- Explain INNER JOIN vs LEFT JOIN with a practical example.EasyDataDank only
- 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)
- How would you optimize a slow analytical SQL query?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 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 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 and other methods, dbt, semantic layers and incremental models.