One task · six dialects

The same SQL, in every dialect.

Running totals, top-N per group, dedup with ROW_NUMBER, pivot/unpivot, upsert, JSON and recursive CTEs — each shown in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, with the gotchas that bite between them.

19 recipes

• Window functions Compare a row to the previous one (LAG / LEAD) Day-over-day change and previous/next row values with LAG and LEAD, the same way in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 2 min →
• Window functions Moving average (rolling window) in SQL A rolling N-row average with AVG() OVER and a ROWS frame — the same syntax in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, plus calendar windows with RANGE. 2 min →
• Window functions Percent of total (and percent of group) Each row's share of the grand total or of its group with SUM() OVER () — plus the integer-division trap — in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 2 min →
• Window functions ROW_NUMBER vs RANK vs DENSE_RANK The three ranking window functions side by side — how they treat ties, with the same syntax in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 2 min →
• Window functions Running total (cumulative sum) in SQL A row-by-row running total with SUM() OVER, in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB — including the ROWS-vs-RANGE frame trap. 3 min →
• Window functions Top-N rows per group in SQL Get the top N rows per group (e.g. top 3 orders per customer) with ROW_NUMBER — QUALIFY in BigQuery/Snowflake/DuckDB, a subquery in MySQL/PostgreSQL/SQLite, DISTINCT ON in Postgres. 3 min →
• Grouping & aggregation Conditional count and sum (COUNT/SUM with a condition) Count or sum only the rows that match a condition in one pass — CASE, the FILTER clause, COUNTIF and COUNT_IF across MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 2 min →
• Grouping & aggregation Concatenate rows into one string per group Aggregate values into a comma-separated list — STRING_AGG, GROUP_CONCAT and LISTAGG across MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, with the ordering and length traps. 3 min →
• Grouping & aggregation Median and percentiles in SQL Compute the median (and any percentile) with PERCENTILE_CONT where it exists, and a window-function workaround for MySQL and SQLite, which have no built-in. 3 min →
• Grouping & aggregation Pivot rows into columns Turn row values into columns (e.g. quarters across the top) with portable conditional aggregation, plus the PIVOT operator in BigQuery, Snowflake and DuckDB. 3 min →
• Grouping & aggregation Unpivot columns into rows Turn wide columns (q1, q2, q3, q4) into rows with portable UNION ALL or a LATERAL VALUES join, plus the UNPIVOT operator in BigQuery, Snowflake and DuckDB. 3 min →
• Insert, update & upsert Delete duplicate rows, keeping one Remove duplicate rows while keeping a single copy per key — ROW_NUMBER, MIN(rowid)/ctid, and CREATE OR REPLACE across PostgreSQL, MySQL, SQLite, BigQuery, Snowflake and DuckDB. 3 min →
• Insert, update & upsert UPDATE a table from a join Update rows using values from another table — UPDATE ... FROM, UPDATE ... JOIN, and the correlated-subquery form across PostgreSQL, DuckDB, MySQL, SQLite, BigQuery and Snowflake. 3 min →
• Insert, update & upsert Upsert (insert or update) in SQL Insert a row or update it if the key already exists — ON CONFLICT, ON DUPLICATE KEY UPDATE and MERGE across PostgreSQL, SQLite, DuckDB, MySQL, BigQuery and Snowflake. 3 min →
• JSON Expand a JSON array into rows Turn a JSON array column into one row per element — jsonb_array_elements, JSON_TABLE, json_each, UNNEST and FLATTEN across PostgreSQL, MySQL, SQLite, BigQuery, Snowflake and DuckDB. 3 min →
• JSON Read a field from a JSON column Extract a scalar (and a nested path) from a JSON/JSONB column — ->/->>, JSON_EXTRACT, JSON_VALUE and the colon syntax across PostgreSQL, MySQL, SQLite, BigQuery, Snowflake and DuckDB. 3 min →
• Strings Split a delimited string into rows Turn 'a,b,c' into three rows — string_to_array/unnest, SPLIT, SPLIT_TO_TABLE, plus the JSON_TABLE and recursive-CTE workarounds for MySQL and SQLite. 3 min →
• CTEs & recursion Generate a sequence of numbers Produce rows 1..N (for filling gaps, building calendars, or test data) with generate_series, GENERATE_ARRAY, GENERATOR, and a portable recursive CTE. 2 min →
• CTEs & recursion Walk a hierarchy with a recursive CTE Traverse a parent/child tree (org chart, category tree) with WITH RECURSIVE — the same pattern in MySQL, PostgreSQL, SQLite, DuckDB, BigQuery and Snowflake. 3 min →

You know the query. You don't remember which dialect spells it which way.

Each recipe puts the task on one page with the exact syntax per dialect, side by side, plus the traps (argument order, version requirements, NULL handling).

FAQ