Lesson 1Filtering methods: WHERE vs HAVING, use of EXISTS, IN, correlated subqueriesDevelop accurate filtering strategies for analytical queries. Compare WHERE and HAVING, use EXISTS and IN for subquery filters, and apply correlated subqueries to express complex, row-specific analytical conditions.
WHERE vs HAVING in grouped queriesUsing IN and NOT IN with subqueriesEXISTS and NOT EXISTS for semi joinsCorrelated subqueries for row-aware logicHandling NULLs in filter conditionsPerformance tips for complex filtersLesson 2SQL data types and date/time handling (DATE, TIMESTAMP, numeric precision)Learn the main SQL data types used in analytics and how they affect storage, precision, and calculations. Understand numeric scales, text handling, and reliable date and timestamp operations for time-based analysis.
Numeric types and precision for metricsCharacter and text data considerationsDATE vs TIMESTAMP and time zonesCasting and converting between typesDate arithmetic and interval calculationsExtracting parts of dates for groupingLesson 3Aggregations and grouping: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAXLearn to summarise data with aggregations and grouping. Use GROUP BY and HAVING to build metrics, apply COUNT, SUM, AVG, MIN, and MAX, and design strong aggregate queries for dashboards and reports.
GROUP BY fundamentals and syntaxAggregate functions COUNT and SUMAVG, MIN, and MAX for distributionsHAVING to filter aggregated resultsGrouping by expressions and bucketsDealing with NULLs in aggregatesLesson 4Loading CSVs into databases: COPY, LOAD DATA, sqlite import, and common pitfallsLearn practical ways to load CSV data into databases for analysis. Use COPY, LOAD DATA, and SQLite import, handle delimiters and encodings, and avoid common pitfalls that cause bad or partial loads.
Preparing CSVs for reliable importsUsing COPY in PostgreSQL and similar systemsLOAD DATA for MySQL and compatible enginesSQLite .import workflow and optionsHandling encodings, delimiters, and quotesValidating row counts and rejected recordsLesson 5DDL and DML essentials: CREATE TABLE, ALTER, INSERT, UPDATE, DELETE, transaction controlLearn how DDL and DML shape and modify tables for analytics. Practice creating and altering schemas, inserting and updating data, deleting safely, and using transactions to ensure data integrity in analytical workflows and pipelines.
Creating analytical tables with CREATE TABLEModifying schemas safely with ALTER TABLEINSERT patterns for bulk and incremental loadsUPDATE and DELETE with safe predicatesCOMMIT, ROLLBACK, and transaction scopeACID properties in analytical workloadsLesson 6Query basics: SELECT, WHERE, ORDER BY, LIMIT, DISTINCTMaster core query syntax used in nearly every analysis. Learn how SELECT retrieves columns, WHERE filters rows, ORDER BY sorts results, LIMIT controls sample size, and DISTINCT removes duplicates in analytical queries.
SELECT list design and column aliasesFiltering rows with WHERE conditionsSorting results with ORDER BYLIMIT and OFFSET for sampling dataUsing DISTINCT to remove duplicatesBasic query debugging and refinementLesson 7Joins and set operations: INNER, LEFT, RIGHT, FULL, CROSS, UNION, EXCEPT, INTERSECTUnderstand how joins and set operations combine datasets for analysis. Learn when to use each join type, how to avoid duplication errors, and how UNION, EXCEPT, and INTERSECT support complex analytical comparisons.
INNER JOIN for intersecting datasetsLEFT, RIGHT, and FULL OUTER JOIN use casesCROSS JOIN and Cartesian products in analysisUNION vs UNION ALL for stacking dataEXCEPT and INTERSECT for set comparisonsDetecting and handling join duplicationLesson 8Relational database concepts: tables, primary/foreign keys, normalisation vs denormalisationUnderstand core relational concepts that underpin analytical schemas. Learn tables, primary and foreign keys, normalisation forms, and when to denormalise for performance in reporting and BI workloads.
Tables, rows, and columns in practicePrimary keys and uniqueness constraintsForeign keys and referential integrityNormalization forms and redundancy controlDenormalization for reporting performanceStar and snowflake schemas overviewLesson 9Performance basics: indexes, query plans, explain/analyze, simple optimisation strategies for analytical queriesGain a practical view of query performance for analytics. Learn how indexes work, read query plans, use EXPLAIN and ANALYZE, and apply simple optimisation strategies to keep analytical queries efficient.
How indexes speed up lookups and joinsReading and interpreting query plansUsing EXPLAIN and ANALYZE in practiceIdentifying slow filters and joinsOptimizing GROUP BY and aggregationsBasic indexing strategies for analyticsLesson 10Window functions overview: ROW_NUMBER, RANK, DENSE_RANK, SUM() OVER(), AVG() OVER(), PARTITION BYExplore window functions to perform advanced analytics without collapsing rows. Learn ranking, running totals, moving averages, and partitioning strategies that power cohort, trend, and segmentation analysis in SQL.
Window function syntax and OVER clauseROW_NUMBER, RANK, and DENSE_RANK use casesRunning totals with SUM() OVER()Moving averages with window framesPARTITION BY for cohort and segment logicORDER BY in windows vs query ordering
