SQL playground: SQLite and DuckDB

Both databases run entirely in your browser. SQLite (via sql.js) keeps one database for the whole page, so later blocks can use tables made by earlier ones — run the blocks in order. DuckDB (via duckdb-wasm) is a column-store analytics engine; use Load file… to query a CSV, Parquet, or JSON file from your computer — it is read locally and never uploaded.

SQLite

Create a table and query it

CREATE TABLE sales (region TEXT, month TEXT, amount INTEGER);
INSERT INTO sales VALUES
  ('North', '2026-01', 120), ('North', '2026-02', 150), ('North', '2026-03', 90),
  ('South', '2026-01', 200), ('South', '2026-02', 180), ('South', '2026-03', 240),
  ('West',  '2026-01',  80), ('West',  '2026-02', 110), ('West',  '2026-03', 130);

SELECT region, SUM(amount) AS total, ROUND(AVG(amount), 1) AS average
FROM sales GROUP BY region ORDER BY total DESC;

Window functions: rankings and running totals (uses the table above)

SELECT region, month, amount,
       RANK() OVER (PARTITION BY month ORDER BY amount DESC) AS rank_in_month,
       SUM(amount) OVER (PARTITION BY region ORDER BY month) AS running_total,
       amount - LAG(amount) OVER (PARTITION BY region ORDER BY month) AS change
FROM sales
ORDER BY region, month;

A recursive CTE draws a calendar

WITH RECURSIVE days(d) AS (
  SELECT date('2026-10-01')
  UNION ALL
  SELECT date(d, '+1 day') FROM days WHERE d < date('2026-10-31')
)
SELECT strftime('%W', d) - strftime('%W', '2026-10-01') + 1 AS week,
       group_concat(CASE strftime('%w', d) WHEN '1' THEN printf('%2d', strftime('%d', d)) END, '') AS Mo,
       group_concat(CASE strftime('%w', d) WHEN '2' THEN printf('%2d', strftime('%d', d)) END, '') AS Tu,
       group_concat(CASE strftime('%w', d) WHEN '3' THEN printf('%2d', strftime('%d', d)) END, '') AS We,
       group_concat(CASE strftime('%w', d) WHEN '4' THEN printf('%2d', strftime('%d', d)) END, '') AS Th,
       group_concat(CASE strftime('%w', d) WHEN '5' THEN printf('%2d', strftime('%d', d)) END, '') AS Fr,
       group_concat(CASE strftime('%w', d) WHEN '6' THEN printf('%2d', strftime('%d', d)) END, '') AS Sa,
       group_concat(CASE strftime('%w', d) WHEN '0' THEN printf('%2d', strftime('%d', d)) END, '') AS Su
FROM days GROUP BY week;

DuckDB

Analytics on generated data: a million rows in a moment

CREATE OR REPLACE TABLE readings AS
SELECT
  range AS id,
  TIMESTAMP '2026-01-01' + INTERVAL (range * 37) SECOND AS ts,
  ['kitchen', 'garage', 'workshop', 'office'][1 + range % 4] AS room,
  round(15 + 2.5 * (range % 4) + 6 * sin(range / 2000.0) + random() * 2, 2) AS celsius
FROM range(1000000);

SELECT room,
       count(*) AS readings,
       round(avg(celsius), 2) AS avg_c,
       round(quantile_cont(celsius, 0.95), 2) AS p95_c,
       min(ts)::DATE AS first_day,
       max(ts)::DATE AS last_day
FROM readings GROUP BY room ORDER BY room;

PIVOT and SUMMARIZE (uses the table above)

PIVOT (SELECT room, strftime(ts, '%m') AS month, celsius FROM readings)
ON month USING round(avg(celsius), 1) GROUP BY room ORDER BY room;

SUMMARIZE readings;

Your own data: press Load file…, then edit the table name

-- After loading, e.g., trips.csv the table is called "trips".
SHOW TABLES;