Choose your character

It shows in the top bar and on your profile. Change it any time.

Courses / Data and analytics tools

SQL

Write real queries against a real database in your browser, from your first SELECT to expert analyst work.

Start lesson 1Practice interviewTake the assessmentPractice the first unit of every course. No account needed.
97lessons
507checked exercises
16projects, cases and exams
Prepares you for SQL interviews and tests

Projects, case studies and mock exams

Case study · BeginnerThe April numbers

The operations manager needs April in five numbers and a pick for the May newsletter.

6 steps · 25 minProject · IntermediateMonthly sales report

Build the monthly report finance asked for, one column at a time, ending with a query they can re-run every month.

8 steps · 50 minCase study · IntermediateThe spring coupon's impact

Marketing wants to know whether to run the SPRING20 coupon again. Compare coupon orders with the rest and make the call.

6 steps · 35 minProject · AdvancedFrom spreadsheet export to database

Turn the flat export from the old order system into customers, products, orders and order lines, with keys, and prove nothing was lost.

8 steps · 60 minProject · AdvancedClean the sign-up list

Turn a messy newsletter export into a clean, deduplicated list marketing can send to, and report where sign-ups come from.

7 steps · 45 minCase study · AdvancedMembership churn

Measure churn in Kestrel Plus by plan, tenure and buying behavior, and name the group to target first.

6 steps · 40 minCase study · AdvancedSupport response times

Measure how fast support answers against its targets, find what is still waiting, and name the weakest channel.

6 steps · 40 minProject · ExpertKPI pack for the leadership meeting

Build the first-half KPI pack the leadership team asked for, one number at a time, in a single query.

9 steps · 60 minProject · ExpertBuild a price history table

Build the price history the finance team needs, apply July price changes the right way, and answer as-of questions from it.

7 steps · 45 minCase study · ExpertCampaign attribution

Credit orders to the campaigns that led to them, compare first-touch and last-touch views, and find the cheapest campaign per order.

6 steps · 45 minCase study · ExpertWhat stockouts cost

Measure how often and how long products ran out, estimate the sales lost, and name the product to fix first.

6 steps · 45 minMock exam · ExpertSQL certification practice

Twelve questions in the style of a cloud data warehouse SQL certification: six to answer by reasoning, six to write. Allow 30 minutes.

12 questions · 30 min

Practice SQL the way you'll use it at work and in tests: in a query editor with a table browser and a results grid, against the tables of Kestrel, a made-up online store. Every query runs for real in DuckDB, which follows PostgreSQL's rules, so what you learn carries over to PostgreSQL, MySQL, SQL Server and the SQL tests used in hiring.

The course runs from Beginner to Expert. Each lesson teaches one thing in a few short exercises, and every unit ends with a practice set on fresh data. Projects and case studies give you a real brief and several steps to work through, and interview sets and a mock exam get you ready for tests. Your answer is checked by the rows it returns, so any correct query passes, and a wrong one gets a hint about what to fix. Visit plans and credits for current access.

Projects that use SQL

Work one company brief through several tools. Your SQL stage reads the tables from the stage before.

Project · WayfriendFrom invites to meetupsDatabricks → dbt → SQL → Dagster · 3 hr 30 minCase study · WayfriendThe reminder questionDatabricks → SQL · 45 minJourney · WayfriendYour first 90 days as a data engineerDatabricks → dbt → SQL → Dagster · 8 hrProject · Greenbasket Market · OutlineThe empty-shelf problemGoogle Sheets → SQL → Tableau · 2 hr
See all projects

Interview practice

Explain what you've learned in a full-screen interview. Speak or type, then review your answers against the rubric for your level.

Interview loops by job

Beginner

FreeNo account or card needed. Every lesson in this unit is yours to practice.

Query a table

Pick columns, filter rows, sort them and remove duplicates.

7 lessons
1SELECT: choose the columns you needPull a whole table or just the columns you need, calculate new columns, and name them with AS.4 exercises · 8 minFree
2WHERE: keep only the rows you wantFilter rows with a condition on numbers, text or dates.4 exercises · 8 minFree
3AND, OR, IN and BETWEEN: combine conditionsFilter on several conditions at once, match a list of values, and pick a range.4 exercises · 10 minFree
4LIKE: match text patternsFind text that starts with, ends with or contains something, using the % and _ wildcards.4 exercises · 8 minFree
5ORDER BY and LIMIT: sort and take the top rowsSort by one or more columns, largest or smallest first, and keep only the top rows.4 exercises · 10 minFree
6DISTINCT: remove duplicate rowsReturn each different value or combination once, and count how many different values there are.4 exercises · 8 minFree
Practice setQuery a tableTen mixed questions on new tables: staff, suppliers and support tickets.Free

Summarize data

Count, total and average, overall and for each group.

4 lessons
8COUNT, SUM, AVG, MIN and MAXBoil many rows down to one number: how many, the total, the average, the smallest and the largest.4 exercises · 10 minWith a plan
9GROUP BY: totals for each groupSplit rows into groups and get a count, total or average for each one.4 exercises · 10 minWith a plan
10HAVING: filter groupsKeep only the groups whose totals pass a test, such as customers with three or more orders.4 exercises · 10 minWith a plan
Practice setSummarize dataNine questions that count, total and average across support tickets, staff, suppliers and orders.With a plan

Combine tables

Join tables on their keys and handle the rows that do not match.

5 lessons
12INNER JOIN: combine matching rowsBring columns from two tables together by matching a key, such as each order with its customer's name.4 exercises · 12 minWith a plan
13LEFT JOIN: keep rows without a matchKeep every row from the first table, matched or not, and find the rows that have no match.4 exercises · 12 minWith a plan
14NULL, IS NULL and COALESCEFind missing values with IS NULL, count them correctly, and fill gaps with COALESCE.4 exercises · 10 minWith a plan
15Joining three or more tablesChain joins across several tables, and avoid counting the same row twice after a join.4 exercises · 12 minWith a plan
Practice setCombine tablesNine questions that join tickets, staff, suppliers and purchase orders, including the rows that have no match.With a plan

Create and change data

Create tables, then add, change and remove rows.

5 lessons
17CREATE TABLE and data typesCreate a table with the right column types, required columns and a primary key, or build one from a query.4 exercises · 12 minWith a plan
18INSERT: add rowsAdd one or many rows, leave columns out, put apostrophes in text, and copy rows from a query.4 exercises · 10 minWith a plan
19UPDATE: change rowsChange values in the rows you choose, set several columns at once, and calculate new values.4 exercises · 10 minWith a plan
20DELETE: remove rowsRemove the rows you choose, empty a table without dropping it, and avoid the traps around NULL.4 exercises · 10 minWith a plan
Practice setCreate and change dataEight small data jobs: build tables, add, fix and remove rows, checked by what the tables look like afterwards.With a plan

Beginner checkpoints

A first case study and the questions a basic SQL screen asks.

2 lessons
Case studyThe April numbersThe operations manager needs April in five numbers and a pick for the May newsletter.With a plan
Interview setBasicsEight questions of the kind asked in a first SQL screen: six to write and two to explain.With a plan

Intermediate

Functions and types

If-then logic, types and conversion, and functions for text, dates and numbers.

6 lessons
24CASE WHEN: if-then logicLabel rows by rules, sort by a custom order, and count by condition.4 exercises · 12 minWith a plan
25Data types and CASTConvert text to numbers and dates, survive values that won't convert, and avoid whole-number division.4 exercises · 12 minWith a plan
26String functionsClean and reshape text: trim, change case, join, split, take parts and replace characters.4 exercises · 14 minWith a plan
27Date functionsPull parts out of dates, round them to the month, measure the days between two dates and do date math.4 exercises · 14 minWith a plan
28Number functions and ROUNDRound money, round up to whole boxes, find remainders, and divide without dividing by zero.4 exercises · 12 minWith a plan
Practice setFunctions and typesEight cleaning and reporting tasks that mix text, date and number functions with CASE.With a plan

Nested queries

Queries inside queries, EXISTS, and named steps with WITH.

5 lessons
30Subqueries: a query inside a queryUse the result of one query inside another: compare with an average, filter with IN, or query a derived table.4 exercises · 12 minWith a plan
31Correlated subqueriesWrite a subquery that looks at the current row: each customer's last order, each order against the customer's own average.4 exercises · 12 minWith a plan
32EXISTS and NOT EXISTSKeep rows that have (or don't have) a match in another table, and avoid the NOT IN trap with NULLs.4 exercises · 12 minWith a plan
33CTEs: name a step with WITHBreak a complex query into named steps with WITH, so each step is easy to read and check.4 exercises · 12 minWith a plan
Practice setNested queriesEight questions that need a query inside a query, on staff, orders, tickets, suppliers and memberships.With a plan

More joins

Self joins, outer joins from both sides, every combination, and joins on ranges.

5 lessons
35Self joinsJoin a table to itself: employees with their managers, and pairs of rows that share something.4 exercises · 12 minWith a plan
36RIGHT and FULL OUTER JOINKeep unmatched rows from the right table, or from both, and reconcile two lists.4 exercises · 14 minWith a plan
37CROSS JOINPair every row with every other row, and use the result as a grid that shows zeros where nothing happened.4 exercises · 12 minWith a plan
38Joins on ranges and several conditionsJoin on BETWEEN, on date ranges with open ends, and on more than one column.4 exercises · 14 minWith a plan
Practice setMore joinsEight questions that need the less common joins, on staff, products, campaigns and stock.With a plan

Combine results

Stack and compare query results with UNION, INTERSECT and EXCEPT.

3 lessons
40UNION and UNION ALLStack the results of two queries, with or without removing duplicates.4 exercises · 12 minWith a plan
41INTERSECT and EXCEPTFind rows two queries have in common, or rows in one and not the other.4 exercises · 12 minWith a plan
Practice setCombine resultsEight questions that stack and compare lists of customers, products, emails and events.With a plan

Window functions

Rank, number, total and compare rows without collapsing them.

5 lessons
43Window functions: ROW_NUMBER, RANK and DENSE_RANKNumber and rank rows without collapsing them, across the whole table or within each group.4 exercises · 12 minWith a plan
44Top N per groupGet the best, latest or first row in each group by ranking inside a CTE and filtering outside it.4 exercises · 14 minWith a plan
45Running totals and LAGAdd up values as you go, restart them per group, and compare each row with the one before.4 exercises · 14 minWith a plan
46LAG and LEADLook at the previous or next row: days between orders, the next ticket, and day-to-day change.4 exercises · 12 minWith a plan
Practice setWindow functionsEight analyst questions that rank, accumulate and compare rows on orders, tickets, events and stock.With a plan

Intermediate projects

A report built step by step, a marketing question, and a medium interview set.

3 lessons
ProjectMonthly sales reportBuild the monthly report finance asked for, one column at a time, ending with a query they can re-run every month.With a plan
Case studyThe spring coupon's impactMarketing wants to know whether to run the SPRING20 coupon again. Compare coupon orders with the rest and make the call.With a plan
Interview setIntermediateEight medium questions: ranking within groups, running totals, duplicates, percentages and set operators.With a plan

Advanced

Database design

Keys and constraints, changing tables, views, indexes and normalization.

6 lessons
51Constraints, primary and foreign keysMake the database refuse bad data: required values, unique values, allowed ranges, defaults and links to other tables.4 exercises · 14 minWith a plan
52ALTER TABLEChange a table that already holds data: add, rename and drop columns, and change a column's type safely.4 exercises · 12 minWith a plan
53ViewsSave a query under a name, use it like a table, and see it stay current as the data changes.4 exercises · 12 minWith a plan
54IndexesCreate the indexes a query needs, enforce uniqueness, and know when an index doesn't help.4 exercises · 12 minWith a plan
55NormalizationSpot repeated data in a flat table and split it into tables where each fact is stored once.4 exercises · 14 minWith a plan
Practice setDatabase designEight design tasks for a new loyalty feature, checked by probing the tables you build.With a plan

Changing data safely

Transactions, upserts, changes driven by other tables, and code that runs inside the database.

5 lessons
57Transactions: BEGIN, COMMIT, ROLLBACKGroup changes so they all happen or none do, and undo a mistake before it's saved.4 exercises · 12 minWith a plan
58Upsert: INSERT ON CONFLICT and MERGEInsert new rows and update existing ones in one statement, the standard way to load a feed.4 exercises · 14 minWith a plan
59UPDATE and DELETE using other tablesChange or remove rows based on data in another table, without updating a row twice.4 exercises · 14 minWith a plan
60Stored procedures and triggersRead and fix code that runs inside the database, and know which databases support it.4 exercises · 14 minWith a plan
Practice setChanging data safelyEight data fixes from a support and operations backlog, checked by the tables they leave behind.With a plan

Advanced analytics

Moving windows, percentiles, pivots, recursion, date series and subtotals.

7 lessons
62Window frames and moving averagesChoose exactly which rows a window adds up: moving averages, partial windows and FIRST_VALUE and LAST_VALUE.4 exercises · 14 minWith a plan
63NTILE, median and percentilesFind the median and other percentiles, split rows into equal buckets, and flag outliers.4 exercises · 14 minWith a plan
64Pivoting rows into columnsTurn values into columns with conditional aggregation or PIVOT, and turn columns back into rows.4 exercises · 14 minWith a plan
65Recursive CTEsWalk a hierarchy of any depth and generate sequences with a CTE that refers to itself.4 exercises · 14 minWith a plan
66Date series and filling gapsGenerate every day or week in a range so days with no activity show up as zero.4 exercises · 12 minWith a plan
67Subtotals with ROLLUP and CUBEAdd subtotal and grand total rows to a grouped query, and tell them apart from real NULLs.4 exercises · 12 minWith a plan
Practice setAdvanced analyticsEight report questions that need the advanced tools, on tickets, stock, memberships and orders.With a plan

Analyst patterns

The recurring shapes of analyst work: dedup, streaks, sessions, funnels, cohorts and growth.

7 lessons
69DeduplicationFind duplicates that differ only by case or spaces, keep the newest copy, and remove the rest.4 exercises · 14 minWith a plan
70Gaps and islandsFind runs of consecutive days (streaks) and the holes between them.4 exercises · 16 minWith a plan
71SessionizationSplit a stream of website events into visits using a 30-minute inactivity rule.4 exercises · 16 minWith a plan
72FunnelsCount how many visitors reach each step from browsing to buying, and where they drop off.4 exercises · 14 minWith a plan
73Cohort retentionGroup customers by the month they first bought and measure how many come back in later months.4 exercises · 18 minWith a plan
74Month over month and year over yearCompare each period with the one before and the same period last year, with gaps and zero bases handled.4 exercises · 16 minWith a plan
Practice setAnalyst patternsEight analyst tasks on tickets, events, memberships and orders, each using one of the unit's patterns.With a plan

Advanced projects

Build a database from an export, clean a messy list, and answer two business questions.

5 lessons
ProjectFrom spreadsheet export to databaseTurn the flat export from the old order system into customers, products, orders and order lines, with keys, and prove nothing was lost.With a plan
ProjectClean the sign-up listTurn a messy newsletter export into a clean, deduplicated list marketing can send to, and report where sign-ups come from.With a plan
Case studyMembership churnMeasure churn in Kestrel Plus by plan, tenure and buying behavior, and name the group to target first.With a plan
Case studySupport response timesMeasure how fast support answers against its targets, find what is still waiting, and name the weakest channel.With a plan
Interview setAdvancedEight hard questions: ties in top N, streaks, retention, running totals that reset, and two reasoning questions.With a plan

Expert

Performance

Read query plans and write queries the database can run fast.

4 lessons
81Reading EXPLAIN plansAsk the database how it will run a query, and read the plan: scans, joins, grouping and sorting.4 exercises · 14 minWith a plan
82Index-friendly filtersWrite filters that let the database use an index: no functions on the column, no leading wildcard, no casts on the column side.4 exercises · 14 minWith a plan
83Rewriting slow queriesRewrite the shapes that make queries slow: per-row subqueries, joins before aggregation, and DISTINCT hiding duplicates.4 exercises · 16 minWith a plan
Practice setPerformanceEight tasks: read plans, and rewrite slow shapes into ones that return the same rows with less work.With a plan

Dialects

What changes between PostgreSQL, MySQL, SQL Server, BigQuery and Snowflake.

3 lessons
85PostgreSQL and MySQL differencesTranslate the MySQL habits that fail in PostgreSQL: backticks, LIMIT with a comma, GROUP_CONCAT and IFNULL.4 exercises · 14 minWith a plan
86SQL Server differencesTranslate SQL Server habits: TOP, ISNULL, IIF, DATEADD, square brackets and + for text.4 exercises · 14 minWith a plan
87BigQuery and Snowflake differencesUse the features cloud warehouses share with the database here: QUALIFY, TRY_CAST and SAFE_CAST, and arrays with UNNEST.4 exercises · 14 minWith a plan

Modeling for analytics

Star schemas, history tables and the checks that keep data trustworthy.

4 lessons
88Star schemas: facts and dimensionsModel data for reporting as a fact table of events surrounded by dimension tables that describe them.4 exercises · 16 minWith a plan
89Slowly changing dimensionsKeep history of changing attributes with valid-from and valid-to dates, query them as of a date, and record a change.4 exercises · 16 minWith a plan
90Data quality checksWrite the checks that catch bad data before a report does: duplicate keys, orphans, impossible values and missing fields.4 exercises · 14 minWith a plan
Practice setModeling for analyticsEight tasks that build reporting views, query history as of a date, and test the data behind them.With a plan

Expert projects and exams

End-to-end projects, two case studies, an expert interview set and a mock exam.

6 lessons
ProjectKPI pack for the leadership meetingBuild the first-half KPI pack the leadership team asked for, one number at a time, in a single query.With a plan
ProjectBuild a price history tableBuild the price history the finance team needs, apply July price changes the right way, and answer as-of questions from it.With a plan
Case studyCampaign attributionCredit orders to the campaigns that led to them, compare first-touch and last-touch views, and find the cheapest campaign per order.With a plan
Case studyWhat stockouts costMeasure how often and how long products ran out, estimate the sales lost, and name the product to fix first.With a plan
Interview setExpertEight expert questions: rolling distinct counts, sessions, as-of joins, deduplication with rules, median by hand and plan reasoning.With a plan
Mock examSQL certification practiceTwelve questions in the style of a cloud data warehouse SQL certification: six to answer by reasoning, six to write. Allow 30 minutes.With a plan

Your Work Gym account

Save your progress and pick up on another device. Your browser's progress comes with you.

We'll email you a link. It works once and expires in 15 minutes.

You can keep practicing without an account.