A · SQL Pocket Reference

This page collects the SQL that the book uses, one pattern at a time, with the differences you will meet between databases at work. Look up what you need and leave. Every query that reads Steep’s tables has been run on Steep’s data, and all of them but one also run in the console at the end. The Hive statement under “Writing tables” is shown, not run. The SQL is written for DuckDB, the database inside the book; the dialect table translates it.

The tables on this page

All examples use the small copies of Steep’s tables that the console loads. Chapter 1 explains order statuses, and the chapter in the last column explains each table.

Table One row is Rows Chapter
orders one order (31 August to 13 September, every status) 91,818 3
order_items one order line: one drink and its quantity 131,510 3
menu_items one drink on the menu 12 3
monday_orders one order placed on Monday 14 September 5,711 7
ods_events one delivered app event that arrived on 14 September (re-delivered copies included) 51,927 8
dws_city_platform_day one day, city and platform: completed orders 1,764 12
dwd_user_zipper one version of a member’s loyalty tier (a sample of members) 38,677 12

The shape of a query

You write the clauses in one order. The database works through them in another (Chapter 3). The comments show the second order, numbering only the clauses this query uses.

SELECT   city, count(*) AS orders   -- 5. pick and name the columns
FROM     orders                     -- 1. take the table
WHERE    status = 'completed'       -- 2. keep the rows that pass
GROUP BY city                       -- 3. one group per city
HAVING   count(*) > 15000           -- 4. keep the groups that pass
ORDER BY orders DESC                -- 6. sort the result
LIMIT    2;                         -- 7. keep the first two rows

Returns: the two largest of the three cities with more than 15,000 completed orders in the two weeks.

The full order is:

  1. FROM and every JOIN: build the rows.
  2. WHERE: keep the rows that pass.
  3. GROUP BY: make the groups and compute the aggregates.
  4. HAVING: keep the groups that pass.
  5. SELECT: compute and name the columns. Window functions are computed here.
  6. QUALIFY: keep the rows whose window function passes (not every database has it).
  7. DISTINCT: remove repeated rows.
  8. ORDER BY: sort.
  9. LIMIT and OFFSET: cut.

Three rules follow from this order. WHERE cannot use an aggregate such as count(*), so filter groups with HAVING. WHERE cannot use a window function, so filter in an outer query or with QUALIFY. And in standard SQL, a name made with AS in SELECT is not yet known in WHERE. Some databases allow it anyway; do not count on it.

Filters and NULL

You write It keeps a row when
a = b, a <> b, a < b, a >= b the comparison is true
a BETWEEN x AND y x <= a AND a <= y: both ends are included
a IN ('ios', 'android') a equals one of the values
a LIKE 'A10%' the text matches; % is any text, _ is one character
a IS NULL, a IS NOT NULL the value is missing, or present
NOT, AND, OR the combined test is true; AND runs before OR, so use brackets

NULL means “no value here”. Aggregates skip it, but count(*) counts every row:

SELECT event_name,
       count(*)        AS events,
       count(order_id) AS with_order_id
FROM ods_events
GROUP BY event_name
ORDER BY events DESC;

Returns: five rows, one per event name. Only order_completed events carry an order_id. For the other four, the column is NULL, so count(order_id) is 0.

Three-valued logic in one line: a comparison with NULL is neither true nor false but unknown, and WHERE keeps only rows whose test is true.

SELECT count(*)                                 AS all_events,
       count(*) FILTER (WHERE order_id <> 0)    AS order_id_not_0,
       count(*) FILTER (WHERE order_id IS NULL) AS order_id_null
FROM ods_events;

Returns: one row. The second and third numbers add up to the first. For a NULL row, order_id <> 0 is unknown, so a NULL row fails both order_id = 0 and order_id <> 0; only IS NULL catches it.

  • coalesce(a, b, ...) returns the first value that is not NULL: coalesce(sum(qty), 0) shows 0 instead of NULL.
  • NOT IN with a NULL in the list is never true. This query returns 0, because at least one event has no order_id. Use NOT EXISTS (below) instead.
SELECT count(*) AS orders
FROM monday_orders
WHERE order_id NOT IN (SELECT order_id FROM ods_events);
  • On a timestamp, BETWEEN '2026-09-07' AND '2026-09-13' stops at midnight at the start of the last day. Write >= DATE '2026-09-07' AND < DATE '2026-09-14' (Chapter 3).

CASE: if, then, otherwise

CASE tests its WHEN lines from the top, and the first true one wins. Without ELSE, a row that matches nothing gets NULL.

SELECT CASE WHEN items_count = 1  THEN '1 drink'
            WHEN items_count <= 3 THEN '2 to 3 drinks'
            WHEN items_count < 20 THEN '4 to 19 drinks'
            ELSE '20 or more'
       END                         AS size,
       count(*)                    AS orders,
       round(avg(net_amount), 2)   AS avg_net_amount
FROM orders
WHERE status = 'completed'
GROUP BY size
ORDER BY min(items_count);

Returns: four rows, one per order size, smallest first. Most orders hold one drink. ORDER BY min(items_count) sorts the labels by size instead of alphabetically.

CASE also works inside an aggregate: count(CASE WHEN platform = 'ios' THEN 1 END) counts iOS rows in every database. The standard FILTER clause (DuckDB, Spark SQL) is shorter: count(*) FILTER (WHERE platform = 'ios').

Joins

Join Keeps Where there is no partner
JOIN (INNER JOIN) only rows that found a partner the row is dropped
LEFT JOIN every row of the left table the right table’s columns are NULL
FULL OUTER JOIN every row of both tables the other side’s columns are NULL
CROSS JOIN every pair of rows (no condition)
semi join left rows that have a partner, each once (written with EXISTS)
anti join left rows that have no partner (written with NOT EXISTS or LEFT JOIN … IS NULL)

A full outer join shows both kinds of gap at once. Here, Monday’s orders meet Monday’s order_completed events (Chapter 7):

WITH db AS (            -- every order placed on Monday
  SELECT order_id FROM monday_orders
),
ev AS (                 -- order_completed events in Monday's file
  SELECT DISTINCT order_id
  FROM ods_events
  WHERE event_name = 'order_completed'
)
SELECT CASE WHEN ev.order_id IS NULL THEN 'database only'
            WHEN db.order_id IS NULL THEN 'events only'
            ELSE 'both' END AS found_in,
       count(*) AS orders
FROM db
FULL OUTER JOIN ev ON ev.order_id = db.order_id
GROUP BY found_in
ORDER BY orders DESC;

Returns: three rows. both: orders with an event. database only: orders with no event in Monday’s file. Most of them, 818 of 843 with A1024 among them, are iOS 3.2.0 Apple Pay orders whose event was never sent (Chapter 15); for four of them, the event arrived after midnight, in the next day’s file (Chapter 7). events only: an event that arrived on Monday for an order placed the day before.

Semi join. Keep the orders that have a partner, each one once. EXISTS asks “is there at least one?”:

SELECT count(*) AS orders_with_event
FROM monday_orders AS o
WHERE EXISTS (
  SELECT 1
  FROM ods_events AS e
  WHERE e.order_id = o.order_id
    AND e.event_name = 'order_completed'
);

Returns: 4,868 orders, the same as both above. A plain JOIN with the same condition returns 4,884 rows, because a few events were delivered twice (Chapter 8), and each copy pairs with the order again.

Anti join. Keep the orders that have no partner. This is the reconciliation of Chapter 7, written in two portable ways. Both return the same 843 orders, Mia’s A1024 among them.

SELECT o.order_id, o.pickup_code, o.store_id, o.created_at_local
FROM monday_orders AS o
LEFT JOIN (
  SELECT DISTINCT order_id
  FROM ods_events
  WHERE event_name = 'order_completed'
) AS e ON e.order_id = o.order_id
-- no partner: no event
WHERE e.order_id IS NULL
ORDER BY o.created_at_local;
SELECT o.order_id, o.pickup_code, o.store_id, o.created_at_local
FROM monday_orders AS o
WHERE NOT EXISTS (
  SELECT 1
  FROM ods_events AS e
  WHERE e.order_id = o.order_id
    AND e.event_name = 'order_completed'
)
ORDER BY o.created_at_local;

DuckDB (0.8 and later) and Spark SQL also have SEMI JOIN and ANTI JOIN keywords; Hive has LEFT SEMI JOIN, and LEFT ANTI JOIN from 4.0 (see the dialect table). The forms above work everywhere, including the book’s console.

The fan-out trap. A join between two grains copies rows (Chapter 3). Check every join that you add up after:

SELECT count(*)                   AS joined_rows,
       count(DISTINCT o.order_id) AS orders
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed';

Returns: one row. The two numbers differ, so the join copied each order once per line. Add up order amounts before the join (in a CTE), never after it.

Aggregates and percentiles

SELECT city,
       count(*)                        AS orders,
       count(DISTINCT user_id)         AS customers,
       round(avg(net_amount), 2)       AS mean,
       median(net_amount)              AS median,
       quantile_cont(net_amount, 0.9)  AS p90,
       max(net_amount)                 AS largest
FROM orders
WHERE status = 'completed'
GROUP BY city
ORDER BY orders DESC;

Returns: one row per city. In every city, the mean is above the median: a few very large orders pull the mean up (Chapter 2).

  • count(*) counts rows; count(x) counts values that are not NULL; count(DISTINCT x) counts different values.
  • median(x) is quantile_cont(x, 0.5). quantile_cont interpolates: it estimates a value between two neighbouring values; quantile_disc returns a value that is really in the data.
  • Percentile functions have different names in every database (see the dialect table).
  • Distinct counts and averages do not add up across groups: you cannot sum daily distinct customers into weekly ones, or average the city averages (Chapter 12).

CTEs: one step at a time

A CTE (WITH name AS (...)) names a small query, so the next step can use it like a table. Write one step per CTE, each at one grain:

WITH daily AS (         -- step 1: one row per day and city
  SELECT order_date, city, count(*) AS orders
  FROM orders
  WHERE status = 'completed'
  GROUP BY order_date, city
),
weekly AS (             -- step 2: one row per city
  SELECT city,
    sum(orders) FILTER (WHERE order_date <= DATE '2026-09-06')
      AS week_before,
    sum(orders) FILTER (WHERE order_date >= DATE '2026-09-07')
      AS last_week
  FROM daily
  GROUP BY city
)
SELECT city, week_before, last_week,
       round(100.0 * (last_week - week_before) / week_before, 1)
         AS change_pct
FROM weekly
ORDER BY change_pct;

Returns: one row per city with its week-over-week change in completed orders. Every city fell; the two largest cities fell the most (Chapter 16 finds out why).

100.0 * keeps the division in decimals. In some databases, and in the old DuckDB inside the book’s console, a whole number divided by a whole number stays whole: 7 / 2 is 3.

Window functions

A window function computes a value for each row from related rows, and keeps every row (Chapter 3). The pattern is function() OVER (PARTITION BY ... ORDER BY ... frame).

Function Gives each row
row_number() 1, 2, 3, … : ties get different numbers, in no fixed order
rank() ties share a number, then it skips: 1, 1, 3
dense_rank() ties share a number, no skipping: 1, 1, 2
lag(x), lead(x) x from the row before, or after
sum(x) OVER (ORDER BY ...) a running total
avg(x) OVER (... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) a 7-row moving average
first_value(x), last_value(x) the first or last x in the frame

The three ranking functions, on the twelve drinks of the menu. Some drinks cost the same:

SELECT name, base_price,
       row_number() OVER (ORDER BY base_price DESC) AS row_num,
       rank()       OVER (ORDER BY base_price DESC) AS rnk,
       dense_rank() OVER (ORDER BY base_price DESC) AS dense_rnk
FROM menu_items
ORDER BY base_price DESC, row_num;

Returns: twelve rows. Where two prices tie, row_number() still gives two numbers, rank() repeats one and then skips, and dense_rank() repeats one and does not skip. Add a tie-breaker, such as ORDER BY base_price DESC, name, when you need the same answer on every run.

Looking back, a moving average, and a total that restarts every week, on Steep’s daily orders:

WITH daily AS (
  SELECT order_date, sum(orders) AS orders
  FROM dws_city_platform_day
  GROUP BY order_date
),
moving AS (             -- the windows see every day ...
  SELECT order_date, orders,
         orders - lag(orders) OVER (ORDER BY order_date)
           AS vs_day_before,
         avg(orders) OVER (
           ORDER BY order_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
         ) AS avg_7_days,
         sum(orders) OVER (
           PARTITION BY date_trunc('week', order_date)
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS week_so_far
  FROM daily
)
SELECT order_date, orders, vs_day_before,  -- ... then we filter
       round(avg_7_days) AS avg_7_days, week_so_far
FROM moving
WHERE order_date BETWEEN DATE '2026-08-31' AND DATE '2026-09-13'
ORDER BY order_date;

Returns: fourteen rows, 31 August to 13 September. vs_day_before is filled even on the first day, because the window saw the days before it. That is why the filter sits in the outer query: a WHERE next to the window would run first, and the first six days would average fewer than seven days.

  • Write ROWS BETWEEN ... for running totals. Without it, the default frame is RANGE, which gives tied rows the same total (Chapter 3).
  • QUALIFY filters on a window function in the same query. Where it is missing, wrap the query and filter outside (see top N per group).

Dates and time zones

Steep stores two clocks for every order (Chapter 7): created_at in UTC and created_at_local in Steep’s local time, UTC−7. order_date is the local day: group by it, not by the UTC day.

SELECT order_id,
       created_at,          -- UTC
       created_at_local,    -- Steep time, UTC-7
       order_date,          -- the local day
       date_trunc('week', order_date) AS week_start,
       dayname(order_date)            AS weekday,
       date_diff('day', order_date, DATE '2026-09-14')
         AS days_to_monday
FROM orders
WHERE pickup_code = 'A1024'
ORDER BY order_id
LIMIT 3;

Returns: three orders with the receipt label A1024 (a label repeats every day; it is not a key). The week starts on a Monday, and date_diff('day', start, end) counts from start to end.

Converting UTC to local time. Steep’s offset never changes in this data, so both forms below give created_at_local on every row. With a real time zone that has summer time, use the named form:

SELECT count(*) AS orders,
       count(*) FILTER (
         WHERE created_at - INTERVAL 7 HOUR = created_at_local
       ) AS shifted_matches,
       count(*) FILTER (
         WHERE created_at_local =
               (created_at AT TIME ZONE 'UTC')
               AT TIME ZONE 'America/Los_Angeles'
       ) AS named_zone_matches
FROM orders;

Returns: three equal numbers. In DuckDB, named time zones come from the ICU extension, an add-on that knows the names and rules of the world’s time zones; the usual DuckDB downloads load it by themselves. This is the one query on the page that the book’s console cannot run: its old DuckDB has no named time zones.

SELECT isodow(order_date)  AS day_no,   -- 1 = Monday
       dayname(order_date) AS weekday,
       count(*)            AS orders
FROM orders
WHERE status = 'completed'
GROUP BY day_no, weekday
ORDER BY day_no;

Returns: seven rows, Monday first. Fridays and Saturdays are the busiest days; Mondays are the quietest. Day numbers differ between databases, so sort by an ISO number (1 = Monday) when you can.

Date arithmetic is the least portable part of SQL. Above all, datediff takes its two dates in a different order in different databases (see the dialect table).

Deduplication

First, measure: compare the rows with the different keys.

SELECT count(*)                            AS rows_in_table,
       count(DISTINCT event_id)            AS different_events,
       count(*) - count(DISTINCT event_id) AS extra_copies
FROM ods_events;

Returns: one row. Some events arrived twice: Kafka delivers at least once (Chapter 8).

SELECT DISTINCT * does not help here. It removes only rows that are equal in every column, and a copy arrives with a later offset. On this table, it removes nothing:

SELECT count(*) AS rows_left
FROM (SELECT DISTINCT * FROM ods_events) AS d;

Keep one row per key instead, and say which one wins. This is how Steep’s dwd_event_detail is built (Chapter 12):

SELECT count(*) AS events_kept
FROM (
  SELECT *
  FROM ods_events
  QUALIFY row_number() OVER (
            PARTITION BY event_id     -- one group per event
            ORDER BY "offset"         -- the first delivery wins
          ) = 1
) AS deduplicated;
SELECT count(*) AS events_kept
FROM (
  SELECT event_id,
         row_number() OVER (
           PARTITION BY event_id ORDER BY "offset"
         ) AS rn
  FROM ods_events
) AS numbered
WHERE rn = 1;

Returns: the number of different events, 51,779. "offset" is in double quotes because OFFSET is a reserved word in DuckDB.

Common patterns

Appendix E has more patterns, written as interview answers: the second-highest value, sessions from events, pivot and unpivot and an ordered funnel.

Days in a row (连续登录)

The classic “gaps and islands” trick. Number each customer’s days in order. Within a run of days in a row, the date minus its number is the same day, so that difference names the run. In Hive and Spark SQL, write the subtraction as date_sub(order_date, rn).

WITH days AS (          -- one row per customer and order day
  SELECT DISTINCT user_id, order_date
  FROM orders
  WHERE status = 'completed'
),
islands AS (            -- days in a row share one "island" date
  SELECT user_id, order_date,
         order_date - CAST(row_number() OVER (
           PARTITION BY user_id ORDER BY order_date
         ) AS INTEGER) AS island
  FROM days
)
SELECT user_id,
       min(order_date) AS first_day,
       max(order_date) AS last_day,
       count(*)        AS days_in_a_row
FROM islands
GROUP BY user_id, island
HAVING count(*) >= 3
ORDER BY days_in_a_row DESC, user_id;

Returns: one row per run of three or more days in a row with a completed order. The longest runs last seven days. For “logged in on N days in a row”, use app events instead of orders (see the interview answer in Appendix E).

Retention: who came back

WITH week_before AS (
  SELECT DISTINCT user_id
  FROM orders
  WHERE status = 'completed'
    AND order_date BETWEEN DATE '2026-08-31' AND DATE '2026-09-06'
),
last_week AS (
  SELECT DISTINCT user_id
  FROM orders
  WHERE status = 'completed'
    AND order_date BETWEEN DATE '2026-09-07' AND DATE '2026-09-13'
)
SELECT count(*)            AS customers,
       count(l.user_id)    AS came_back,
       round(100.0 * count(l.user_id) / count(*), 1)
         AS retention_pct
FROM week_before AS b
LEFT JOIN last_week AS l ON l.user_id = b.user_id;

Returns: one row: customers of the week before, how many ordered again last week, and the share. Count each customer once on each side (DISTINCT), or the left join fans out. For retention by signup cohort, see Chapter 6. For day-1 and day-7 retention, see the model answer in Appendix E. There, the test for “active on day 7” is a.day = c.day0 + 7 in DuckDB; in Hive and Spark SQL, write a.day = date_add(c.day0, 7).

Funnel: how far each visit got

SELECT platform,
       count(DISTINCT session_id)
         FILTER (WHERE event_name = 'app_open')        AS opened,
       count(DISTINCT session_id)
         FILTER (WHERE event_name = 'view_menu')       AS menu,
       count(DISTINCT session_id)
         FILTER (WHERE event_name = 'add_to_cart')     AS cart,
       count(DISTINCT session_id)
         FILTER (WHERE event_name = 'checkout_start')  AS checkout,
       count(DISTINCT session_id)
         FILTER (WHERE event_name = 'order_completed') AS paid
FROM ods_events
GROUP BY platform
ORDER BY platform;

Returns: one row per platform: visits (sessions) that reached each step on 14 September. On iOS, far fewer visits reach order_completed after checkout_start than on the other platforms: the missing event of the case (Chapter 6). Counting distinct sessions makes each step count a visit once, even when an event arrived twice. This query counts each step on its own; a strict funnel also requires the earlier steps. Here, 12 sessions have order_completed but no checkout_start. For an ordered funnel, see Appendix E.

Top N per group

WITH sold AS (
  SELECT o.city, m.name, sum(i.qty) AS drinks
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  JOIN menu_items AS m ON m.menu_item_id = i.menu_item_id
  WHERE o.status = 'completed'
  GROUP BY o.city, m.name
)
SELECT city, name, drinks,
       rank() OVER (PARTITION BY city ORDER BY drinks DESC) AS rnk
FROM sold
QUALIFY rnk <= 3
ORDER BY city, rnk;
SELECT city, name, drinks, rnk
FROM (
  SELECT o.city, m.name, sum(i.qty) AS drinks,
         rank() OVER (PARTITION BY o.city
                      ORDER BY sum(i.qty) DESC) AS rnk
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  JOIN menu_items AS m ON m.menu_item_id = i.menu_item_id
  WHERE o.status = 'completed'
  GROUP BY o.city, m.name
) AS ranked
WHERE rnk <= 3
ORDER BY city, rnk;

Returns: the three best-selling drinks in each city. With rank(), two drinks with the same total would share a place, and a city could return four rows. Use row_number() with a tie-breaker for exactly three.

Latest row per key

SELECT user_id, order_id, created_at_local, status
FROM orders
QUALIFY row_number() OVER (
          PARTITION BY user_id
          ORDER BY created_at_local DESC, order_id DESC
        ) = 1
ORDER BY user_id;

Returns: one row per customer, 45,626 rows: each customer’s most recent order in the two weeks. When you need only one column of that row, arg_max(order_id, created_at_local) with GROUP BY user_id gives the same answer in DuckDB when no two of a customer’s orders share a time (Spark SQL has max_by).

A zipper table “as of” a date

A zipper table (拉链表) keeps one row per version, with the first and last day it was true (Chapter 12). The row whose dates contain a day is the version of that day:

SELECT loyalty_tier, count(*) AS members
FROM dwd_user_zipper
WHERE DATE '2026-09-10' BETWEEN start_date AND end_date
GROUP BY loyalty_tier
ORDER BY members DESC;

Returns: members per tier on 10 September. Join facts to the version that was true on their own day:

SELECT z.loyalty_tier,
       count(*)                    AS orders,
       round(avg(o.net_amount), 2) AS avg_net_amount
FROM orders AS o
JOIN dwd_user_zipper AS z
  ON z.user_id = o.user_id
 AND o.order_date BETWEEN z.start_date AND z.end_date
WHERE o.status = 'completed'
GROUP BY z.loyalty_tier
ORDER BY orders DESC;

Returns: completed orders by the tier the customer had on the day of the order. Each member has exactly one version per day, so this join does not fan out: the orders add up to all of these members’ orders.

Writing tables

Re-running a load must not create a second copy (Chapter 13). So a nightly job replaces one whole day, or merges rows by a key.

Replace one day. Hive and Spark SQL do it in one statement:

-- Hive and Spark SQL: replace one partition in one statement.
INSERT OVERWRITE TABLE dws_city_day PARTITION (dt = '2026-09-13')
SELECT city, count(*) AS orders
FROM dwd_order_detail
WHERE dt = '2026-09-13' AND status = 'completed'
GROUP BY city;

DuckDB and MySQL have no INSERT OVERWRITE. Delete the day and insert it again, inside one transaction, so that no reader sees the gap:

CREATE OR REPLACE TABLE daily_city_orders (
  order_date DATE,
  city       VARCHAR,
  orders     BIGINT,
  PRIMARY KEY (order_date, city)
);
BEGIN TRANSACTION;
DELETE FROM daily_city_orders
WHERE order_date = DATE '2026-09-13';
INSERT INTO daily_city_orders
SELECT order_date, city, count(*) AS orders
FROM orders
WHERE status = 'completed' AND order_date = DATE '2026-09-13'
GROUP BY order_date, city;
COMMIT;

We ran this three times; the table still held one row per city for that day.

Upsert: update or insert by key.

MERGE INTO daily_city_orders AS t
USING (
  SELECT order_date, city, count(*) AS orders
  FROM orders
  WHERE status = 'completed' AND order_date >= DATE '2026-09-11'
  GROUP BY order_date, city
) AS s
ON t.order_date = s.order_date AND t.city = s.city
WHEN MATCHED THEN UPDATE SET orders = s.orders
WHEN NOT MATCHED THEN INSERT (order_date, city, orders)
                      VALUES (s.order_date, s.city, s.orders);
INSERT INTO daily_city_orders
SELECT order_date, city, count(*) AS orders
FROM orders
WHERE status = 'completed' AND order_date >= DATE '2026-09-12'
GROUP BY order_date, city
ON CONFLICT (order_date, city)
DO UPDATE SET orders = excluded.orders;

MERGE runs in DuckDB 1.4 and later, in Hive (on transactional tables, and on Iceberg tables in Hive 4) and in Spark (on table formats that support it, such as Delta Lake, Iceberg, Hudi and Paimon). MySQL writes the same idea as INSERT ... ON DUPLICATE KEY UPDATE. A merge is only safe if the key is truly unique. Give the source one row per key first, as Chapter 11 does (“the newest change per order”). Otherwise some engines stop with an error, and others, such as DuckDB, keep one row without telling you.

The same task in four dialects

The same everyday task, written for four engines. The cells were checked on 4 October 2026 against the official documentation of DuckDB 1.5 (release 1.5.6), Apache Hive 4.2.1 (the LanguageManual), Apache Spark SQL 4.2.0 (the SQL reference) and MySQL 8.4 (the Reference Manual, 8.4.11). A version in brackets, such as “(3.4+)”, is the first release that has the feature; check the version your company runs. † means that Hive’s manual is silent and the cell comes from Hive’s source code (release 4.2.1).

Some things are the same in all four: CASE WHEN, coalesce, if(condition, a, b), count(DISTINCT x), CTEs (WITH), and the window functions row_number, rank, dense_rank, lag and lead.

Dates and times

Task DuckDB 1.5 Hive 4.2 Spark SQL 4.2 MySQL 8.4
Add 7 days to a date d + 7 (a DATE; d + INTERVAL 7 DAY gives a TIMESTAMP) date_add(d, 7) date_add(d, 7) DATE_ADD(d, INTERVAL 7 DAY)
Days from start to end (watch the order) date_diff('day', start, end), or end - start datediff(end, start) datediff(end, start); but datediff(DAY, start, end) (3.3+) takes start first DATEDIFF(end, start)
First day of the month date_trunc('month', d) (a TIMESTAMP) trunc(d, 'MM') (a STRING) trunc(d, 'MM') (a DATE) DATE_FORMAT(d, '%Y-%m-01') (a string; there is no DATE_TRUNC)
A date as text, 2026-09 strftime(d, '%Y-%m') date_format(d, 'yyyy-MM') date_format(d, 'yyyy-MM') DATE_FORMAT(d, '%Y-%m')
Text 20260914 to a date strptime(s, '%Y%m%d')::DATE to_date(from_unixtime(unix_timestamp(s, 'yyyyMMdd'))) to_date(s, 'yyyyMMdd') STR_TO_DATE(s, '%Y%m%d')
Day of the week isodow(d): 1 = Monday; dayofweek(d): 0 = Sunday extract(dayofweek FROM d): 1 = Sunday dayofweek(d): 1 = Sunday; weekday(d): 0 = Monday DAYOFWEEK(d): 1 = Sunday; WEEKDAY(d): 0 = Monday
UTC to local time (ts AT TIME ZONE 'UTC') AT TIME ZONE 'America/Los_Angeles' from_utc_timestamp(ts, 'America/Los_Angeles') from_utc_timestamp(ts, 'America/Los_Angeles') CONVERT_TZ(ts, '+00:00', 'America/Los_Angeles') (needs the time zone tables)
Epoch milliseconds (a Kafka timestamp) to a time make_timestamp_ms(ms), or epoch_ms(ms) (the one the console knows) from_unixtime(ms DIV 1000) (a STRING in the local time zone) timestamp_millis(ms) FROM_UNIXTIME(ms / 1000) (in the session time zone)
One row per day between two dates generate_series(d1, d2, INTERVAL 1 DAY) (TIMESTAMPs: cast to DATE) no such function: posexplode(split(space(datediff(d2, d1)), ' ')) gives the positions 0 to n, where n is the number of days between the dates; date_add(d1, pos) turns each position into a date explode(sequence(d1, d2, INTERVAL 1 DAY)) WITH RECURSIVE (stops after 1,000 steps by default)

Text, NULL and numbers

Task DuckDB 1.5 Hive 4.2 Spark SQL 4.2 MySQL 8.4
Join two texts a || b (NULL if either is NULL); concat(a, b) skips NULLs concat(a, b) (NULL if any is NULL †); a || b (2.2+) concat(a, b) or a || b (NULL if any is NULL) CONCAT(a, b) (NULL if any is NULL); || means OR
First value that is not NULL coalesce(a, b), ifnull(a, b); no nvl coalesce(a, b), nvl(a, b) coalesce, nvl, ifnull COALESCE, IFNULL; no NVL
Whole number divided by whole number 7 / 2 is 3.5; 7 // 2 is 3 (before 0.8, 7 / 2 was 3) 7 / 2 is 3.5; 7 DIV 2 is 3 7 / 2 is 3.5; 7 div 2 is 3 7 / 2 is 3.5000; 7 DIV 2 is 3
Count rows that pass a test count(*) FILTER (WHERE c), count_if(c) sum(CASE WHEN c THEN 1 ELSE 0 END) count(*) FILTER (WHERE c), count_if(c) SUM(c), or COUNT(CASE WHEN c THEN 1 END)
Median and 90th percentile median(x), quantile_cont(x, 0.9) percentile(x, 0.5) (whole numbers only), percentile_approx(x, 0.9), percentile_cont(x, 0.9) (4.0+ †) median(x) (3.4+), percentile(x, 0.9), percentile_approx(x, 0.9) none: rank the rows with ROW_NUMBER() or CUME_DIST()
Values into one list list(x) (also array_agg) collect_list(x); collect_set(x) drops repeats collect_list(x), collect_set(x), array_agg(x) (3.3+) JSON_ARRAYAGG(x)
Values into one text string_agg(x, ',') concat_ws(',', collect_list(x)) string_agg(x, ',') (4.0+), or concat_ws(',', collect_list(x)) GROUP_CONCAT(x SEPARATOR ',') (cut at group_concat_max_len, 1,024 by default)
One row per list element unnest(xs) LATERAL VIEW explode(xs) t AS x LATERAL VIEW explode(xs) t AS x, or explode(xs) in SELECT JSON_TABLE(...)

concat_ws(separator, a, b, ...) skips NULLs in all four engines (in Hive †).

Queries and joins

Task DuckDB 1.5 Hive 4.2 Spark SQL 4.2 MySQL 8.4
Filter on a window function QUALIFY QUALIFY (4.0+ †); before: a subquery QUALIFY (4.2+); before: a subquery a subquery or a CTE
Semi join WHERE EXISTS (...); SEMI JOIN (0.8+) LEFT SEMI JOIN; EXISTS [LEFT] SEMI JOIN; EXISTS WHERE EXISTS (...) or IN (...)
Anti join WHERE NOT EXISTS (...); ANTI JOIN (0.8+) LEFT JOIN ... WHERE b.k IS NULL; NOT EXISTS; LEFT ANTI JOIN (4.0+ †) [LEFT] ANTI JOIN; NOT EXISTS NOT EXISTS (...), or LEFT JOIN ... IS NULL
GROUP BY a name made in SELECT allowed not for an expression: repeat it allowed allowed
Skip 20 rows, return 10 LIMIT 10 OFFSET 20 LIMIT 20, 10 (offset first); LIMIT 10 OFFSET 20 also parses † LIMIT 10 OFFSET 20 (3.4+) LIMIT 10 OFFSET 20, or LIMIT 20, 10
Quote a name that is a keyword "order" `order` `order` `order` (or "order" with ANSI_QUOTES)

Writing tables

Task DuckDB 1.5 Hive 4.2 Spark SQL 4.2 MySQL 8.4
Replace one day of a table DELETE + INSERT in one transaction INSERT OVERWRITE TABLE t PARTITION (dt='...') SELECT ... the same as Hive for one named day. Without a value (a dynamic partition), the default static mode deletes all partitions first; set spark.sql.sources.partitionOverwriteMode=dynamic DELETE + INSERT in one transaction
Update or insert by key (upsert) INSERT ... ON CONFLICT (k) DO UPDATE ...; MERGE INTO (1.4+) MERGE INTO (2.2+, on transactional tables; on Iceberg tables in 4.x) MERGE INTO on table formats that support it (Delta Lake, Iceberg, Hudi, Paimon); not in the Spark SQL reference INSERT ... ON DUPLICATE KEY UPDATE ...

Two more differences often cause mistakes. Spark 4 runs in ANSI mode by default, so bad input, such as a date that does not exist, raises an error instead of giving NULL (try_to_date gives NULL). And MySQL’s || is a deprecated way of writing OR, not a way of joining texts.

Try it

Steep’s tables, in your browser. The console loads the seven tables listed at the top of this page. Choosing a starting query runs it. After you edit it, or paste any query from this page, press “Run query” or Ctrl+Enter (the button stays grey until you change something).

The console runs one statement at a time, on an older DuckDB, from before version 0.8 (inside DuckDB-WASM 1.24). So three kinds of SQL on this page do not run there: blocks of several statements (such as BEGIN … COMMIT), MERGE, and named time zones. Also, there 7 / 2 is 3, and the SEMI JOIN and ANTI JOIN keywords are missing. Every query on this page that only reads runs there, except the one with a named time zone.

Things to try:

  • In “Top 3 drinks per city”, change rank() to row_number() and <= 3 to = 1.
  • In “Anti-join”, add AND o.store_id = 'HBR-01' as a new line under the WHERE line. Which pickup codes are left?
  • In “Days in a row”, change >= 3 to >= 5, then count the runs with an outer SELECT count(*).
  • Type SELECT 7 / 2, 7 / 2.0; to see the console’s old integer division.