One task · six dialects · copy the one you need

The same SQL, in every dialect.

Running totals, top-N per group, removing duplicates with ROW_NUMBER, pivot and unpivot, upsert, reading JSON, recursive CTEs — each shown in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, with the gotchas that bite when you move 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 →

Why this exists

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

Every engine does the common things differently — upsert is ON CONFLICT in Postgres, ON DUPLICATE KEY UPDATE in MySQL, and MERGE in BigQuery and Snowflake; filtering a window function needs QUALIFY in some and a subquery in others. So you end up googling the same five patterns over and over. sqlindex.pages.dev puts each task on one page with the exact syntax per dialect, side by side, plus the traps (argument order, version requirements, NULL handling).

How it works

Find the task, copy your dialect's version

Pick a recipe. Browse by topic in the full recipe list.
Copy the block for your database (one-click copy on every snippet).
Swap in your table and column names — the examples use plain placeholders.

FAQ

Frequently asked questions

Are these SQL recipes free?

Yes. Every recipe on sqlindex.pages.dev is free to read and copy, with no account, paywall, or sign-up. Some outbound links (for example to managed databases, data warehouses, or courses) may be affiliate links, which never change the price you pay.

Which databases are covered?

Each recipe shows the syntax for MySQL (8+), PostgreSQL, SQLite (3.25+), BigQuery, Snowflake, and DuckDB. Where a feature needs a specific version (window functions, QUALIFY, JSON_TABLE) the recipe says so.

Why does the same query differ between databases?

The SQL standard leaves a lot implementation-defined, and each engine grew its own functions — so aggregating strings is STRING_AGG in Postgres/BigQuery/DuckDB, GROUP_CONCAT in MySQL/SQLite, and LISTAGG in Snowflake. These recipes hand you the right one for your engine instead of making you translate.

What's QUALIFY, and why isn't it in every example?

QUALIFY filters the result of a window function inline (e.g. keep ROW_NUMBER() = 1), and it exists in BigQuery, Snowflake, and DuckDB. MySQL, PostgreSQL, and SQLite do not have it, so those versions wrap the window function in a subquery or CTE and filter outside. The recipes show both forms.

Is this the same as datesql?

They are companions with no overlap. datesql.pages.dev covers date and time across dialects (DATEDIFF, DATE_TRUNC, formatting, time zones). sqlindex covers everything else: window functions, grouping and pivots, upsert/MERGE, JSON, strings, and recursive CTEs.

Will the snippets run as-is?

They are minimal, working statements written with placeholder table and column names (orders, amount, customer_id…) that you swap for yours. Window functions need MySQL 8+, SQLite 3.25+, or any modern warehouse — version-sensitive parts are flagged in each recipe.