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

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