---
title: "0.5.5 DuckDB and essential SQL on Parquet"
description: "SQL with DuckDB on the SINT Parquet and CSV files: SELECT, WHERE, GROUP BY, JOIN and look-ahead-free window functions; results to pandas and Polars."
url: "https://pythonparatrading.com/en/python-trading-course/level-0/duckdb-sql-on-parquet/"
lang: "en"
image: "https://pythonparatrading.com/og/en/python-trading-course/level-0/duckdb-sql-on-parquet.png"
---

# 0.5.5 · DuckDB and essential SQL: SELECT, JOIN, GROUP BY and window functions on Parquet

> SQL is the common language of data: brokers' databases, data warehouses and query engines all speak it. DuckDB runs it inside Python, with no server, directly on Parquet and CSV files. This lesson teaches the SQL you need for research with the SINT data, with special care on windows, where it is easy to use the future without noticing.

- Course: [Level 0: Python from scratch and a data mindset](https://pythonparatrading.com/en/python-trading-course/level-0/)
- Module: 0.5 · Numerical computing and data
- Duration: 50 min
- Prerequisites: [0.5.4 · Polars: lazy versus eager API, expressions, performance and when to pick it over pandas](https://pythonparatrading.com/en/python-trading-course/level-0/polars-lazy-api-and-expressions/)

## By the end of this lesson

- Query a Parquet or CSV file with `SELECT`, `WHERE`, `ORDER BY` and `LIMIT` from Python with DuckDB.
- Summarize by year with `GROUP BY` and combine the trade log with prices through `JOIN`.
- Compute returns, moving averages and drawdown with window functions (`LAG`, `AVG OVER`, `MAX OVER`) using an explicit `ROWS BETWEEN … PRECEDING AND CURRENT ROW` frame.
- Pass the result of a query to pandas or Polars and query with SQL a `DataFrame` that is already in memory.

## SQL without a server

SQL (*Structured Query Language*) describes **what** data you want, not **how** to get it: the engine decides the read
order, the indexes and the algorithms. It is the language of brokers' order and position databases, of market data
warehouses and of a good share of cloud research tools. Knowing how to read and write it is as basic as pandas.

To use it you need an engine. Classic databases (PostgreSQL, MySQL) are a separate server that the program connects
to. **DuckDB** is something else: an analytical database installed as a library (`uv add duckdb`) that runs inside the
Python process, like SQLite, but designed for queries that read and aggregate millions of rows by columns. And it reads
files directly: a Parquet or a CSV file is queried by its path, without importing it first.

The course version is DuckDB 1.5.6 (pinned in `curso-code/uv.lock`).

```python
import duckdb

con = duckdb.connect()   # in-memory database
print(duckdb.__version__)
con.sql("""
    SELECT fecha, cierre, volumen
    FROM 'sint_diario.parquet'
    ORDER BY fecha
    LIMIT 3
""")
```
```output
1.5.6
┌─────────────────────┬────────┬─────────┐
│        fecha        │ cierre │ volumen │
│      timestamp      │ double │  int64  │
├─────────────────────┼────────┼─────────┤
│ 2021-01-04 00:00:00 │  99.14 │ 2322609 │
│ 2021-01-05 00:00:00 │  99.25 │ 1517954 │
│ 2021-01-06 00:00:00 │  97.38 │ 3245658 │
└─────────────────────┴────────┴─────────┘
```

`duckdb.connect()` with no arguments creates an in-memory database, which disappears when the program ends; with a
path (`duckdb.connect("research.duckdb")`) it saves it to a file. `con.sql` returns a **relation**: the result of the
query, which prints as a table and can be converted to other formats. Under each column appears its SQL type:
`timestamp` (date with time), `double` (floating-point number) and `int64`.

## SELECT, WHERE and ORDER BY

A query always has the same structure. What changes with experience is knowing that the order in which it is written
is not the order in which it runs:

```mermaid
flowchart TB
  A[FROM and JOIN] --> B[WHERE]
  B --> C[GROUP BY and HAVING]
  C --> D[SELECT and windows]
  D --> E[ORDER BY and LIMIT]
```

First the tables are read and combined, then rows are discarded with `WHERE`, grouped, the `SELECT` columns are
computed and finally the result is sorted and trimmed. That order explains frequent errors: `WHERE` cannot use an alias
defined in the `SELECT` or the result of a window function, because they do not exist yet when it is evaluated.

```python
con.sql("""
    SELECT fecha::DATE AS dia, cierre, volumen
    FROM 'sint_diario.parquet'
    WHERE year(fecha) = 2022 AND volumen > 3500000
    ORDER BY volumen DESC
    LIMIT 5
""")
```
```output
┌────────────┬────────┬─────────┐
│    dia     │ cierre │ volumen │
│    date    │ double │  int64  │
├────────────┼────────┼─────────┤
│ 2022-04-22 │  99.38 │ 5400080 │
│ 2022-08-26 │ 112.63 │ 5010487 │
│ 2022-11-28 │  96.98 │ 4662485 │
│ 2022-05-23 │ 109.61 │ 4342762 │
│ 2022-12-05 │  99.44 │ 3676322 │
└────────────┴────────┴─────────┘
```

`fecha::DATE` converts the timestamp into a date (the Parquet file stored it with a time, as seen in lesson 0.5.4) and
`AS dia` names the resulting column. `year()` is one of DuckDB's date functions. Conditions are combined with `AND`,
`OR` and `NOT`, without the mandatory parentheses of pandas.

DuckDB reads from the Parquet file only the columns and blocks the query needs, just like Polars lazy mode: `EXPLAIN`
before a query shows the plan, with the projected columns (`Projections`) and the filters pushed down to the reader
(`Filters`).

## GROUP BY: yearly summaries

`GROUP BY` groups the rows with the same key value, and the aggregate functions (`count`, `min`, `max`, `avg`, `sum`…)
reduce each group to one row. Every `SELECT` column must be in the `GROUP BY` or inside an aggregation:

```python
con.sql("""
    SELECT year(fecha)               AS ano,
           count(*)                  AS sesiones,
           round(min(cierre), 2)     AS minimo,
           round(max(cierre), 2)     AS maximo,
           arg_max(cierre, fecha)    AS ultimo,
           round(avg(volumen) / 1e6, 2) AS vol_medio_m
    FROM 'sint_diario.parquet'
    GROUP BY ano
    ORDER BY ano
""")
```
```output
┌───────┬──────────┬────────┬────────┬────────┬─────────────┐
│  ano  │ sesiones │ minimo │ maximo │ ultimo │ vol_medio_m │
│ int64 │  int64   │ double │ double │ double │   double    │
├───────┼──────────┼────────┼────────┼────────┼─────────────┤
│  2021 │      260 │  95.38 │ 115.78 │ 114.21 │        1.89 │
│  2022 │      260 │  94.45 │ 125.98 │  104.5 │         1.9 │
│  2023 │      260 │ 101.72 │  121.8 │ 119.35 │         1.8 │
│  2024 │      262 │ 118.36 │ 137.15 │ 136.44 │        1.87 │
│  2025 │      261 │ 122.38 │ 149.74 │ 142.22 │        1.74 │
└───────┴──────────┴────────┴────────┴────────┴─────────────┘
```

`arg_max(cierre, fecha)` returns the `cierre` of the row with the maximum `fecha` in the group: the last close of each
year. It is a very useful DuckDB function with time series, and it avoids a subquery. 2022 has the highest maximum of
the first three years (125.98) and also the lowest minimum of the whole period (94.45): the year of the decline.
DuckDB allows the `ano` alias in the `GROUP BY`, a convenience not every SQL engine supports.

`HAVING` filters **after** grouping, on the results of the aggregations. To keep the years whose minimum fell below
100, the condition goes in `HAVING min(cierre) < 100`, not in `WHERE`.

## JOIN: trades and prices

`JOIN` combines rows from two tables that meet a condition, like `merge` in pandas (lesson 0.5.3). The two tables can
come from different files, even in different formats: the trade log is in CSV and the prices are in Parquet.

```python
con.sql("""
    SELECT o.id, o.fecha_entrada, o.lado,
           o.precio_entrada, p.cierre, p.volumen
    FROM 'operaciones_sint.csv' AS o
    JOIN 'sint_diario.parquet' AS p
      ON p.fecha = o.fecha_entrada
    WHERE o.simbolo = 'SINT'
    ORDER BY o.id
    LIMIT 3
""")
```
```output
┌───────┬───────────────┬─────────┬────────────────┬────────┬─────────┐
│  id   │ fecha_entrada │  lado   │ precio_entrada │ cierre │ volumen │
│ int64 │     date      │ varchar │     double     │ double │  int64  │
├───────┼───────────────┼─────────┼────────────────┼────────┼─────────┤
│     2 │ 2021-09-02    │ largo   │         108.64 │ 108.64 │ 1454984 │
│     3 │ 2021-10-27    │ corto   │         112.07 │ 112.07 │ 1644654 │
│     4 │ 2021-11-10    │ corto   │          104.3 │  104.3 │ 2575784 │
└───────┴───────────────┴─────────┴────────────────┴────────┴─────────┘
```

`AS o` and `AS p` are aliases so the file names are not repeated. The `ON` condition compares a date from the CSV
(`DATE`) with a timestamp from the Parquet file (`TIMESTAMP`); DuckDB converts the first to midnight and the comparison
works. With timestamps that carry a time, the same join would stop matching without raising an error: always check the
types of the keys.

| `JOIN` | Rows it keeps | pandas equivalent |
|---|---|---|
| `JOIN` (or `INNER JOIN`) | Only those with a match | `how="inner"` |
| `LEFT JOIN` | All rows on the left | `how="left"` |
| `FULL JOIN` | All rows on both sides | `how="outer"` |

Aggregation works on the result of the join as on any table. The net result of each symbol, with the trade sign
written with `CASE`:

```python
con.sql("""
    SELECT simbolo,
           count(*) AS operaciones,
           round(sum(
               CASE WHEN lado = 'largo' THEN 1 ELSE -1 END
               * (precio_salida - precio_entrada) * cantidad
               - comision), 2) AS total
    FROM 'operaciones_sint.csv'
    GROUP BY simbolo
    ORDER BY total DESC
""")
```
```output
┌─────────┬─────────────┬──────────┐
│ simbolo │ operaciones │  total   │
│ varchar │    int64    │  double  │
├─────────┼─────────────┼──────────┤
│ SINT    │          30 │  4405.08 │
│ RVAR    │           4 │    17.09 │
│ TECH    │           3 │   -35.02 │
│ ORO     │           3 │ -3237.96 │
└─────────┴─────────────┴──────────┘
```

The totals are the same as with pandas and Polars in the previous lessons: SINT gains 4,405.08 and `ORO` loses
3,237.96. Three different tools, the same number: when a computation is rewritten in another tool, comparing the result
with the original one is the cheapest test that no error has crept in.

## Window functions

A **window function** computes, for each row, a value from other related rows, **without grouping them**: the table
keeps all its rows. It is written with `OVER (...)`, which defines three things:

- `PARTITION BY`: the independent groups (for example, a symbol). Without it, the whole table is one partition.
- `ORDER BY`: the order of the rows within the partition. In time series, always the date.
- The **frame** (`ROWS BETWEEN … AND …`): which rows, relative to the current one, enter the computation.

The daily return needs the previous close. `LAG(cierre)` brings it to the current row:

```python
con.sql("""
    CREATE TABLE sint AS
    SELECT fecha::DATE AS fecha, cierre,
           cierre / LAG(cierre) OVER (ORDER BY fecha) - 1 AS r
    FROM 'sint_diario.parquet'
""")
con.sql("""
    SELECT fecha, cierre, round(r, 6) AS r
    FROM sint ORDER BY fecha LIMIT 3
""")
```
```output
┌────────────┬────────┬───────────┐
│   fecha    │ cierre │     r     │
│    date    │ double │  double   │
├────────────┼────────┼───────────┤
│ 2021-01-04 │  99.14 │      NULL │
│ 2021-01-05 │  99.25 │   0.00111 │
│ 2021-01-06 │  97.38 │ -0.018841 │
└────────────┴────────┴───────────┘
```

`CREATE TABLE ... AS` stores the result in a table of the in-memory database, so the read is not repeated in the
following queries. The first row has no previous close and its return is `NULL`, like the pandas `NaN`. `LAG` only
looks backwards; its counterpart, `LEAD`, looks forward and is the most direct way to introduce look-ahead bias into a
signal.

### Moving averages with an explicit frame

A 50-session moving average is the average of the current row and the 49 previous ones. It is written with an explicit
`ROWS` frame, which counts rows (sessions) and not dates:

```python
con.sql("""
    SELECT row_number() OVER (ORDER BY fecha) AS fila,
           fecha, cierre,
           round(AVG(cierre) OVER w, 2) AS media_50,
           COUNT(cierre) OVER w AS n
    FROM sint
    WINDOW w AS (ORDER BY fecha
                 ROWS BETWEEN 49 PRECEDING AND CURRENT ROW)
    QUALIFY fila IN (1, 2, 49, 50, 51)
    ORDER BY fecha
""")
```
```output
┌───────┬────────────┬────────┬──────────┬───────┐
│ fila  │   fecha    │ cierre │ media_50 │   n   │
│ int64 │    date    │ double │  double  │ int64 │
├───────┼────────────┼────────┼──────────┼───────┤
│     1 │ 2021-01-04 │  99.14 │    99.14 │     1 │
│     2 │ 2021-01-05 │  99.25 │     99.2 │     2 │
│    49 │ 2021-03-11 │ 101.76 │     97.6 │    49 │
│    50 │ 2021-03-12 │ 104.72 │    97.74 │    50 │
│    51 │ 2021-03-15 │ 106.21 │    97.88 │    50 │
└───────┴────────────┴────────┴──────────┴───────┘
```

`WINDOW w AS (...)` defines the frame once for several columns, and `row_number()` numbers the rows so that only a few
are shown with `QUALIFY` (the filter applied after the windows; explained below). The `n` column reveals the most
important difference from pandas `rolling(50)`: SQL computes with whatever rows are in the frame. The first row has a
"50-session average" that is its own close, and row 49 averages 49 closes; pandas would return `NaN` on the first 49.
From row 50 (97.74) the two match. To get a null with incomplete windows, wrap it in
`CASE WHEN COUNT(cierre) OVER w = 50 THEN AVG(cierre) OVER w END`, or filter by `n` as in exercise 3.

<aside class="callout" data-kind="riesgo">

Without an explicit frame, a window with `ORDER BY` defaults to `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`:
the whole past, plus the rows **tied** with the current one on the ordering key. With one date per row there are no
ties; with intraday bars ordered only by day, the window would include the rest of the day, which is the future. And
without `ORDER BY`, `OVER ()` is the entire table. Always write the `ORDER BY` and the `ROWS` frame.

</aside>

The difference between a frame that looks at the past and one that uses the whole table shows up in a single row:

```python
con.sql("""
    SELECT fecha, cierre,
           round(AVG(cierre) OVER (), 2) AS media_toda,
           round(AVG(cierre) OVER (
               ORDER BY fecha
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ), 2) AS media_hasta_hoy
    FROM sint
    QUALIFY fecha = DATE '2021-06-01'
""")
```
```output
┌────────────┬────────┬────────────┬─────────────────┐
│   fecha    │ cierre │ media_toda │ media_hasta_hoy │
│    date    │ double │   double   │     double      │
├────────────┼────────┼────────────┼─────────────────┤
│ 2021-06-01 │  99.56 │     117.26 │           100.2 │
└────────────┴────────┴────────────┴─────────────────┘
```

On June 1, 2021, the average of the whole series (117.26) includes four and a half years of future prices and is well
above the actual average up to that day (100.2). A rule such as "buy if the close is below its average" would look
excellent with the first column on an asset that rises, and could not be traded. `QUALIFY` filters **after** computing
the windows: with `WHERE`, the filter would be applied first and the average would be computed over a single row.

### Running maximum and drawdown

The drawdown needs the maximum of all closes up to today: a frame that starts at `UNBOUNDED PRECEDING`. And the rolling
volatility is the standard deviation of returns over a 21-row frame:

```python
riesgo = con.sql("""
    SELECT fecha, cierre,
           cierre / MAX(cierre) OVER (
               ORDER BY fecha
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) - 1 AS dd,
           STDDEV_SAMP(r) OVER (
               ORDER BY fecha
               ROWS BETWEEN 20 PRECEDING AND CURRENT ROW
           ) * sqrt(252) AS vol_21
    FROM sint
""")
con.sql("""
    SELECT round(min(dd), 4) AS peor_dd,
           arg_min(fecha, dd) AS fondo,
           round(max(vol_21), 4) AS vol_max
    FROM riesgo
""")
```
```output
┌─────────┬────────────┬─────────┐
│ peor_dd │   fondo    │ vol_max │
│ double  │    date    │ double  │
├─────────┼────────────┼─────────┤
│ -0.2503 │ 2022-10-20 │  0.3395 │
└─────────┴────────────┴─────────┘
```

The same −25.0 % with its trough on October 20, 2022 and the same maximum volatility of 33.9 % as in pandas and
Polars. A relation can be queried by the name of its Python variable (`FROM riesgo`): DuckDB chains the two queries
without materializing the first one. As with the moving average, the first rows have a volatility computed with fewer
than 21 returns (`STDDEV_SAMP` ignores the initial `NULL`), where pandas gave `NaN`; the maximum, far from the start,
is the same.

## From SQL to pandas and Polars, and back

A DuckDB query usually ends in a `DataFrame`. The relation's methods do it directly:

```python
import pandas as pd

df_pd = riesgo.df()
df_pl = riesgo.pl()
print(type(df_pd).__name__, df_pd.shape, df_pd["fecha"].dtype)
print(type(df_pl).__name__, df_pl.shape, df_pl.schema["fecha"])

precios = pd.read_csv("sint_diario.csv", parse_dates=["fecha"])
con.sql("SELECT count(*) AS sesiones FROM precios WHERE cierre > 120")
```
```output
DataFrame (1303, 4) datetime64[us]
DataFrame (1303, 4) Date
┌──────────┐
│ sesiones │
│  int64   │
├──────────┤
│      523 │
└──────────┘
```

`.df()` returns pandas and `.pl()` returns Polars (there is also `.arrow()`, which returns Arrow data, and
`.fetchall()`, which returns Python tuples). The last query goes the other way: `precios` is not a database table but a
pandas `DataFrame` in a Python variable, and DuckDB finds it by its name and queries it in place, without copying it.
The 523 sessions above 120 are the ones from lesson 0.5.2.

| Task | Natural tool |
|---|---|
| Queries with joins and aggregations over many Parquet files | DuckDB or Polars lazy |
| Logic shared with a SQL database or with another team | DuckDB (standard SQL) |
| Indexed time series, `resample`, statistical models | pandas |
| Chained transformations in Python | Polars |

The three tools combine without friction because they share Apache Arrow as their in-memory format. A common flow:
DuckDB filters and aggregates the large files, and pandas receives the already small table for the analysis.

## Exercises

1. With a query on `sint_diario.parquet`, compute the mean daily return and the number of sessions for each day of the
   week (use `dayname(fecha)` and `LAG`). Compare with the table from lesson 0.5.3.

**Solution.**

```python
con.sql("""
    SELECT dayname(fecha) AS dia,
           count(r) AS sesiones,
           round(avg(r) * 100, 3) AS media_pct
    FROM sint
    GROUP BY dia, isodow(fecha)
    ORDER BY isodow(fecha)
""")
```
```output
┌───────────┬──────────┬───────────┐
│    dia    │ sesiones │ media_pct │
│  varchar  │  int64   │  double   │
├───────────┼──────────┼───────────┤
│ Monday    │      260 │     0.045 │
│ Tuesday   │      261 │    -0.052 │
│ Wednesday │      261 │     0.019 │
│ Thursday  │      260 │     0.051 │
│ Friday    │      260 │     0.108 │
└───────────┴──────────┴───────────┘
```

The figures match the pandas ones. `count(r)` does not count `NULL` values, so the initial Monday (with no return) is
not counted; `isodow` (1 = Monday) sorts the days of the week instead of alphabetically. The same caveats apply: the
differences are of the order of the standard error.

2. Join each SINT trade with the close of the **exit** day and check that the exit price is that close in all of them.
   Use `LEFT JOIN` and count the trades without a match.

**Solution.**

```python
con.sql("""
    SELECT count(*) AS operaciones,
           count(p.cierre) AS con_precio,
           sum(CASE WHEN o.precio_salida = p.cierre
                    THEN 1 ELSE 0 END) AS iguales
    FROM 'operaciones_sint.csv' AS o
    LEFT JOIN sint AS p ON p.fecha = o.fecha_salida
    WHERE o.simbolo = 'SINT'
""")
```
```output
┌─────────────┬────────────┬─────────┐
│ operaciones │ con_precio │ iguales │
│    int64    │   int64    │ int128  │
├─────────────┼────────────┼─────────┤
│          30 │         30 │      30 │
└─────────────┴────────────┴─────────┘
```

With `LEFT JOIN`, a trade without a price on its exit day would appear with a null `p.cierre`, and `count(p.cierre)`
would be lower than `count(*)`. None is missing, and all 30 exits were made at the close, as the data README states.

3. Write a crossover signal without look-ahead bias: 1 if the close is above its 50-session average (with the full
   window) and 0 otherwise. In what percentage of the sessions with a full window was SINT above it?

**Solution.**

```python
con.sql("""
    WITH m AS (
        SELECT fecha, cierre,
               AVG(cierre) OVER w AS media,
               COUNT(cierre) OVER w AS n
        FROM sint
        WINDOW w AS (ORDER BY fecha
                     ROWS BETWEEN 49 PRECEDING AND CURRENT ROW)
    )
    SELECT count(*) AS sesiones,
           round(avg(CASE WHEN cierre > media THEN 1 ELSE 0 END), 3)
               AS por_encima
    FROM m
    WHERE n = 50
""")
```
```output
┌──────────┬────────────┐
│ sesiones │ por_encima │
│  int64   │   double   │
├──────────┼────────────┤
│     1254 │      0.649 │
└──────────┴────────────┘
```

`WITH` defines an auxiliary query (a CTE) that the main query uses as a table. The windows are computed inside the CTE
and the `n = 50` filter is applied outside, afterwards: at the same level, `WHERE` would act before the window. SINT
was above its average in 64.9 % of the 1,254 sessions with a full window. Each day's signal uses that day's close, so
it can only be traded at the close or in the next session.

## Self-assessment

1. In what order does SQL evaluate these clauses of a query?

   - A. `SELECT`, `FROM`, `WHERE`, `GROUP BY`, `ORDER BY`
   - B. `WHERE`, `FROM`, `SELECT`, `GROUP BY`, `ORDER BY`
   - C. `FROM`, `WHERE`, `GROUP BY`, `SELECT`, `ORDER BY`
   - D. In the order in which they are written

   *Answer C: `FROM`, `WHERE`, `GROUP BY`, `SELECT`, `ORDER BY`.* First the tables are read and combined (`FROM`, `JOIN`), then rows are filtered (`WHERE`), grouped (`GROUP BY`), the `SELECT` columns are computed (windows included) and finally the result is sorted. That is why `WHERE` cannot use a `SELECT` alias or a window function.

2. `AVG(cierre) OVER ()` on the row for June 1, 2021: what average does it compute?

   - A. That of the sessions up to June 1
   - B. That of the previous 50 sessions
   - C. None: it is a syntax error
   - D. That of the whole table, later sessions included: look-ahead bias

   *Answer D: That of the whole table, later sessions included: look-ahead bias.* Without `ORDER BY` or a frame, the window is the entire partition. To use only the past you have to order by date and limit the frame: `ROWS BETWEEN 49 PRECEDING AND CURRENT ROW`.

3. A 50-session moving average with `ROWS BETWEEN 49 PRECEDING AND CURRENT ROW`: what does it return on the tenth row of the table?

   - A. The average of the ten available rows
   - B. `NULL`, because there are not 50 rows
   - C. An error
   - D. The average of the next 50 rows

   *Answer A: The average of the ten available rows.* SQL computes with the rows present in the frame. Unlike pandas `rolling(50)`, it does not return null with incomplete windows: you have to check it with a `COUNT(*) OVER` on the same frame.

4. What does `con.sql("SELECT ... FROM precios")` do if `precios` is a pandas `DataFrame` defined in Python?

   - A. It fails, because `precios` is not a database table
   - B. It queries the `DataFrame` directly, without copying it into a table
   - C. It looks for a file called `precios`
   - D. It creates a database on disk

   *Answer B: It queries the `DataFrame` directly, without copying it into a table.* DuckDB looks among the Python variables for a pandas or Polars `DataFrame` or an Arrow table with that name and reads it in place (*replacement scan*).

## Key points

- DuckDB is an analytical database that runs inside Python: it queries Parquet and CSV by their path (`FROM 'sint_diario.parquet'`) without importing them.
- SQL is evaluated in this order: `FROM`/`JOIN`, `WHERE`, `GROUP BY`, `HAVING`, `SELECT` (with the windows), `ORDER BY`, `LIMIT`.
- Window functions compute over neighboring rows without grouping them: `LAG` for returns, `AVG OVER` for moving averages, `MAX OVER` for the running maximum.
- Against look-ahead bias, every window carries `ORDER BY fecha` and an explicit `ROWS BETWEEN n PRECEDING AND CURRENT ROW` frame. `OVER ()` uses the whole table, future included.
- `.df()` and `.pl()` return the result as pandas or Polars, and DuckDB can query with SQL a `DataFrame` that is already in memory.

## Resources

### Research

- [Big Data for trading with ArcticDB](https://pythonparatrading.com/en/big-data-for-trading/) · Research-0019 · 15 min

### Documentation

- [DuckDB: Python API](https://duckdb.org/docs/current/clients/python/overview.html) · duckdb.org
- [DuckDB: reading and writing Parquet](https://duckdb.org/docs/current/data/parquet/overview.html) · duckdb.org
- [DuckDB: window functions](https://duckdb.org/docs/current/sql/functions/window_functions.html) · duckdb.org
- [DuckDB: SQL on pandas DataFrames](https://duckdb.org/docs/current/guides/python/sql_on_pandas.html) · duckdb.org

### Data

- [Level 0 data: SINT daily bars (Parquet)](https://pythonparatrading.com/curso/datos/nivel-0/sint_diario.parquet) · Download · sint_diario.parquet

Previous lesson: [0.5.4 · Polars: lazy versus eager API, expressions, performance and when to pick it over pandas](https://pythonparatrading.com/en/python-trading-course/level-0/polars-lazy-api-and-expressions/)

Next lesson: [0.6.1 · Object-oriented Matplotlib, Seaborn and Plotly: price, drawdown and candlestick charts](https://pythonparatrading.com/en/python-trading-course/level-0/matplotlib-seaborn-plotly-charts/)