Get fluent in Spark SQL by writing it. Read a short lesson, then answer the kind of question a colleague actually asks — against a practice warehouse with orders, clickstream, FX rates and pipeline telemetry. The cheat sheet stays one click away the whole time, because remembering the exact syntax is not the point.
SELECT, WHERE, ORDER BY, LIMIT — and the order the engine actually runs them in.
A query names the columns you want (SELECT), the table they come from (FROM), and optionally which rows (WHERE), in what order (ORDER BY), and how many (LIMIT).
The clauses must appear in that written order, but the engine does not run them in that order. It reads FROM first, then filters with WHERE, then groups, then computes SELECT, then sorts, then limits. That single fact explains most beginner errors — including why an alias you create in SELECT can't be used in WHERE.
Get in the habit of aliasing tables (FROM orders o) and qualifying columns (o.status). It costs three keystrokes and saves you from ambiguous-column errors the moment you add a join.
SELECT <columns> FROM <table> <alias> WHERE <row filter> ORDER BY <columns> [ASC|DESC] LIMIT <n>
SELECT o.order_id,
o.user_id,
o.order_ts,
o.status,
o.order_amount
FROM orders o
WHERE o.currency = 'USD'
AND o.status = 'delivered'
ORDER BY o.order_amount DESC
LIMIT 20
WHERE takes a boolean expression. Combine conditions with AND / OR, and always parenthesise when you mix them — AND binds tighter than OR, so a AND b OR c means (a AND b) OR c, which is rarely what a stakeholder asked for.
Useful shorthands: IN ('a','b') for a set, BETWEEN x AND y for an inclusive range, LIKE '%checkout%' for substring matching, and IS NULL / IS NOT NULL for missing values.
One trap worth internalising early: NULL = NULL is not true, it is unknown. Any comparison against NULL with = or != yields unknown, and unknown rows are dropped by WHERE. Use IS NULL. This is the single most common cause of rows silently disappearing from a pipeline.
WHERE col IN ('a','b')
AND ts BETWEEN '2025-01-01' AND '2025-03-31'
AND (plan = 'pro' OR plan = 'enterprise')
AND user_id IS NOT NULL
SELECT e.event_id, e.user_id, e.event_type, e.page, e.device
FROM events e
WHERE e.event_type IN ('add_to_cart', 'checkout_start', 'purchase')
AND e.user_id IS NULL -- logged-out traffic that still converts
ORDER BY e.event_ts
LIMIT 25
Anything you can compute can go in the SELECT list: arithmetic, string functions, casts. Give every computed column an alias — an unnamed expression becomes a column literally called (qty * price), which downstream code can't reference.
Casting matters more than it looks. Integer division truncates in many engines, so rows_out / rows_in can come back as 0 when you meant 0.62. Cast one side to DOUBLE first.
Rounding is presentation, not truth. Round at the very end — the display layer — not in the middle of a pipeline, or the rounding error compounds through every downstream aggregate.
SELECT qty * unit_price AS line_total,
CAST(rows_out AS DOUBLE) / rows_in AS keep_rate,
ROUND(amount, 2) AS amount_rounded,
UPPER(country) AS country_code
SELECT oi.order_id,
oi.sku,
oi.quantity,
oi.unit_price,
ROUND(oi.quantity * oi.unit_price, 2) AS line_total
FROM order_items oi
ORDER BY line_total DESC
LIMIT 15
Loading…
QueryDojo has two halves. Learn is ten short modules that go from SELECT to building a
layered pipeline — each lesson is a few paragraphs, one syntax block, and one runnable example.
Practice is 34 exercises phrased the way requests actually arrive: someone asks a question in a
sentence, and you turn it into a query. Your answer is checked against a reference query by comparing the
actual result sets, so any correct approach passes.
Eight tables — users, products, orders, order items, clickstream events, FX rates, profile snapshots and pipeline run telemetry — covering January to June 2025. It is generated from a fixed seed, so everyone sees identical data and every exercise has one stable answer. It also carries the problems real data has: exact duplicate rows, orders pointing at users who don't exist, header totals that don't match their line items, and a currency feed with missing dates. Those aren't bugs; several exercises exist to find them.
Queries run server-side against SQLite with a Spark SQL function library layered on top, so
date_format, datediff, regexp_extract, collect_list,
percentile_approx and the rest behave the way you'd write them in Spark. Window functions,
CTEs, every join type and correlated subqueries all work as expected. It is not Spark, though — the guide
panel lists exactly what differs (no LATERAL VIEW/EXPLODE, no
INTERVAL literals, no QUALIFY, no struct or map columns). Everything is read-only:
INSERT, UPDATE and DROP are blocked, and each query runs against a
fresh copy of the data.
Solved exercises are remembered in your browser's local storage only. There's no account, no login, and nothing about your queries is stored on the server — it runs your SQL, returns the rows, and forgets it.
Most SQL tutorials teach syntax against a toy table of three customers, which is fine right up until the
first time a join silently doubles your revenue number. The gap that actually matters isn't knowing that
GROUP BY exists — it's knowing which of COUNT(*), COUNT(col) and
COUNT(DISTINCT col) the person asking meant, spotting that an INNER JOIN just
dropped six orders, and noticing that a day with zero rows disappeared from the chart instead of showing
zero.
So the exercises here are built around the mistakes rather than the keywords: fan-out, NULL propagation, non-deterministic tie-breaking, missing join keys, reconciliation between two sources that should agree. The data has real defects in it on purpose, and several tasks are simply "find them". The later modules move from single queries to layered transforms — raw, clean, enriched, mart — because that's the shape production work takes once more than one person depends on the output.
It's aimed at people who understand what a database is and roughly what SQL does, but can't recall the exact incantation for "top three per category" or "latest row per key" without looking it up. That's a normal place to be. The fix isn't memorising a reference — it's writing enough queries that the shapes become familiar, with the reference sitting open beside you until it isn't needed.