SQL Data Workbench: Code Examples

DuckDB Window Functions

A window function lets one row see its neighbors before the result collapses into a group. DuckDB supports the full SQL window function vocabulary: ranking, running aggregates, lag/lead offsets, and percentile functions. Use the SQL Workbench to run these against Parquet, CSV, JSON, or Excel files directly in your browser.¹

How the OVER clause controls partitions and frames

DuckDB window functions compute a result for each row based on a set of related rows defined by the OVER clause. The PARTITION BY clause divides the result set into independent groups before the function runs; ORDER BY inside OVER sorts rows within each partition. Without ORDER BY, RANK and ROW_NUMBER produce non-deterministic results, and LAG and LEAD have no defined offset direction.²

Choosing a deterministic frame

The ROWS BETWEEN clause specifies exactly which rows within the partition the function includes. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows from the start of the partition up to and including the current row, which is the standard running total frame. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes the current row and the 6 rows before it, producing a 7-row sliding window for moving averages.³

RANGE is the alternative to ROWS and bounds the frame by value rather than by row count, so it groups together all rows whose ORDER BY value equals the current row within the given distance. For a date-ordered window, RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW includes every row within seven days of the current date, which is more robust than a fixed row count when daily rows are missing. DuckDB computes all window functions in a single pass, so adding several frames in one query does not multiply the cost.

For ranking: choosing ROW_NUMBER, RANK, and DENSE_RANK

For ranking within a partition, DuckDB offers three functions that differ in how they handle tied values. ROW_NUMBER assigns a unique sequential integer starting at 1, even to rows with identical values in the ORDER BY column. RANK assigns the same rank to tied rows but leaves a gap: two rows tied at rank 2 both receive rank 2, and the next row receives rank 4. DENSE_RANK eliminates the gap: the same two tied rows receive rank 2, and the next row receives rank 3.⁴

Top N per group with QUALIFY

For selecting the top N rows per group, combine ROW_NUMBER with QUALIFY: SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn FROM sales QUALIFY rn <= 3. QUALIFY is a DuckDB-specific clause that filters on window function results without wrapping the query in a CTE or subquery.⁵ It is handy for exploratory work in the workbench because you can test a ranking rule and filter it in the same statement.

Period-over-period comparisons with LAG and LEAD

When you need period-over-period comparisons, LAG retrieves a value from a row that precedes the current row within the window. LAG(revenue, 1) OVER (ORDER BY month) returns the revenue from the previous row in month order. LEAD does the reverse: LEAD(revenue, 1) OVER (ORDER BY month) returns the revenue from the next row. Both functions return NULL at the boundary where no preceding or following row exists; supply a default with a third argument: LAG(revenue, 1, 0).⁴

Building a month-over-month growth column

Alias the LAG result in a CTE to avoid computing it twice in the same expression: WITH prev AS (SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue FROM monthly_revenue) SELECT month, (revenue - prev_revenue) / NULLIF(prev_revenue, 0) AS mom_growth FROM prev. Wrapping prev_revenue in NULLIF avoids division by zero for months that follow a zero-revenue period. This is useful when reviewing time series, ranking sales reps, or checking whether an export changed between daily snapshots without exporting the data again.

Notes

Window functions use the OVER clause. PARTITION BY divides rows into groups before the function runs, like GROUP BY but without collapsing rows. ORDER BY within OVER determines the sort order for running aggregates, LAG, LEAD, and ranking functions. Without ORDER BY, ROW_NUMBER and RANK produce non-deterministic results. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the default frame for running totals. RANGE uses value-based bounds instead of row-based bounds. QUALIFY filters window function results without a subquery: SELECT *, ROW_NUMBER() OVER (...) AS rn FROM t QUALIFY rn = 1 keeps only the first row per partition. Window functions run after WHERE and GROUP BY but before SELECT column aliases, so you cannot reference a window alias in the same SELECT clause. DuckDB computes all window functions in one pass, making multiple window functions in the same query efficient.

Examples

SELECT
  region,
  sales_rep,
  revenue,
  RANK() OVER (
    PARTITION BY region
    ORDER BY revenue DESC
  ) AS rank_in_region
FROM sales_data;

SELECT
  order_date,
  region,
  amount,
  SUM(amount) OVER (
    PARTITION BY region
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change
FROM monthly_revenue;

SELECT *
FROM sales_data
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY region
  ORDER BY revenue DESC
) <= 3;

SELECT
  event_date,
  daily_users,
  AVG(daily_users) OVER (
    ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_metrics;

SELECT
  region,
  sales_rep,
  revenue,
  RANK() OVER (
    PARTITION BY region
    ORDER BY revenue DESC
  ) AS rank_in_region
FROM sales_data;

DuckDB PIVOT / UNPIVOT

Need a crosstab without spreadsheet pivots? PIVOT rotates rows into columns, turning a long-format table into a wide table. UNPIVOT does the reverse, melting wide column headers into rows. DuckDB implements both as first-class SQL statements¹, so you can reshape CSV, Parquet, and Excel data directly in the workbench without Python or a spreadsheet pivot table.

Understanding the ON clause and dynamic column names

Inside a PIVOT statement, the ON clause determines which column's distinct values become new column headers. For a sales table where the month column holds Jan, Feb, and Mar, PIVOT sales ON month USING SUM(revenue) GROUP BY product scans all distinct month values at query time and generates one output column per value. This dynamic behavior is useful when you do not know the possible values in advance, yet it means the output schema shifts whenever new values appear in the source data; a new month value produces a new column in the PIVOT result without warning.¹

Locking pivot columns with IN

For a stable, predictable output schema, restrict the pivot values with IN: ON month IN ('Jan', 'Feb', 'Mar'). DuckDB then generates exactly those three columns regardless of what the underlying data contains. Missing values produce NULL columns in the output rather than errors. Use IN whenever your PIVOT output feeds a Parquet export, an automated report, or any downstream step that depends on a fixed column list.

Locking the column list also makes the result repeatable across runs, so a scheduled query that pivots a fresh export each morning always returns the same columns in the same order. When a month in the IN list has no matching rows, that column arrives as NULL rather than dropping out, which keeps joins and report templates aligned even on sparse data.

Choosing between PIVOT and conditional aggregation

Choosing PIVOT over CASE WHEN conditional aggregation depends on whether you know the output column names in advance. CASE WHEN requires every pivot value to be written explicitly in the SELECT statement: SELECT product, SUM(CASE WHEN month = 'Jan' THEN revenue END) AS jan_revenue, SUM(CASE WHEN month = 'Feb' THEN revenue END) AS feb_revenue FROM sales GROUP BY product. This produces a schema that downstream tools can depend on, because every output column is explicitly named in the SELECT statement, and the column list remains stable even when new values appear in the source data later. PIVOT generates column names automatically from data values, making it concise for exploratory work yet less reliable in production pipelines where schema predictability matters, because a new value in the source data can silently add a column to the output.²

For most exploratory tasks in the workbench, PIVOT is the right default because you can write a concise query without listing every output column. When the output feeds a downstream file or an automated report, the IN clause locks the column list and prevents unexpected schema changes when data arrives with new values.

Using UNPIVOT to restore long format for analysis

When your data arrives as a wide crosstab with one column per time period or category, analysis is easier in long format where all values share one column. UNPIVOT converts that wide layout directly: UNPIVOT quarterly_revenue ON (q1, q2, q3, q4) INTO NAME quarter VALUE revenue. The result has one row per original row per listed column, with the original column name in the quarter column and the numeric value in revenue.³

Combining UNPIVOT with window functions

Long-format tables unlock window functions that wide tables resist. After you UNPIVOT, a running total across quarters requires only a straightforward window expression: SUM(revenue) OVER (PARTITION BY product ORDER BY quarter ROWS UNBOUNDED PRECEDING). The same calculation on a wide table requires a verbose CASE WHEN expression for each quarter separately. Restructure to long format first whenever your analysis requires per-period ordering, rolling aggregates, or rank comparisons within groups; the window function syntax stays consistent regardless of how many periods your data contains.

Exporting reshaped data

After PIVOT or UNPIVOT, use the Export button to write the reshaped result to CSV, JSON, Excel, or Parquet. CapyToolkit keeps the SQL work local, so you can validate the layout before handing the file to another tool. Parquet export is the best choice when the downstream consumer is an analytical engine, because it preserves column types and compresses the output, whereas CSV serializes every value as text and forces re-inference on the next read.

Notes

PIVOT syntax: PIVOT table_name ON pivot_col USING agg_fn(value_col) GROUP BY group_col. The ON clause lists the column whose distinct values become new column headers. USING specifies the aggregation (SUM, COUNT, AVG, MAX). The resulting column names are derived from the distinct values of the ON column at query time, so the output schema is dynamic. For a static list of pivot values, use IN: ON month IN ('Jan', 'Feb', 'Mar'). UNPIVOT syntax: UNPIVOT table_name ON (col1, col2, col3) INTO NAME metric VALUE amount. The ON clause lists the columns to melt; NAME and VALUE name the resulting key and value columns. DuckDB PIVOT is a statement, not a function: it cannot be nested directly inside a CTE without a workaround. Both statements are available only in DuckDB sessions, not SQLite sessions. Column names from PIVOT are auto-quoted if they contain special characters or spaces.

Examples

PIVOT sales
ON month
USING SUM(revenue)
GROUP BY product;

PIVOT sales
ON month IN ('Jan', 'Feb', 'Mar', 'Apr')
USING SUM(revenue)
GROUP BY region;

UNPIVOT quarterly_revenue
ON (q1, q2, q3, q4)
INTO
  NAME quarter
  VALUE revenue;

PIVOT orders
ON status
USING COUNT(*)
GROUP BY region;

PIVOT sales
ON month
USING SUM(revenue)
GROUP BY product;

DuckDB JSON Queries

A JSON blob inside a table column is useful until you need one field in a report. DuckDB can query JSON values stored as column data, not just JSON files. If your Parquet or CSV file has a column that contains JSON strings, DuckDB's JSON logical type lets you cast those strings into JSON and then extract fields, check types, and navigate nested structures directly in SQL, with no upload.¹

Extracting fields from VARCHAR JSON columns

When DuckDB reads a Parquet or CSV file, columns that contain JSON strings arrive as VARCHAR rather than as structured JSON values. To extract values from VARCHAR JSON, cast the string to JSON or use the json_extract family of functions. json_extract_string(metadata, '$.source') returns the source field as a plain string without surrounding quotes. json_extract(metadata, '$.count')::INTEGER returns a numeric field cast to INTEGER. The -> operator is shorthand for json_extract and ->> is shorthand for json_extract_string, but those operators require a JSON logical value, so cast VARCHAR JSON first: (metadata::JSON)->>'$.source' produces the same result as the longer form.²

Cast before using operators

Dot notation (col.field) works when DuckDB has a JSON logical column or a parsed STRUCT column. On VARCHAR JSON columns from Parquet or CSV files, dot notation raises an error because the value is still text. Run DESCRIBE on the loaded table first to confirm which columns are JSON, STRUCT, or VARCHAR before writing extraction expressions. Getting this wrong produces confusing runtime errors that are hard to debug, so the DESCRIBE-first habit saves time on every subsequent query you write against that table.

Navigating deep nesting and handling missing paths

For JSON columns with nested objects two or three levels deep, JSONPath syntax navigates the entire hierarchy in a single expression. json_extract_string(payload, '$.user.address.city') descends three levels without intermediate extraction steps. DuckDB supports JSONPath and JSON Pointer notation, and JSON array indexing is zero-based.³ When a path does not exist in a particular record, the function returns NULL rather than raising an error, so your queries remain stable even with inconsistent schemas.

Discovering unknown JSON keys

For unknown structures, json_keys(col) returns the top-level key names as a list. Combine it with UNNEST to see all distinct keys across a dataset: SELECT DISTINCT UNNEST(json_keys(metadata)) AS key FROM events. This reveals the field landscape before you commit to specific extraction expressions, which is especially valuable when the JSON schema varies across records. In CapyToolkit, that discovery step is useful because you can inspect a messy export locally before deciding which fields matter.

Flattening JSON arrays embedded in VARCHAR columns

When a VARCHAR JSON column contains arrays rather than objects, UNNEST requires a cast step before it can expand the elements. json_extract(col, '$.tags')::VARCHAR[] converts the JSON array at the tags path into a DuckDB list column. Passing that to UNNEST produces one row per element: SELECT id, UNNEST(json_extract(tags_col, '$')::VARCHAR[]) AS tag FROM events.

Handling mixed-type JSON arrays safely

For arrays where elements vary between strings and numbers across rows, cast to VARCHAR[] first and handle type conversion inside the column expression. DuckDB raises an error if you attempt to cast a mixed-type JSON array to INTEGER[] when string elements are present. Using VARCHAR[] accepts all elements without error; you can then apply TRY_CAST(element AS DOUBLE) in an outer query to convert safely. Wrap the entire UNNEST in a CTE to keep the query readable when you need both the cast and a filter in one expression.

For nested objects inside arrays, unnest first, then extract fields from each element. A pattern like SELECT (item).id, (item).status FROM (SELECT UNNEST(json_extract(payload, '$.items')::STRUCT(id INTEGER, status VARCHAR)[]) AS item FROM events) keeps each array element as a struct. This avoids losing the relationship between the parent row and each child item.

Notes

json_extract(col, '$.key') returns a JSON value (preserving type). json_extract_string(col, '$.key') returns a plain string. The -> operator is shorthand for json_extract and ->> is shorthand for json_extract_string. Use :: to cast extracted values: json_extract(col, '$.price')::DOUBLE. json_keys(col) returns the top-level keys as a list. json_type(col, '$.key') returns the JSON type string ('string', 'number', 'object', 'array', 'null'). json_array_length(col, '$.items') counts elements in an array at a path. For dot notation access on structured JSON columns (not string blobs), DuckDB auto-parses the column as a STRUCT; this applies when DuckDB reads a JSON file, not when it reads a VARCHAR column containing JSON. On VARCHAR JSON columns, cast to JSON before using -> or ->>. UNNEST works on json_extract results that return arrays: SELECT UNNEST(json_extract(col, '$.tags')::VARCHAR[]) AS tag FROM t.

Examples

SELECT
  id,
  json_extract_string(metadata, '$.source') AS source,
  json_extract(metadata, '$.priority')::INTEGER AS priority
FROM events;

SELECT
  payload -> '$.user' AS user_obj,
  payload ->> '$.user.email' AS email
FROM requests;

SELECT COUNT(*)
FROM events
WHERE json_extract_string(properties, '$.plan') = 'pro';

SELECT
  id,
  UNNEST(json_extract(tags, '$')::VARCHAR[]) AS tag
FROM articles;

SELECT
  id,
  json_type(data, '$.value') AS value_type,
  CASE json_type(data, '$.value')
    WHEN 'number' THEN json_extract(data, '$.value')::DOUBLE
    ELSE NULL
  END AS numeric_value
FROM records;

SELECT
  id,
  json_extract_string(metadata, '$.source') AS source,
  json_extract(metadata, '$.priority')::INTEGER AS priority
FROM events;

DuckDB UNNEST

Flattening arrays is the moment a nested export becomes tabular. UNNEST turns several values in one list cell into separate rows, so one product with three tags becomes three product-tag rows.¹ DuckDB's UNNEST works in the SELECT clause, handles multiple arrays side by side, and supports recursive unnesting for nested structures. Use it on Parquet, JSON, CSV, or Excel files loaded in the workbench.

Unnesting list columns from Parquet and JSON files

Parquet files produced by pandas, Polars, or Spark often contain LIST-typed columns: a product table might have a tags column of type LIST<VARCHAR>, or an orders table might have a line_items column of type LIST<STRUCT>. DuckDB LIST columns can contain values with different lengths, but every element must share the same underlying type.² Drop the Parquet file, run DESCRIBE to confirm the column type, and apply UNNEST in the SELECT clause: SELECT id, UNNEST(tags) AS tag FROM products. The result has one row per tag per product, with the id column repeated for each element.

Unnesting direct LIST columns

For JSON files loaded by DuckDB, array fields within each record become LIST columns automatically. Verify the column type with DESCRIBE before unnesting: a LIST column unnests directly, but a VARCHAR column that contains JSON arrays requires the explicit cast covered in the JSON queries guide.³ When the source file is Parquet, the LIST type is preserved natively, so no cast step is needed and UNNEST operates directly on the typed column without any intermediate conversion.

After the unnest, every other column in the SELECT repeats for each element, so a table with ten million rows and a tags list of average length three explodes to roughly thirty million output rows. Keep the unnested query scoped to the columns you actually need and consider a COUNT or GROUP BY on the element when you only want statistics rather than the full expansion. This keeps memory use predictable during exploratory work in the browser.

Preserving element position with WITH ORDINALITY

Because UNNEST does not preserve the original array order by default, pair it with generate_subscripts to attach a 1-based position index to each unnested element: SELECT id, UNNEST(items) AS item, generate_subscripts(items, 1) AS pos FROM orders.⁴ The pos column holds the element's position in the original list, starting at 1. This lets you reconstruct the original order with ORDER BY id, pos after filtering the unnested rows.

Keeping array order after filtering

Ordinality is particularly useful when element position carries meaning. For arrays where the first element is the primary value and subsequent elements are alternatives, capturing the index before filtering preserves your ability to identify the original position after you apply a WHERE clause on the unnested values. This is also useful for ranking or time-series arrays where the order is part of the meaning, not just formatting. During exploratory analysis, that index also helps you spot shifted data. A single missing value, dropped element, or reordered array becomes visible when pos no longer matches expectations. Keep the index before you filter, and you can audit the original sequence without reopening the source file.

Unnesting struct arrays and zipping multiple list columns

Unnesting a LIST<STRUCT> column produces one row per struct element, with each struct's fields accessible via dot notation on the unnested alias. A line_items column of type LIST<STRUCT(product_id VARCHAR, quantity INTEGER, price DOUBLE)> unnests to rows where you access fields as (UNNEST(line_items)).product_id and (UNNEST(line_items)).quantity. Wrap the parentheses carefully when accessing struct subfields directly in the SELECT clause.²

Parallel UNNEST and zip behavior

To unnest two list columns simultaneously, place both UNNEST calls in the same SELECT clause: SELECT id, UNNEST(names) AS name, UNNEST(scores) AS score FROM data.¹ DuckDB unnests the arrays side by side and pads the shorter list with NULL values. A row with 3 names and 5 scores produces 3 output rows with NULL for the last 2 score values. When the two arrays are guaranteed to have equal length, parallel UNNEST is the correct pattern for unnesting related arrays in a single pass.

Notes

UNNEST(col) in the SELECT clause flattens a LIST column into one row per element. When a table has other non-list columns, they are repeated for each element. UNNEST multiple columns simultaneously by listing them in parallel UNNEST calls; DuckDB pads the shorter list with NULL values. To attach the element index, use generate_subscripts: SELECT UNNEST(tags) AS tag, generate_subscripts(tags, 1) AS idx FROM articles. For JSON array columns (VARCHAR containing JSON), cast to an array type first: json_extract(col, '$')::VARCHAR[]. Struct arrays (LIST of STRUCT) unnest with each struct field accessible via dot notation on the unnested alias. UNNEST in a WHERE clause is not valid. Unnest first in a subquery or CTE, then filter. max depth: UNNEST flattens only one level at a time; nested arrays need multiple UNNEST calls.

Examples

SELECT
  id,
  UNNEST(tags) AS tag
FROM articles;

SELECT
  id,
  UNNEST(items) AS item,
  generate_subscripts(items, 1) AS pos
FROM orders;

SELECT
  id,
  UNNEST(json_extract(properties, '$.tags')::VARCHAR[]) AS tag
FROM events;

Count list length without unnesting:
SELECT id, len(tags) AS tag_count FROM articles;

Verify after unnest, one row per element:
SELECT id, COUNT(*) AS elements
FROM (SELECT id, UNNEST(tags) AS tag FROM articles)
GROUP BY id;

SELECT
  order_id,
  UNNEST(line_items) AS item
FROM orders;

Access struct fields on the unnested alias:
SELECT
  order_id,
  (UNNEST(line_items)).product_id,
  (UNNEST(line_items)).quantity
FROM orders;

SELECT
  id,
  UNNEST(tags) AS tag
FROM articles;

DuckDB Date & Time

A useful date column has to line up on type, format, and calendar unit before your analysis can trust it. DuckDB has a rich date and time library: parse many format strings, truncate to a calendar unit, extract parts, do arithmetic with INTERVAL, and convert to and from Unix epoch.¹ All of these work on Parquet, CSV, JSON, and Excel files loaded in the workbench.

Parsing date strings loaded from CSV and JSON

When DuckDB loads a CSV or JSON file with date columns stored as text, those columns may arrive as VARCHAR rather than as DATE or TIMESTAMP types.²³ You must parse or cast them explicitly before applying date arithmetic or truncation. The fastest option for ISO 8601 strings like 2026-01-15 or 2026-01-15T09:30:00Z is the cast operator: created_at::DATE or created_at::TIMESTAMP.

Parsing non-ISO date strings

For non-ISO formats, strptime handles explicit patterns: strptime(date_col, '%d/%m/%Y') parses European-style dates with day first.⁴ Run DESCRIBE on the loaded table before writing any date expressions; confirm which columns DuckDB inferred as DATE or TIMESTAMP and which arrived as VARCHAR. strptime format codes follow the C strftime convention: %Y is a 4-digit year, %m is the zero-padded month, %d is the day, %H is the hour in 24-hour format, %M is the minute, and %S is the second.

Truncating and extracting for grouping

For time-series aggregation, date_trunc rounds a timestamp down to the start of a calendar unit.⁵ date_trunc('week', event_ts) returns the Monday of the week containing event_ts. date_trunc('month', ts) returns the first day of the month. Group by the truncated value to produce per-week or per-month summaries: SELECT date_trunc('month', order_date) AS month, SUM(revenue) AS monthly_revenue FROM orders GROUP BY 1 ORDER BY 1. Valid units include second, minute, hour, day, week, month, quarter, and year.

Extracting components for distributions

For extracting a single numeric component, EXTRACT and date_part are equivalent: EXTRACT(HOUR FROM event_ts) and date_part('hour', event_ts) both return the hour component. Use EXTRACT for ISO SQL compatibility; use date_part for readability. Pair EXTRACT with GROUP BY to build hour-of-day or day-of-week distributions across an entire dataset. These distributions are useful for spotting peak activity windows, identifying off-hours anomalies, and scheduling batch jobs during low-traffic periods.

INTERVAL arithmetic and Unix epoch conversion

DuckDB supports INTERVAL arithmetic for DATE and TIMESTAMP expressions.⁶ Add days, months, or years to a date with order_date + INTERVAL '30 days' or subtract a period with ts - INTERVAL '3 months'. INTERVAL values accept years, hours, minutes, and seconds as units. For rolling time windows, combine INTERVAL with NOW() or CURRENT_TIMESTAMP: WHERE event_ts >= NOW() - INTERVAL '7 days' filters to the trailing 7 days relative to the moment the query runs.⁵

Converting Unix epoch milliseconds to a timestamp

JavaScript event data and many logging systems store timestamps as Unix epoch milliseconds, which is the number of milliseconds since January 1, 1970 UTC and is a common format in web APIs, log aggregators, and time-series databases that need a compact numeric timestamp representation instead of a formatted date string. DuckDB's make_timestamp_ms converts milliseconds since the epoch directly to a timestamp. The reverse conversion from TIMESTAMP to epoch milliseconds uses epoch_ms(ts). For epoch microseconds, use epoch_us(ts) or make_timestamp(microseconds).¹ Log streams that emit JSON events with a ts field in milliseconds and IoT sensors that report epoch microseconds are two real-world sources where picking the wrong helper silently produces wrong results. Always verify the magnitude of your epoch column with SELECT MAX(epoch_col) FROM table before choosing the function: values around 1.7 trillion are milliseconds; values around 1.7 billion are seconds.

This epoch check prevents a common bug: dividing a millisecond column by 1000 and passing it to make_timestamp creates dates in the year 1970 instead of the intended modern date range. A column holding 1700000000000 passed to make_timestamp produces a date in January 1970, not the year 2023 that the data actually represents. Use the matching DuckDB helper for the magnitude you actually have to avoid this off-by-three-orders-of-magnitude error.

Notes

DuckDB native date types: DATE (calendar date), TIME, TIMESTAMP (date + time, microsecond precision), TIMESTAMPTZ (UTC-anchored instant displayed with an offset). CSV and JSON files often load date columns as VARCHAR; parse them with strptime(col, format) or cast with col::DATE / col::TIMESTAMP when the string is already in a recognized format. strptime format codes follow C strftime conventions: %Y=4-digit year, %m=month, %d=day, %H=hour, %M=minute, %S=second.

Date truncation and extraction: date_trunc('week', ts) rounds down to the start of the week (Monday). date_part('month', ts) returns an integer; EXTRACT(MONTH FROM ts) is equivalent. CURRENT_DATE, NOW(), and CURRENT_TIMESTAMP are available; NOW() and CURRENT_TIMESTAMP return the current timestamp at the start of the transaction. AGE(ts1, ts2) returns the interval between two timestamps.

Interval arithmetic: ts + INTERVAL '7 days', ts - INTERVAL '3 months'. epoch_ms(ts) converts a TIMESTAMP to milliseconds since Unix epoch; make_timestamp_ms(epoch_ms) reverses it. If a date column remains VARCHAR, parse or cast it explicitly before date arithmetic. CapyToolkit keeps these checks local in your browser.

Examples

SELECT
  strptime(created_at, '%Y-%m-%dT%H:%M:%SZ') AS ts,
  user_id
FROM events
LIMIT 10;

SELECT
  date_trunc('week', event_ts) AS week_start,
  COUNT(*) AS events
FROM events
GROUP BY 1
ORDER BY 1;

SELECT
  EXTRACT(HOUR FROM event_ts) AS hour_of_day,
  COUNT(*) AS n
FROM events
GROUP BY 1
ORDER BY 1;

SELECT *
FROM events
WHERE event_ts >= CURRENT_TIMESTAMP - INTERVAL '30 days';

SELECT
  make_timestamp_ms(epoch_ms) AS readable_ts,
  user_id,
  event_type
FROM raw_events
LIMIT 20;

SELECT
  strptime(created_at, '%Y-%m-%dT%H:%M:%SZ') AS ts,
  user_id
FROM events
LIMIT 10;

Parquet Export

The SQL Workbench exports any DuckDB query result as a typed Parquet file using DuckDB's native COPY ... TO writer.¹ Column types carry through exactly: no string coercion, no precision loss.² Use the Export button after any query to download the result as Parquet for use in downstream tools, and keep the original file untouched while you share a typed result file.

How DuckDB writes typed Parquet output

The Export button triggers a COPY ... TO statement through DuckDB internally, mapping each result column to its Parquet physical type. BIGINT columns become INT64, DOUBLE becomes DOUBLE, VARCHAR becomes BYTE_ARRAY with a UTF8 annotation, TIMESTAMP becomes INT64 with a TIMESTAMP_MICROS annotation, and BOOLEAN becomes BOOLEAN.² Parquet's physical and logical type rules define INT64, DOUBLE, BYTE_ARRAY, BOOLEAN, and timestamp annotations at the file-format level, ensuring that any compliant reader can interpret the file without external schema information.³⁴

Why typed output matters

No string coercion happens at any stage. A pipeline that reads a Parquet file, filters it in SQL, and exports the result to a new Parquet file preserves the original column types end to end, which eliminates the re-inferring step that makes CSV-based workflows error-prone and ensures that downstream tools receive correctly typed data.

This typed fidelity is what distinguishes Parquet export from CSV export, because CSV serialises every value as a text string and forces any downstream reader to re-infer types, introducing the risk that a numeric column is re-read as text. Parquet export also preserves the column names, types, and nullable flags from the query result, which means the output file documents its own schema without requiring a separate readme or data dictionary. Snappy compression applies to all exported Parquet files by default;¹ this codec is widely supported by analytical tools such as BigQuery, Spark, pandas, Polars, and DuckDB itself.

Exporting aggregations, JOINs, and derived columns

For derived columns that produce types DuckDB cannot map to a standard Parquet physical type, the workbench falls back to VARCHAR. Verify the output schema by loading the exported Parquet file back into the workbench and running DESCRIBE on it. This round-trip check confirms that the types you expected survived the export before you hand the file to a downstream tool.

Exporting any DuckDB result

Parquet export is not limited to SELECT * from a source file. Any query result is exportable: GROUP BY aggregations, LEFT JOINs between tables loaded in the same session, computed columns, window function results, and PIVOT output all export correctly. The exported file reflects exactly what the last query returned, including computed columns with user-defined aliases and the types DuckDB assigned to aggregated expressions. CapyToolkit keeps that export local, so you can validate the result before sending it elsewhere.

Choosing between Parquet, CSV, and Excel export

Choosing the right export format depends on what the downstream tool expects. CSV is universally supported and human-readable, but it carries no type information: a BIGINT column becomes a string of digits, and a TIMESTAMP becomes a formatted text value. Excel is useful for stakeholders who open the file in a spreadsheet, but it caps at roughly 1 million rows and applies its own type coercion.⁵ Parquet preserves types, compresses well, and reads fast in analytical tools.

When Parquet export is not available

Parquet export requires a DuckDB session. SQLite sessions run through sql.js rather than DuckDB, and DuckDB's native COPY ... TO writer is not accessible from sql.js. If the Export button shows Parquet greyed out, your current session is SQLite-backed. Load a Parquet, CSV, JSON, Arrow, Feather, Avro, or Excel file to start a DuckDB session where Parquet export is available.

Notes

Parquet export runs a COPY (SELECT ...) TO 'result.parquet' (FORMAT PARQUET) statement through DuckDB internally. The output file is typed: INT64 columns remain INT64, DOUBLE remains DOUBLE, TIMESTAMP remains TIMESTAMP. This differs from CSV export, which serialises all values as strings. Parquet export is available for all DuckDB-loaded file formats: Parquet, Arrow, Feather, CSV, TSV, JSON, NDJSON, Avro, DBF, Excel (after JS bridge), and GeoParquet. Parquet export is NOT available during SQLite sessions because those run through sql.js rather than DuckDB. Exporting is triggered by the Export button in the results panel; there is no SQL command to type. The exported file reflects the exact rows returned by the last query, including any filtering, aggregation, or JOIN. The Parquet file uses Snappy compression by default. There is no size limit imposed by the workbench; the browser memory ceiling applies.

Examples

SELECT *
FROM orders
WHERE status = 'fulfilled'
  AND order_date >= '2026-01-01';

SELECT
  region,
  COUNT(*) AS order_count,
  SUM(total) AS revenue
FROM orders
GROUP BY region
ORDER BY revenue DESC;

SELECT * FROM sales_data;

SELECT *
FROM places
WHERE country_code = 'DE'
  AND population > 100000;

SELECT *
FROM orders
WHERE status = 'fulfilled'
  AND order_date >= '2026-01-01';

SQL for Pandas Users

Pandas already gives you the mental model; SQL gives you another syntax for the same transformations. DuckDB SQL covers every common pandas operation: groupby is GROUP BY, merge is JOIN, query is WHERE, explode is UNNEST, pivot_table is PIVOT, and transform window operations are window functions.¹ The SQL Workbench runs DuckDB SQL directly on your CSV, Parquet, or Excel files without writing a line of Python.²

Translating groupby, filter, sort, and null handling

The most common pandas operations map directly to SQL clauses with near-identical semantics, so if you already understand how groupby, filter, and sort work in pandas, you can translate those patterns to SQL almost mechanically. df.groupby("region")["revenue"].sum() becomes SELECT region, SUM(revenue) FROM sales GROUP BY region. df.query("revenue > 1000") becomes WHERE revenue > 1000. df.sort_values("date", ascending=False) becomes ORDER BY date DESC. df[['id', 'name']] becomes SELECT id, name. These mappings cover the majority of data exploration tasks you will reach for.¹

Common pandas operations in SQL

Where pandas adds convenience methods, SQL requires more explicit syntax. df.drop_duplicates() becomes SELECT DISTINCT * FROM table. df.drop_duplicates(subset=['id']) requires a window function: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) AS rn FROM table) WHERE rn = 1. For null handling, df.fillna(0) becomes COALESCE(col, 0) and df.dropna(subset=['col']) becomes WHERE col IS NOT NULL. The window function pattern for deduplication is worth memorizing because it comes up frequently when you need to keep the most recent or highest-value row per group.

For pandas merge, transform, and pivot_table operations

For pandas merge operations, SQL JOIN types map cleanly, and the correspondence is close enough that you can translate a pandas merge to a SQL JOIN by memory without consulting the documentation. df.merge(other, on='id') becomes JOIN other ON table.id = other.id (inner join by default in both). how='left' becomes LEFT JOIN, how='right' becomes RIGHT JOIN, and how='outer' becomes FULL OUTER JOIN.³ When merging on multiple keys, list all key pairs in the ON clause: ON a.user_id = b.user_id AND a.date = b.date.

Reshaping and transform equivalents

df.pivot_table(values='revenue', index='product', columns='month', aggfunc='sum') maps to DuckDB's PIVOT statement: PIVOT sales ON month USING SUM(revenue) GROUP BY product.⁴ For pandas transform operations that add an aggregated column without reducing rows, use SQL window functions: df.groupby("region")["revenue"].transform("sum") becomes SUM(revenue) OVER (PARTITION BY region) without a GROUP BY. This window function pattern is the SQL equivalent of adding an aggregate back onto each original row, and it avoids the need for a self-join or subquery to achieve the same result.

When DuckDB SQL outperforms pandas on large files

Compared to pandas, DuckDB reads far fewer bytes for the same analytical query on Parquet files. Pandas loads the entire file into memory before any computation begins.⁵ For a large Parquet file where you only need three columns and a filtered subset of rows, pandas reads every column and every row before discarding what it does not need. DuckDB applies column pruning and filter pushdown: it reads only the three referenced columns and skips row groups where the min/max statistics prove no matching rows exist.⁶

When files exceed Python memory limits

For files larger than your available RAM, pandas raises a MemoryError or swaps to disk and becomes unresponsively slow. DuckDB streams Parquet data in row group increments, processing each chunk independently.⁶ Consequently, a very large Parquet file on a machine with limited RAM is queryable in DuckDB but fails in pandas. The workbench runs DuckDB-WASM in a browser tab with its own memory ceiling, but for files that fit within browser memory, the streaming behavior makes DuckDB consistently more efficient for column-selective, row-filtered queries.

This does not mean SQL replaces pandas for every task. Use pandas when you need custom Python functions, plotting, or machine-learning workflows. Use the SQL Workbench when you need quick inspection, filtering, aggregation, or type-safe export before returning to Python. That split keeps SQL focused on inspection and leaves Python for modeling, plotting, and machine learning.

Notes

Mental model mapping: df[['a','b']] = SELECT a, b; df.query('x > 0') = WHERE x > 0; df.groupby('col').agg({'val': 'sum'}) = GROUP BY col with SUM(val); df.merge(other, on='id') = JOIN other ON id; df.sort_values('col') = ORDER BY col; df.explode('col') = UNNEST(col); df.pivot_table(values='v', index='r', columns='c', aggfunc='sum') = PIVOT ... ON c USING SUM(v) GROUP BY r; df.assign(new_col=...) = SELECT *, expression AS new_col; df.drop_duplicates() = SELECT DISTINCT; df.fillna(0) = COALESCE(col, 0); df[df.col.str.contains('x')] = WHERE col LIKE '%x%' or WHERE regexp_matches(col, 'x'). DuckDB evaluates lazily in query planning and executes all operations in one pass where possible, which is why it often outperforms pandas on large files that do not fit in Python memory.

Examples

SELECT region,
       SUM(revenue) AS revenue
FROM sales
GROUP BY region
ORDER BY revenue DESC;

SELECT o.*,
       c.name AS customer_name,
       c.email
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

SELECT id,
       UNNEST(tags) AS tag
FROM articles;

SELECT
  region,
  order_date,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY region
    ORDER BY order_date
    ROWS UNBOUNDED PRECEDING
  ) AS cumulative_revenue
FROM orders;

SELECT
  first_name || ' ' || COALESCE(last_name, '') AS full_name,
  email
FROM contacts;

SELECT region,
       SUM(revenue) AS revenue
FROM sales
GROUP BY region
ORDER BY revenue DESC;