Analytical SQL

SQL for questions, not transactions

Every month Lucía Romero builds the KPI pack for Margriet de Wit, the CFO of Kestrel Energy: kWh delivered per tariff, the amount billed, overdue invoices, the biggest users. In a spreadsheet each of those numbers is a pivot table or a block of formulas. In SQL each is a query that can be rerun next month without copying anything, and that is what Joost Bakker is moving the pack to.

The SQL on the Databases & SQL pages filters, joins and groups. Analysis asks a different kind of question: which customer used the most within each tariff, how the amount billed accumulates month by month, what the subtotal per segment is and the grand total. Those questions need window functions, subtotals and date buckets. This page covers them; the basics of GROUP BY, a first look at window functions and common table expressions are explained there.

None of this runs on the production database. Hamid Rahimi, the database administrator, keeps heavy analysis off db01 for the reasons on Why analytics doesn't run on the production database; the queries below run on a separate copy.

DuckDB and the Kestrel sample data

DuckDB is an open-source analytical database that runs inside another program or as a single command-line tool, with no server to install. It stores data by column (see columnar storage), so it scans and aggregates millions of rows quickly on a laptop, and it reads CSV, JSON and Parquet files directly. Its SQL dialect follows PostgreSQL closely and adds a few conveniences of its own. As of October 2026 the current release is DuckDB 1.5; version 2.0 is scheduled for 21 October 2026. The examples on this page were run with DuckDB 1.5.6.

The examples use a small slice of Kestrel's data: 12 customers, 14 contracts with four tariff types (fixed, day_night, dynamic and variable), one meter per contract, 360 daily meter readings for September 2026, 37 invoices and a price list. The data contains a few deliberate quality problems, which some results below run into. The other pages of this topic use the same data, so their numbers agree with these. This is the plain grouping query that the rest of the page builds on, kWh delivered per tariff in September:

SELECT c.tariff,
       count(DISTINCT r.meter_id) AS meters,
       sum(r.kwh_import)          AS kwh_import
FROM daily_readings r
JOIN meters m    USING (meter_id)
JOIN contracts c USING (contract_id)
GROUP BY c.tariff
ORDER BY c.tariff;

┌───────────┬────────┬───────────────┐
│  tariff   │ meters │  kwh_import   │
│  varchar  │ int64  │ decimal(38,3) │
├───────────┼────────┼───────────────┤
│ day_night │      3 │      1828.518 │
│ dynamic   │      3 │      2057.276 │
│ fixed     │      3 │      1074.240 │
│ variable  │      3 │       597.561 │
└───────────┴────────┴───────────────┘

DuckDB prints results as a box with the column type under each name. sum() over a DECIMAL(8,3) column returns decimal(38,3): a wider type, so the total cannot overflow, with the same three decimals.

Window functions and the OVER clause

A GROUP BY turns the readings of a tariff into one row. Margriet also wants to see each meter's share of its tariff: the meter's own total and the tariff total on the same row. The OVER clause does that. Written after an aggregate or ranking function, it turns the function into a window function: the function is computed over a set of rows (the window) and the result is attached to every row, instead of collapsing the rows into one.

PARTITION BY

PARTITION BY inside OVER splits the rows into independent groups, like GROUP BY does, but without merging them: the function starts again for each partition. OVER () with nothing inside makes all rows one window.

WITH per_meter AS (
  SELECT c.tariff, r.meter_id, sum(r.kwh_import) AS kwh
  FROM daily_readings r
  JOIN meters m    USING (meter_id)
  JOIN contracts c USING (contract_id)
  GROUP BY c.tariff, r.meter_id
)
SELECT tariff, meter_id, kwh,
       sum(kwh) OVER (PARTITION BY tariff) AS tariff_kwh,
       round(100 * kwh / sum(kwh) OVER (PARTITION BY tariff), 1) AS pct_of_tariff
FROM per_meter
ORDER BY tariff, kwh DESC;

┌───────────┬──────────┬───────────────┬───────────────┬───────────────┐
│  tariff   │ meter_id │      kwh      │  tariff_kwh   │ pct_of_tariff │
│  varchar  │ varchar  │ decimal(38,3) │ decimal(38,3) │    double     │
├───────────┼──────────┼───────────────┼───────────────┼───────────────┤
│ day_night │ M-30011  │      1195.540 │      1828.518 │          65.4 │
│ day_night │ M-30007  │       336.270 │      1828.518 │          18.4 │
│ day_night │ M-30002  │       296.708 │      1828.518 │          16.2 │
│ dynamic   │ M-30006  │      1554.960 │      2057.276 │          75.6 │
│ dynamic   │ M-30004  │       271.212 │      2057.276 │          13.2 │
│ dynamic   │ M-30012  │       231.104 │      2057.276 │          11.2 │
│ fixed     │ M-30008  │       463.290 │      1074.240 │          43.1 │
│ fixed     │ M-30001  │       334.990 │      1074.240 │          31.2 │
│ fixed     │ M-30010  │       275.960 │      1074.240 │          25.7 │
│ variable  │ M-30014  │       221.324 │       597.561 │          37.0 │
│ variable  │ M-30005  │       208.233 │       597.561 │          34.8 │
│ variable  │ M-30009  │       168.004 │       597.561 │          28.1 │
└───────────┴──────────┴───────────────┴───────────────┴───────────────┘
  12 rows                                                    5 columns

The CTE first reduces 360 readings to 12 meter totals; the window then adds the tariff total to each of them. One meter dominates two tariffs: M-30006 delivers 75.6% of all dynamic kWh and M-30011 65.4% of day_night. Both belong to business customers, a bakery and a printing company. A total per tariff mostly tells you which tariff the big businesses chose, which matters on Exploratory Data Analysis.

Window functions are evaluated late: after WHERE, GROUP BY and HAVING, before ORDER BY. Two consequences follow. A window can use an aggregate (sum(sum(kwh)) OVER … is legal and useful), and a WHERE clause cannot use a window function, because the window does not exist yet when rows are filtered:

SELECT meter_id, reading_date, kwh_import
FROM daily_readings
WHERE kwh_import > 3 * median(kwh_import) OVER (PARTITION BY meter_id);

Binder Error: WHERE clause cannot contain window functions!

LINE 3: WHERE kwh_import > 3 * median(kwh_import) OVER (PARTITION BY meter_id);
                               ^

Compute the window in a CTE or subquery and filter outside it, or use QUALIFY (below). The same rule means a WHERE on the outer query never changes what a window in a CTE saw, while a WHERE in the same query does: filtering to one week before computing a 7-day average gives a different average for the first days than filtering afterward.

Rankings and the top row per group

A ranking function numbers the rows of each partition in the order given by ORDER BY inside OVER. The three common ones differ only in how they treat ties. Counting contracts per tariff gives a tie, because three tariffs have three contracts each:

SELECT tariff, count(*) AS contracts,
       row_number() OVER (ORDER BY count(*) DESC, tariff) AS row_number,
       rank()       OVER (ORDER BY count(*) DESC)         AS rank,
       dense_rank() OVER (ORDER BY count(*) DESC)         AS dense_rank
FROM contracts
GROUP BY tariff
ORDER BY contracts DESC, tariff;

┌───────────┬───────────┬────────────┬───────┬────────────┐
│  tariff   │ contracts │ row_number │ rank  │ dense_rank │
│  varchar  │   int64   │   int64    │ int64 │   int64    │
├───────────┼───────────┼────────────┼───────┼────────────┤
│ fixed     │         5 │          1 │     1 │          1 │
│ day_night │         3 │          2 │     2 │          2 │
│ dynamic   │         3 │          3 │     2 │          2 │
│ variable  │         3 │          4 │     2 │          2 │
└───────────┴───────────┴────────────┴───────┴────────────┘

• row_number() gives every row its own number, even within a tie. Which tied row gets which number is up to the database unless you add a tie-breaker; here tariff makes it repeatable.
• rank() gives ties the same number and then skips (a fourth tariff after three tied seconds would be 5th).
• dense_rank() gives ties the same number without gaps.

Related functions: ntile(4) splits the rows into four groups of nearly equal size (quartiles of customers by usage), and percent_rank() gives each row's relative position from 0 to 1.

The biggest user per tariff: QUALIFY

"Who is the biggest user in each tariff?" is a top-1-per-group question: rank within each tariff, then keep rank 1. DuckDB (like Snowflake and BigQuery) has a QUALIFY clause, which filters on a window function the way HAVING filters on an aggregate:

SELECT c.tariff, r.meter_id, sum(r.kwh_import) AS kwh,
       rank() OVER (PARTITION BY c.tariff ORDER BY sum(r.kwh_import) DESC) AS rnk
FROM daily_readings r
JOIN meters m    USING (meter_id)
JOIN contracts c USING (contract_id)
GROUP BY c.tariff, r.meter_id
QUALIFY rnk = 1
ORDER BY c.tariff;

┌───────────┬──────────┬───────────────┬───────┐
│  tariff   │ meter_id │      kwh      │  rnk  │
│  varchar  │ varchar  │ decimal(38,3) │ int64 │
├───────────┼──────────┼───────────────┼───────┤
│ day_night │ M-30011  │      1195.540 │     1 │
│ dynamic   │ M-30006  │      1554.960 │     1 │
│ fixed     │ M-30008  │       463.290 │     1 │
│ variable  │ M-30014  │       221.324 │     1 │
└───────────┴──────────┴───────────────┴───────┘

Three of the winners are business meters. The fourth, M-30014, belongs to contract CT-20014, whose customer does not exist in the customer table: the biggest variable-tariff user is a data error. A ranking puts extreme rows at the top, which makes it a quick way to find problems as well as answers.

QUALIFY is not in standard SQL. In PostgreSQL, SQL Server, MySQL or SQLite, compute the rank in a CTE and filter in the outer query:

WITH ranked AS (
  SELECT c.tariff, r.meter_id, sum(r.kwh_import) AS kwh,
         rank() OVER (PARTITION BY c.tariff ORDER BY sum(r.kwh_import) DESC) AS rnk
  FROM daily_readings r
  JOIN meters m    USING (meter_id)
  JOIN contracts c USING (contract_id)
  GROUP BY c.tariff, r.meter_id
)
SELECT tariff, meter_id, kwh
FROM ranked
WHERE rnk = 1
ORDER BY tariff;

Window frames: running totals and moving averages

With ORDER BY inside OVER, a window function no longer sees the whole partition: it sees a window frame, the rows from the start of the partition (or a set distance back) up to the current row. The frame moves along as the current row moves, which is how SQL computes running totals and moving averages.

Running totals and month-on-month change

A running total (cumulative sum) adds each row to the sum of all rows before it: the amount billed so far this year, the kWh used so far this month. The example groups invoices by month, then runs a window over the monthly sums. lag() fetches the value from the previous row, so the difference with the month before is one subtraction:

SELECT strftime(period, '%Y-%m') AS month,
       sum(amount_eur) AS billed,
       sum(sum(amount_eur)) OVER (ORDER BY period) AS billed_to_date,
       sum(amount_eur) - lag(sum(amount_eur)) OVER (ORDER BY period) AS change
FROM invoices
GROUP BY period
ORDER BY period;

┌─────────┬───────────────┬────────────────┬───────────────┐
│  month  │    billed     │ billed_to_date │    change     │
│ varchar │ decimal(38,2) │ decimal(38,2)  │ decimal(38,2) │
├─────────┼───────────────┼────────────────┼───────────────┤
│ 2026-06 │         92.40 │          92.40 │          NULL │
│ 2026-07 │       1751.10 │        1843.50 │       1658.70 │
│ 2026-08 │       1750.70 │        3594.20 │         -0.40 │
│ 2026-09 │       1964.35 │        5558.55 │        213.65 │
└─────────┴───────────────┴────────────────┴───────────────┘

June has a single invoice because the sample holds only one contract's June bill, so the jump to July is an artifact of the data, not growth. lag() returns NULL for the first row, where there is no previous month. Its mirror image lead() looks one row ahead, and both take an offset and a default: with one row per month and no gaps, lag(x, 12) compares with the same month a year earlier (NULL for the first year; a default of 0 would show the whole value as growth). They count rows, not months, so a missing month shifts every comparison after it.

Moving averages, and a moving median

A moving average smooths daily noise: each day shows the average of the last seven days. The frame is written out with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. The WINDOW clause names a window once so two functions can share it. For meter M-30001:

WITH smoothed AS (
  SELECT reading_date, kwh_import,
         round(avg(kwh_import) OVER w, 3) AS avg_7d,
         median(kwh_import) OVER w        AS median_7d
  FROM daily_readings
  WHERE meter_id = 'M-30001'
  WINDOW w AS (ORDER BY reading_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
)
SELECT * FROM smoothed
WHERE reading_date BETWEEN DATE '2026-09-06' AND DATE '2026-09-15'
ORDER BY reading_date;

┌──────────────┬──────────────┬────────┬──────────────┐
│ reading_date │  kwh_import  │ avg_7d │  median_7d   │
│     date     │ decimal(8,3) │ double │ decimal(8,3) │
├──────────────┼──────────────┼────────┼──────────────┤
│ 2026-09-06   │        9.240 │    8.2 │        8.110 │
│ 2026-09-07   │        7.290 │   8.07 │        7.730 │
│ 2026-09-08   │       91.300 │ 20.162 │        8.490 │
│ 2026-09-09   │        8.370 │ 20.145 │        8.370 │
│ 2026-09-10   │        7.610 │ 20.128 │        8.370 │
│ 2026-09-11   │        6.850 │ 20.111 │        8.370 │
│ 2026-09-12   │        9.958 │ 20.088 │        8.370 │
│ 2026-09-13   │        9.081 │ 20.066 │        8.370 │
│ 2026-09-14   │        7.170 │ 20.048 │        8.370 │
│ 2026-09-15   │        9.010 │  8.293 │        8.370 │
└──────────────┴──────────────┴────────┴──────────────┘
  10 rows                                   4 columns

One reading of 91.300 kWh, ten times this household's normal usage, pushes the 7-day average from 8.07 to 20.162 and keeps it there for a full week, until the bad day leaves the frame. The moving median hardly moves. The reading is a data-entry error (the decimal point is in the wrong place), but the lesson is general: an average follows a single extreme value, a median does not (see outliers and skew). The date filter sits outside the CTE, so the first rows shown still average the six days before them.

ROWS versus RANGE, and ties

Without an explicit frame, ORDER BY inside OVER uses the standard default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE works on values, not row positions: all rows with the same ORDER BY value are "the current row" together, so ties get the same running total. Two contracts are invoiced on the same dates here:

SELECT invoice_id, period, amount_eur,
       sum(amount_eur) OVER (ORDER BY period) AS default_frame,
       sum(amount_eur) OVER (ORDER BY period, invoice_id
                             ROWS UNBOUNDED PRECEDING) AS rows_frame
FROM invoices
WHERE contract_id IN ('CT-20001', 'CT-20002')
ORDER BY period, invoice_id;

┌────────────┬────────────┬───────────────┬───────────────┬───────────────┐
│ invoice_id │   period   │  amount_eur   │ default_frame │  rows_frame   │
│  varchar   │    date    │ decimal(10,2) │ decimal(38,2) │ decimal(38,2) │
├────────────┼────────────┼───────────────┼───────────────┼───────────────┤
│ INV-40002  │ 2026-07-01 │         78.15 │        142.35 │         78.15 │
│ INV-40003  │ 2026-07-01 │         64.20 │        142.35 │        142.35 │
│ INV-40014  │ 2026-08-01 │         81.60 │        294.80 │        223.95 │
│ INV-40015  │ 2026-08-01 │         70.85 │        294.80 │        294.80 │
│ INV-40026  │ 2026-09-01 │         86.35 │        469.25 │        381.15 │
│ INV-40027  │ 2026-09-01 │         88.10 │        469.25 │        469.25 │
└────────────┴────────────┴───────────────┴───────────────┴───────────────┘

The default frame jumps per month; the ROWS frame grows per invoice. Neither is wrong, but they answer different questions, and with ties a ROWS frame is only repeatable when the ORDER BY has a tie-breaker (invoice_id). Write the frame out whenever the order can have ties.

Building an analysis step by step with CTEs

Real analytical queries are long. Writing them as a chain of common table expressions, one step per CTE, keeps each step readable and, more importantly, checkable. Before trusting a result, count the rows and add up the key measure after every step. The KPI pack needs kWh per customer segment, so the readings are joined to meters, contracts and customers:

WITH readings AS (
  SELECT * FROM daily_readings
), with_contract AS (
  SELECT r.*, c.contract_id, c.customer_id
  FROM readings r
  JOIN meters m    USING (meter_id)
  JOIN contracts c USING (contract_id)
), with_customer AS (
  SELECT w.*, cu.segment
  FROM with_contract w
  JOIN customers cu USING (customer_id)
)
SELECT 'readings' AS step, count(*) AS rows_kept, sum(kwh_import) AS kwh FROM readings
UNION ALL
SELECT 'with_contract', count(*), sum(kwh_import) FROM with_contract
UNION ALL
SELECT 'with_customer', count(*), sum(kwh_import) FROM with_customer;

┌───────────────┬───────────┬───────────────┐
│     step      │ rows_kept │      kwh      │
│    varchar    │   int64   │ decimal(38,3) │
├───────────────┼───────────┼───────────────┤
│ readings      │       360 │      5557.595 │
│ with_contract │       360 │      5557.595 │
│ with_customer │       330 │      5336.271 │
└───────────────┴───────────┴───────────────┘

The last join loses 30 rows and 221.324 kWh without any error message: an inner join keeps only rows with a match, and contract CT-20014 points to customer C-10099, who does not exist. A KPI pack built on with_customer would report 4% less energy than was delivered. Finding the culprit is an anti-join (rows without a match); deciding what to do about it (fix the source, report "unknown customer" as its own group with a left join, or exclude it on purpose) is a data-quality decision, see Data Types and Data Quality.

Subtotals with ROLLUP and GROUPING SETS

A KPI table usually shows detail rows, subtotals and a grand total. ROLLUP adds them in one query: GROUP BY ROLLUP (segment, tariff) groups by (segment, tariff), then by (segment) alone, then by nothing, the grand total. The columns that a subtotal row does not group by are NULL in that row, and the function grouping(column) returns 1 in exactly those places, so you can label them. Running contracts (no end date) per segment and tariff:

SELECT CASE WHEN grouping(cu.segment) = 1 THEN 'all segments' ELSE cu.segment END AS segment,
       CASE WHEN grouping(c.tariff) = 1 THEN 'all tariffs' ELSE c.tariff END AS tariff,
       count(*) AS contracts
FROM contracts c
JOIN customers cu USING (customer_id)
WHERE c.end_date IS NULL
GROUP BY ROLLUP (cu.segment, c.tariff)
ORDER BY grouping(cu.segment), cu.segment, grouping(c.tariff), c.tariff;

┌──────────────┬─────────────┬───────────┐
│   segment    │   tariff    │ contracts │
│   varchar    │   varchar   │   int64   │
├──────────────┼─────────────┼───────────┤
│ business     │ day_night   │         1 │
│ business     │ dynamic     │         1 │
│ business     │ fixed       │         1 │
│ business     │ all tariffs │         3 │
│ household    │ day_night   │         2 │
│ household    │ dynamic     │         2 │
│ household    │ fixed       │         3 │
│ household    │ variable    │         2 │
│ household    │ all tariffs │         9 │
│ all segments │ all tariffs │        12 │
└──────────────┴─────────────┴───────────┘
  10 rows                      3 columns

Thirteen contracts have no end date, but the total says 12: the orphan contract CT-20014 again disappears in the join to customers. And one of the 12, CT-20013, only starts on 1 October. Whether it counts as "active" in a September report is a definition question, not a SQL question: Femke Jansen's approved definition (started, not ended on that date, known customer) leaves it out and gives 11 for 30 September. See Data Modeling for Analytics on defining a metric once.

GROUPING SETS is the general form: you list exactly the groupings you want. Invoices per month and status, a subtotal per month, and a grand total:

SELECT strftime(period, '%Y-%m') AS month, status,
       count(*) AS invoices, sum(amount_eur) AS amount_eur
FROM invoices
GROUP BY GROUPING SETS ((month, status), (month), ())
ORDER BY month NULLS LAST, status NULLS LAST;

┌─────────┬─────────┬──────────┬───────────────┐
│  month  │ status  │ invoices │  amount_eur   │
│ varchar │ varchar │  int64   │ decimal(38,2) │
├─────────┼─────────┼──────────┼───────────────┤
│ 2026-06 │ paid    │        1 │         92.40 │
│ 2026-06 │ NULL    │        1 │         92.40 │
│ 2026-07 │ overdue │        1 │         66.70 │
│ 2026-07 │ paid    │       11 │       1684.40 │
│ 2026-07 │ NULL    │       12 │       1751.10 │
│ 2026-08 │ overdue │        2 │        118.10 │
│ 2026-08 │ paid    │       10 │       1632.60 │
│ 2026-08 │ NULL    │       12 │       1750.70 │
│ 2026-09 │ open    │       10 │       1132.85 │
│ 2026-09 │ paid    │        2 │        831.50 │
│ 2026-09 │ NULL    │       12 │       1964.35 │
│ NULL    │ NULL    │       37 │       5558.55 │
└─────────┴─────────┴──────────┴───────────────┘
  12 rows                            4 columns

ROLLUP (a, b) is shorthand for GROUPING SETS ((a, b), (a), ()), and CUBE (a, b) for every combination, ((a, b), (a), (b), ()). The NULL in the status column of a subtotal row means "all statuses"; if the data itself contains NULLs, only grouping() tells the two apart. PostgreSQL, SQL Server, Oracle, MySQL 8 (with its own WITH ROLLUP syntax) and DuckDB support subtotals; SQLite does not, which is why these examples run on DuckDB.

Bucketing by date

Most analytical questions are "per period": per week, per month, weekdays versus weekends. date_trunc('week', d) rounds a date down to the start of its period (weeks start on Monday, following ISO 8601). In DuckDB it returns a timestamp, so cast it back to a date for clean output:

SELECT date_trunc('week', reading_date)::DATE AS week_start,
       count(*) AS readings,
       sum(kwh_import) AS kwh
FROM daily_readings
GROUP BY week_start
ORDER BY week_start;

┌────────────┬──────────┬───────────────┐
│ week_start │ readings │      kwh      │
│    date    │  int64   │ decimal(38,3) │
├────────────┼──────────┼───────────────┤
│ 2026-08-31 │       72 │      1048.874 │
│ 2026-09-07 │       85 │      1351.625 │
│ 2026-09-14 │       83 │      1260.115 │
│ 2026-09-21 │       84 │      1273.933 │
│ 2026-09-28 │       36 │       623.048 │
└────────────┴──────────┴───────────────┘

The first and last weeks are partial: the week of 31 August has six September days (72 readings from 12 meters), the week of 28 September only three. Comparing totals of unequal buckets is a classic mistake; compare averages per day, or show only complete periods. The middle weeks have 85, 83 and 84 readings instead of 84: the duplicate reading and the missing day show up here too.

Buckets do not have to be calendar periods. isodow() returns the ISO day of the week (Monday 1 to Sunday 7), which splits weekdays from weekends:

SELECT cu.segment,
       CASE WHEN isodow(r.reading_date) >= 6 THEN 'weekend' ELSE 'weekday' END AS day_type,
       round(avg(r.kwh_import), 2) AS avg_kwh_per_meter_day
FROM daily_readings r
JOIN meters m     USING (meter_id)
JOIN contracts c  USING (contract_id)
JOIN customers cu USING (customer_id)
GROUP BY cu.segment, day_type
ORDER BY cu.segment, day_type;

┌───────────┬──────────┬───────────────────────┐
│  segment  │ day_type │ avg_kwh_per_meter_day │
│  varchar  │ varchar  │        double         │
├───────────┼──────────┼───────────────────────┤
│ business  │ weekday  │                 43.12 │
│ business  │ weekend  │                 15.33 │
│ household │ weekday  │                  8.63 │
│ household │ weekend  │                  9.42 │
└───────────┴──────────┴───────────────────────┘

Businesses use far less at weekends, households a little more. avg() returns a DOUBLE, so round it for display.

Querying CSV and Parquet files directly

DuckDB does not need data to be loaded into a database first: a file name in single quotes is a table. That makes it a quick tool for files like the KBS export that lands every night at 02:30: semicolon-separated, with decimal commas. The small readings file below has the same delimiter and decimal comma (its dates are already ISO 8601). DuckDB's CSV sniffer detects the semicolon but not the decimal comma, so the number column stays text:

printf 'meter_id;reading_date;kwh_import\nM-30001;2026-09-07;7,290\nM-30001;2026-09-08;91,300\nM-30002;2026-09-07;9,493\nM-30002;2026-09-08;8,593\n' > readings.csv

duckdb -c "SELECT * FROM 'readings.csv' LIMIT 2"
duckdb -c "SELECT meter_id, sum(kwh_import) AS kwh
           FROM read_csv('readings.csv', delim = ';', decimal_separator = ',')
           GROUP BY meter_id ORDER BY meter_id"

┌──────────┬──────────────┬────────────┐
│ meter_id │ reading_date │ kwh_import │
│ varchar  │     date     │  varchar   │
├──────────┼──────────────┼────────────┤
│ M-30001  │ 2026-09-07   │ 7,290      │
│ M-30001  │ 2026-09-08   │ 91,300     │
└──────────┴──────────────┴────────────┘
┌──────────┬────────┐
│ meter_id │  kwh   │
│ varchar  │ double │
├──────────┼────────┤
│ M-30001  │  98.59 │
│ M-30002  │ 18.086 │
└──────────┴────────┘

read_csv takes the options the sniffer cannot guess: delimiter, decimal separator, column types (types = {'ean': 'VARCHAR'} keeps an 18-digit meter code as text), date format, header. The same works for read_parquet and read_json (which also reads JSON Lines). COPY … TO writes a result to a file, which is how a cleaned copy is made once and queried many times:

printf 'meter_id;reading_date;kwh_import\nM-30001;2026-09-07;7,290\nM-30001;2026-09-08;91,300\n' > readings.csv

duckdb -c "COPY (FROM read_csv('readings.csv', decimal_separator = ','))
           TO 'readings.parquet' (FORMAT parquet)"
duckdb -c "SELECT count(*) AS readings, max(kwh_import) AS highest FROM 'readings.parquet'"

┌──────────┬─────────┐
│ readings │ highest │
│  int64   │ double  │
├──────────┼─────────┤
│        2 │    91.3 │
└──────────┴─────────┘

Parquet stores the column types with the data, so nobody has to guess them again. Why that matters, and the traps in CSV, are on CSV, JSON and Parquet; how Joost turns these one-off commands into a nightly job is on Data Pipelines.

DuckDB shortcuts and portable SQL

DuckDB adds "friendly SQL" shortcuts that save typing during exploration. Some have been copied by other engines, but none is standard, so use them for exploring and write the standard form in anything other people will run on another database.