Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 38 ยท Getting Started

Your First Analysis with DuckDB

Walk through a complete first analysis in DuckDB style SQL: look at the data, filter, group, join, rank and summarize, with runnable examples on a small shop data set.

You have installed DuckDB and know where data can live. Now let's do what you will actually do most days: open some data, look around, and answer a business question with SQL. This lesson is a guided analysis of an online shop, from "what is in here?" to "which customers matter most?". Each step is standard SQL, so the browser stand-in runs it, and each step notes the extra DuckDB conveniences you would use in the real shell.

Our data has five tables: categories, products, customers, orders and order_items.

erDiagram
    customers ||--o{ orders : places
    orders ||--|{ order_items : contains
    products ||--o{ order_items : "appears in"
    categories ||--o{ products : groups

Step 1: look before you query

With an unfamiliar file or table, first learn its shape. In DuckDB there are two one-word helpers (read-only here):

SQL
-- DuckDB (read-only example)
DESCRIBE products;      -- column names and types
SUMMARIZE products;     -- min, max, average, distinct count, null percentage per column
SELECT * FROM products LIMIT 5;

SUMMARIZE is a unique DuckDB favorite: one statement gives you a profile of every column. You can also run DESCRIBE or SUMMARIZE on a file query such as SUMMARIZE SELECT * FROM 'sales.parquet'.

In portable SQL, the sample rows and a count are the first step:

SQL Stand-in: first look ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Step 2: filter and sort

WHERE picks rows, ORDER BY sorts them, LIMIT cuts the list:

SQL Stand-in: the five dearest products in stock ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DuckDB lets you also write LIMIT 5 as a percentage (LIMIT 10%), which is handy for peeking at a sample of a huge table.

Step 3: aggregate

Aggregates collapse many rows into one. GROUP BY makes one result row per group. Here: how many products, and what average price, in each category?

SQL Stand-in: products per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In DuckDB you can shorten the grouping with GROUP BY ALL, which groups by every non-aggregated column in the SELECT list, and ORDER BY ALL to sort by all columns. You will meet these in Friendly SQL.

Step 4: join and compute revenue

Revenue lives in order_items (quantity times unit price). Join to products and categories to see which categories earn the most, ignoring cancelled orders:

SQL Stand-in: revenue per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Notice the shape of the query: start from the table with the facts (order_items), join dimensions around it (orders, products, categories), filter, then group. This fact and dimension pattern is the backbone of analytical SQL and DuckDB's join engine is built for it.

Step 5: customers that matter

Which customers spent the most? Let's group by customer and keep only those over a threshold with HAVING, which filters groups after they are built:

SQL Stand-in: top customers ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Using the alias spent inside HAVING works in SQLite and also in DuckDB, which lets you reuse aliases almost everywhere.

Step 6: rank within groups

Window functions rank rows without collapsing them. Which product is the best seller in each category?

SQL Stand-in: best seller per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The same task in DuckDB can skip the second WITH step, thanks to QUALIFY, which filters on window results directly:

SQL
-- DuckDB (read-only example)
SELECT category_id, name, SUM(oi.quantity) AS units
FROM order_items oi JOIN products p ON p.id = oi.product_id
GROUP BY category_id, name
QUALIFY ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY units DESC) = 1;

Step 7: save and share

Once you have a result worth keeping, DuckDB can write it straight to a file (read-only):

SQL
-- DuckDB (read-only example)
COPY (SELECT * FROM revenue_by_category) TO 'revenue.csv' (HEADER, DELIMITER ',');
COPY (SELECT * FROM revenue_by_category) TO 'revenue.parquet' (FORMAT parquet);

This is covered in Writing files and remote data.

The analysis workflow

A repeatable workflow

Look DESCRIBE, SUMMARIZE, LIMIT
Filter WHERE and ORDER BY
Aggregate GROUP BY and HAVING
Combine JOIN facts to dimensions

Then rank, save and share.

1 / 2

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which DuckDB statement profiles every column of a table (min, max, distinct count, nulls)?

    SUMMARIZE returns one row per column with statistics. DESCRIBE only lists names and types.
  2. 2. What is the difference between WHERE and HAVING?

    Rows are filtered by WHERE first, groups are built, then HAVING keeps or drops whole groups.
  3. 3. In the revenue query, why is order_items the starting table?

    A fact table holds the numbers you aggregate, and dimension tables add descriptive columns through joins.
  4. 4. What does DuckDB's QUALIFY clause do?

    QUALIFY is to window functions what HAVING is to aggregates, so you do not need a second query layer.

Summary

  • Start every analysis by looking: DESCRIBE, SUMMARIZE and a few rows.
  • Filter with WHERE, group with GROUP BY, filter groups with HAVING.
  • Join fact tables to dimension tables to get descriptive columns.
  • Window functions rank within groups; QUALIFY makes that shorter in DuckDB.
  • COPY ... TO saves a result as CSV or Parquet.

Next: A tour of the DuckDB command line