DuckDB SQL Reference

Quick reference for DuckDB SQL syntax used in the app's SQL editor.

Basics

-- Select specific columns
SELECT name, email, revenue FROM data

-- Filter rows
SELECT * FROM data WHERE revenue > 1000

-- Sort results
SELECT * FROM data ORDER BY revenue DESC

-- Limit results
SELECT * FROM data LIMIT 100

Aggregations

-- Group and aggregate
SELECT category, COUNT(*) AS count, AVG(revenue) AS avg_rev
FROM data GROUP BY category

-- Having (filter groups)
SELECT category, SUM(revenue) AS total
FROM data GROUP BY category HAVING total > 10000

Window Functions

-- Rank within groups
SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank
FROM data

-- Running total
SELECT *, SUM(revenue) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total
FROM data

String Functions

UPPER(col)           -- Uppercase
LOWER(col)           -- Lowercase
TRIM(col)            -- Remove whitespace
REPLACE(col, 'a', 'b')  -- Find and replace
SUBSTRING(col, 1, 5)    -- Extract characters
LENGTH(col)          -- String length
REGEXP_EXTRACT(col, 'pattern')  -- Regex extract

Date Functions

EXTRACT(year FROM date_col)      -- Get year
DATEDIFF('day', date1, date2)    -- Days between dates
date_col + INTERVAL '30 day'     -- Add 30 days
CURRENT_DATE                     -- Today's date
strftime(date_col, '%Y-%m')      -- Format date

Useful DuckDB Extensions

-- EXCLUDE columns
SELECT * EXCLUDE (temp_col) FROM data

-- DISTINCT ON (first row per group)
SELECT DISTINCT ON (email) * FROM data

-- PIVOT
PIVOT data ON category USING SUM(revenue) GROUP BY region

-- List aggregation
SELECT category, LIST(name) AS names FROM data GROUP BY category

What ExploreMyData will and will not run

Both places you can write SQL on this site, the SQL step inside the app and the workbench on /sql-in-browser, are read-only by construction. The check happens in the page, before anything reaches DuckDB, so a refused statement never touches the engine at all.

Allowed

A query has to begin with one of these words. Only one statement runs at a time, so a stray semicolon cannot smuggle a second one in behind it, and comments are stripped before the check so a keyword inside -- notes does not trip it.

SELECT      -- ordinary queries, in every shape DuckDB supports
WITH        -- common table expressions, including RECURSIVE
FROM        -- DuckDB's FROM-first shorthand: FROM t SELECT count(*)
DESCRIBE    -- column names and inferred types
SUMMARIZE   -- per-column min, max, quartiles, nulls, distinct counts
EXPLAIN     -- the query plan
VALUES      -- an inline table
PIVOT / UNPIVOT
TABLE       -- TABLE t, shorthand for SELECT * FROM t

Read-only table functions, on files you loaded

These stay available and are the way to override how a file was sniffed: a delimiter the detector got wrong, a column you would rather read as text, a header row that is not on line one. They only ever read the virtual files your own upload registered.

read_csv_auto('sales.csv')
read_csv_auto('sales.csv', delim=';', header=true, sample_size=-1)
read_parquet('events.parquet')
read_json_auto('records.json')

Refused

Anything that writes, reaches the file system or the network, or changes the engine itself. The last four are the ones that matter most for a page holding your local files: they are how a DuckDB session would read or write something you did not hand it.

INSERT  UPDATE  DELETE  TRUNCATE
DROP    ALTER   CREATE (as a statement)
COPY    EXPORT  IMPORT
ATTACH  DETACH  INSTALL  LOAD

Note that REPLACE is deliberately absent from that list: REPLACE(str, from, to) is an everyday string function, and a REPLACE statement cannot occur anyway once a query must start with one of the allowed words.

Limits

  • In-page workbench: 100 MB per file, results capped at 2,000 rows on screen while downloads carry every row the query produced.
  • The app at /app: files up to 1 GB, streamed rather than held in memory all at once, and the SQL step composes with the other 39 pipeline operations.

For the full DuckDB SQL reference, visit duckdb.org/docs.