Interactive Examples

dbxlite includes ready-to-run examples that teach SQL concepts from basics to advanced analytics. Each example can be loaded instantly via URL.

Quick Start Examples

Get started with simple DuckDB queries that demonstrate core concepts.

Temp Tables

Create temporary tables from inline values and run aggregations.

-- Create a temp table from inline values
CREATE OR REPLACE TEMP TABLE sales AS
SELECT * FROM (
  VALUES
    ('2024-01-01', 'West', 120),
    ('2024-01-02', 'West', 95),
    ('2024-01-01', 'East', 200),
    ('2024-01-02', 'East', 155)
) AS t(order_date, region, amount);

-- Summarize totals by region
SELECT
  region,
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount
FROM sales
GROUP BY region
ORDER BY total_amount DESC;

Generate Series

Use generate_series() to create data programmatically.

-- Generate numbers 1..12 and label quarters
WITH nums AS (
  SELECT generate_series AS month_num
  FROM generate_series(1, 12)
)
SELECT
  month_num,
  CASE
    WHEN month_num <= 3 THEN 'Q1'
    WHEN month_num <= 6 THEN 'Q2'
    WHEN month_num <= 9 THEN 'Q3'
    ELSE 'Q4'
  END AS quarter,
  month_num * month_num AS squared
FROM nums;

Aggregations

GROUP BY, COUNT, SUM, AVG — the aggregate functions every analyst uses daily — plus ROLLUP for automatic subtotals and FILTER for conditional aggregates without subqueries.

-- FILTER computes conditional aggregates in one pass, no subqueries needed
SELECT
    region,
    SUM(units * unit_price) FILTER (WHERE product = 'widgets') AS widget_revenue,
    SUM(units * unit_price) FILTER (WHERE product = 'gadgets') AS gadget_revenue,
    SUM(units * unit_price)                                    AS total_revenue
FROM sales
GROUP BY region
ORDER BY total_revenue DESC;

Joins

INNER, LEFT, and FULL OUTER joins — how each one handles rows with no match on the other side.

-- LEFT JOIN keeps every row from users, NULLs where there's no matching order
SELECT u.name, o.order_id, o.total
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
ORDER BY u.name, o.order_id NULLS LAST;

Remote Data Examples

Query CSV and Parquet files directly from URLs - no upload required.

Classic Datasets

Analyze three popular datasets: Diamonds (54K rows), Titanic (891 passengers), and Gapminder (world development data).

-- Query diamonds dataset directly from GitHub
SELECT
    cut,
    COUNT(*) AS count,
    ROUND(AVG(price), 2) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM 'https://raw.githubusercontent.com/tidyverse/ggplot2/main/data-raw/diamonds.csv'
GROUP BY cut
ORDER BY avg_price DESC;

Wikipedia Pageviews

Query Wikipedia data from Hugging Face's Parquet files.

SELECT *
FROM 'https://huggingface.co/datasets/wikimedia/wikipedia/resolve/main/20231101.ab/train-00000-of-00001.parquet'
LIMIT 1000;

COVID-19 Statistics

Analyze global COVID-19 data from Our World in Data.

SELECT *
FROM 'https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/owid-covid-data.csv'
LIMIT 1000;

World Population

Query historical world population data.

US Baby Names

Explore baby name trends from 1880 to present using SSA data.

Learn DuckDB

Comprehensive tutorials covering DuckDB's powerful features.

Window Functions

Running totals, month-over-month deltas, and per-group rankings — without collapsing rows the way GROUP BY does.

Topics covered:

• SUM()/AVG() OVER (PARTITION BY ... ORDER BY ...) for running totals and moving averages
• LAG() for previous-row comparisons
• RANK() for ranking within each partition
• QUALIFY to filter on a window result in one statement, no wrapping subquery

Nested JSON

Extracting fields from JSON columns with ->, ->>, and UNNEST.

Topics covered:

• -> and ->> for object and array access
• Drilling into nested objects (payload -> 'user' ->> 'tier')
• UNNEST to expand a JSON array into rows
• json_array_length() and aggregating by a nested field

DuckDB Feature Tour

150+ lines covering CTEs, window functions, JSON operations, pivots, and more.

Topics covered:

• Creating and populating tables
• Window functions (RANK, DENSE_RANK, LAG)
• Common Table Expressions (CTEs)
• Date/time functions
• String manipulation
• Aggregations with FILTER clause
• PIVOT operations
• List and Struct types
• JSON operations

DuckDB Advanced Functions

500+ lines exploring advanced DuckDB features for power users.

Topics covered:

• QUALIFY clause for window function filtering
• Advanced window frames (ROWS vs RANGE vs GROUPS)
• GROUPING SETS, CUBE, ROLLUP
• List/Array functions and comprehensions
• MAP operations
• Recursive CTEs (Fibonacci, org charts)
• ASOF joins for time-series
• SAMPLE and approximate functions
• EXCLUDE and REPLACE in SELECT
• Custom MACROS
• COLUMNS expression for dynamic selection
• FROM-first syntax
• UNION BY NAME

Real-World Analytics

Analytical patterns pulled straight from production dashboards: cohorts, customer segmentation, and multi-part reporting.

Cohort Retention

Group users by signup month, then track what fraction came back in each subsequent month — the standard SaaS retention pattern.

Topics covered:

• DATE_TRUNC to bucket users into monthly cohorts
• DATE_DIFF for month-offset since signup
• FIRST_VALUE() OVER (...) to get each cohort's starting size
• Retention percentage per cohort/month cell

RFM Customer Segmentation

Score customers 1-5 on Recency, Frequency, and Monetary value with NTILE, then route them into segments like champion and at-risk.

Topics covered:

• NTILE(5) for quintile scoring on each dimension
• Combining R/F/M scores with CASE into named segments
• The classic e-commerce and subscription marketing model

World Development Dashboard

150+ lines across three chained analyses over 50+ years of country development data: growth trends, continental percentiles, and biggest improvers.

Topics covered:

• Multi-level CTEs chained across independent analyses
• LAG() for year-over-year growth rates
• RANK() within continent/year groups
• quantile_cont() for percentile breakdowns (p25/p50/p75)
• Self-joining a remote CSV by each country's first and last year

DuckDB Extensions

Extend DuckDB's capabilities with built-in and community extensions.

Core Extensions

TPC-H benchmark data generation, Full-Text Search, Spatial/GIS, and JSON operations.

Extensions demonstrated:

• TPC-H: Generate realistic benchmark data
• FTS: Full-text search with BM25 ranking
• Spatial: ST_Point, ST_Distance, GeoJSON
• JSON: Extract, query, and aggregate JSON data

Community Extensions

Uber's H3 hexagonal geospatial system and Rapidfuzz string matching.

Extensions demonstrated:

• H3: Hexagonal hierarchical geospatial indexing
• Rapidfuzz: Fuzzy string matching and similarity

BigQuery Examples

Query Google BigQuery datasets directly from your browser (requires Google authentication).

BigQuery Advanced Functions

600+ lines covering arrays, structs, window functions, JSON, geography, and more.

Topics covered:

• Array creation and operations
• UNNEST for flattening
• STRUCTs (simple and nested)
• Window functions
• Date/Time functions
• Regular expressions
• JSON functions
• Approximate aggregates
• PIVOT/UNPIVOT
• Recursive CTEs
• Geography functions

Public Datasets

Query real-world data from BigQuery's public datasets.

BQ ML — Train & Predict

Train and query machine learning models entirely in SQL with CREATE MODEL and ML.PREDICT — no separate ML pipeline.

Topics covered:

• Logistic regression (CREATE MODEL ... MODEL_TYPE='LOGISTIC_REG') with ML.EVALUATE and ML.PREDICT
• K-means clustering
• ML.GENERATE_TEXT for Gemini via a remote model
• ARIMA_PLUS time-series forecasting with ML.FORECAST

Training scans data and bills accordingly — check dbxlite's cost preview before running CREATE MODEL. ML.PREDICT on an already-trained model is free.

Snowflake Examples

Query Snowflake directly from your browser via OAuth 2.0 PKCE or a Programmatic Access Token — no CREATE SECURITY INTEGRATION or ACCOUNTADMIN required for the PAT path.

TPC-H Sample — Orders by Segment

Aggregate orders and revenue per customer segment using Snowflake's free SNOWFLAKE_SAMPLE_DATA — no setup beyond a grant most accounts already have.

Topics covered:

• SNOWFLAKE_SAMPLE_DATA.TPCH_SF1 (Snowflake's free sample dataset)
• JOIN + GROUP BY across orders and customers
• YEAR() date extraction

VARIANT / OBJECT / ARRAY

Snowflake's colon-path syntax and LATERAL FLATTEN for querying semi-structured JSON directly — no external data needed.

Topics covered:

• PARSE_JSON and colon-path access (payload:user::STRING)
• Drilling into nested objects (payload:metadata.device.os)
• ARRAY_SIZE and array indexing (payload:items[0].sku)
• LATERAL FLATTEN to aggregate across array elements

Window Functions + QUALIFY

Top customers per market segment using QUALIFY — Snowflake's (and DuckDB's) way to filter on a window function result without a wrapping subquery.

Topics covered:

• RANK() OVER (PARTITION BY ... ORDER BY ...)
• QUALIFY to filter directly on the rank
• Percent-of-segment share via a nested window sum

Time Travel

Query a table as it was seconds or minutes ago, and clone a past state without a restore.

Topics covered:

• AT (OFFSET => -N) to query N seconds in the past
• AT (TIMESTAMP => ...) for an absolute point in time
• CLONE ... AT (...) for zero-copy recovery
• Data retention limits (1 day on Standard, up to 90 on Enterprise+)

Cortex AI Functions

LLM and embedding functions that run inside your Snowflake warehouse, so text never leaves your account.

Topics covered:

• COMPLETE for chat-style generation (single prompt or multi-turn message array)
• SENTIMENT scoring from -1 to +1
• SUMMARIZE and TRANSLATE
• CLASSIFY_TEXT for multi-class labeling
• EMBED_TEXT_768 + VECTOR_COSINE_SIMILARITY for semantic search

Each Cortex call consumes warehouse credits and shows up on your Snowflake bill — batch queries if you have many rows.

Lakehouse & Federation

DuckDB reading open table formats and joining across sources without ETL or staging tables.

Iceberg & Delta Lake

Read open table formats directly from object storage or local disk, with snapshot time-travel. Iceberg works in-browser (WASM 1.31+); Delta requires Server mode.

Topics covered:

• iceberg_scan() for local and S3-hosted tables
• Iceberg snapshot time-travel
• iceberg_metadata / iceberg_snapshots for inspection
• delta_scan() (Server mode only — not in the WASM bundle as of duckdb-wasm 1.32)

Cross-source Federation

Join CSV, Parquet, and ATTACHed DuckDB databases as if they were one database — no ETL, no staging tables.

Topics covered:

• Joining local files with remote Parquet
• ATTACH to query across multiple DuckDB database files
• Postgres / SQLite / MySQL scanners (Server mode only — browsers can't open raw TCP sockets)
• Hive-partitioned glob reads (Server mode only)

URL Parameters

Control dbxlite behavior via URL parameters for sharing and embedding.

Loading Examples

Direct SQL

Pass SQL directly in the URL (must be URL-encoded).

sql.dbxlite.com/?sql=SELECT%201%20as%20test
sql.dbxlite.com/?sql=SELECT%20*%20FROM%20range(10)&run=true

Sharing via Gist

Share longer queries using GitHub Gists. See our detailed guide on sharing SQL for a complete walkthrough with working examples.

sql.dbxlite.com/?share=gist:YOUR_GIST_ID&run=true

Display Options

Combined Examples

# Example with Dracula theme
sql.dbxlite.com/?example=duckdb-temp&run=true&theme=dracula

# Example with Nord theme
sql.dbxlite.com/?example=wikipedia&run=true&theme=nord

# Example with Tokyo Night theme
sql.dbxlite.com/?example=remote-datasets&run=true&theme=tokyo-night

# Example with Catppuccin theme
sql.dbxlite.com/?example=duckdb-feature-tour&run=true&theme=catppuccin

# Light themes
sql.dbxlite.com/?example=covid&run=true&theme=github-light
sql.dbxlite.com/?example=population&run=true&theme=solarized-light
sql.dbxlite.com/?example=baby-names&run=true&theme=ayu-light

# Direct SQL with theme
sql.dbxlite.com/?sql=SELECT%20now()&tab=Timestamp&run=true&theme=nord

# Gist with all options
sql.dbxlite.com/?share=gist:abc123&run=true&theme=catppuccin&explorer=true

All Examples Reference