The Bottleneck: Inefficient Traditional Time Series Analysis

In data analytics, time series data is ubiquitous, ranging from sales figures and user activity to server monitoring metrics. However, performing complex analyses on time series data, such as calculating day-over-day changes, week-over-week trends, consecutive growth days, or moving averages, often comes with several challenges:

1. Complex SQL Queries: Traditionally, you might need to write multiple subqueries or use self-joins to retrieve historical data, leading to verbose and difficult-to-understand SQL statements.
2. Performance Bottlenecks: A large number of subqueries and self-joins can result in multiple data scans and the generation of intermediate result sets, causing query performance to drastically decline, especially when dealing with massive time series datasets.
3. Dependency on Temporary Tables: To simplify logic or improve performance, analysts sometimes create temporary tables to store intermediate calculation results, which adds complexity to the data processing workflow.

DuckDB, as a high-performance OLAP database, offers powerful Window Functions and the QUALIFY clause, which perfectly address these pain points, allowing you to perform time series analysis in a more concise and efficient manner.

DuckDB’s Solution: The Powerful Combination of QUALIFY and Window Functions

Window functions allow you to perform calculations over a “window” of rows related to the current row, while the QUALIFY clause enables filtering of results from window functions, similar to how a WHERE clause filters aggregate functions. When combined, they unleash astonishing analytical power.

Data Preparation

First, let’s create a simple sales table, to simulate sales data:

CREATE TABLE sales (
    sale_date DATE,
    product_id INTEGER,
    amount DECIMAL(10, 2)
);

INSERT INTO sales VALUES
('2026-10-01', 101, 100.00),
('2026-10-01', 102, 150.00),
('2026-10-02', 101, 120.00),
('2026-10-02', 102, 160.00),
('2026-10-03', 101, 110.00),
('2026-10-03', 102, 170.00),
('2026-10-04', 101, 130.00),
('2026-10-04', 102, 180.00),
('2026-10-05', 101, 140.00),
('2026-10-05', 102, 190.00),
('2026-10-06', 101, 105.00),
('2026-10-06', 102, 155.00),
('2026-10-07', 101, 115.00),
('2026-10-07', 102, 165.00),
('2026-10-08', 101, 200.00),
('2026-10-08', 102, 250.00);

The initial data is as follows:

| sale_date  | product_id | amount |
|------------|-----------:|-------:|
| 2026-10-01 | 101        | 100.00 |
| 2026-10-01 | 102        | 150.00 |
| 2026-10-02 | 101        | 120.00 |
| 2026-10-02 | 102        | 160.00 |
| 2026-10-03 | 101        | 110.00 |
| 2026-10-03 | 102        | 170.00 |
| 2026-10-04 | 101        | 130.00 |
| 2026-10-04 | 102        | 180.00 |
| 2026-10-05 | 101        | 140.00 |
| 2026-10-05 | 102        | 190.00 |
| 2026-10-06 | 101        | 105.00 |
| 2026-10-06 | 102        | 155.00 |
| 2026-10-07 | 101        | 115.00 |
| 2026-10-07 | 102        | 165.00 |
| 2026-10-08 | 101        | 200.00 |
| 2026-10-08 | 102        | 250.00 |

Case One: Calculating Day-over-Day Total Sales Growth

We want to calculate the total sales for each day and the day-over-day growth rate compared to the previous day.

SELECT
    sale_date,
    SUM(amount) AS daily_total,
    LAG(SUM(amount), 1) OVER (ORDER BY sale_date) AS previous_day_total,
    (SUM(amount) - LAG(SUM(amount), 1) OVER (ORDER BY sale_date)) / LAG(SUM(amount), 1) OVER (ORDER BY sale_date) AS daily_growth_rate
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Output:

| sale_date  | daily_total | previous_day_total | daily_growth_rate |
|------------|------------:|-------------------:|------------------:|
| 2026-10-01 |      250.00 |                 NULL |              NULL |
| 2026-10-02 |      280.00 |               250.00 |               0.12 |
| 2026-10-03 |      280.00 |               280.00 |               0.00 |
| 2026-10-04 |      310.00 |               280.00 |  0.10714285714285714 |
| 2026-10-05 |      330.00 |               310.00 |  0.06451612903225806 |
| 2026-10-06 |      260.00 |               330.00 | -0.21393939393939394 |
| 2026-10-07 |      280.00 |               260.00 |  0.07692307692307693 |
| 2026-10-08 |      450.00 |               280.00 |  0.6071428571428571 |

Case Two: Identifying Products with Consecutive Sales Growth

Suppose we want to find products whose sales have been continuously growing for several days within a certain period. This is where QUALIFY becomes particularly useful, as it allows us to filter after the window function calculations.

WITH daily_sales_by_product AS (
    SELECT
        sale_date,
        product_id,
        SUM(amount) AS daily_amount
    FROM sales
    GROUP BY sale_date, product_id
),
lagged_sales AS (
    SELECT
        sale_date,
        product_id,
        daily_amount,
        LAG(daily_amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_daily_amount
    FROM daily_sales_by_product
)
SELECT
    sale_date,
    product_id,
    daily_amount
FROM lagged_sales
QUALIFY daily_amount > prev_daily_amount
ORDER BY product_id, sale_date;

Output:

| sale_date  | product_id | daily_amount |
|------------|-----------:|-------------:|
| 2026-10-02 |        101 |       120.00 |
| 2026-10-04 |        101 |       130.00 |
| 2026-10-05 |        101 |       140.00 |
| 2026-10-08 |        101 |       200.00 |
| 2026-10-02 |        102 |       160.00 |
| 2026-10-03 |        102 |       170.00 |
| 2026-10-04 |        102 |       180.00 |
| 2026-10-05 |        102 |       190.00 |
| 2026-10-08 |        102 |       250.00 |

Authoritative References

• DuckDB Official Documentation - Window Functions: https://duckdb.org/docs/sql/window_functions
• DuckDB Official Documentation - QUALIFY Clause: https://duckdb.org/docs/sql/query_syntax/qualify

Summary

The QUALIFY clause is a very powerful feature in DuckDB. When combined with window functions, it can significantly simplify complex time series analysis logic and improve query performance. Through the examples in this article, you can see how to leverage this combination to avoid tedious self-joins and subqueries, processing data in a more elegant way.

Further Reading

If you’re interested in advanced DuckDB features or performance optimization, here are some recommended articles:

• DuckDB Installation and Usage Tutorial (All Platforms) — If you haven’t installed DuckDB yet, start with the CLI setup.
• The Complete Guide to DuckDB — A systematic read from data models to advanced features.
• Advanced DuckDB Aggregation Functions: STRING_AGG, ARRAY_AGG, MAP_AGG — Dive deeper into DuckDB’s aggregation capabilities.
• DuckDB Agent AI Brain: Building an Intelligent Data Analysis Assistant — Explore how DuckDB can be integrated with AI.

Verified environment: DuckDB 1.x/2.x (code tested against current stable CLI/Python client)

Full runnable code & test scripts: see the duckdblab-examples repo

Reproducibility

All SQL/Python snippets in this post are copy-paste runnable; test conditions and environment are stated in the article. If your results differ, cross-check the version and dataset notes above.