The Complete Guide to Time Series Analysis with DuckDB: From MoM/YoY to User Retention

SELECT
 date_trunc('month', sale_date) AS month,
 SUM(amount) AS monthly_revenue,
 -- MoM: compare with previous month
 LAG(SUM(amount)) OVER (ORDER BY date_trunc('month', sale_date)) AS prev_month_revenue,
 ROUND(
 100.0 * (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY date_trunc('month', sale_date)))
 / NULLIF(LAG(SUM(amount)) OVER (ORDER BY date_trunc('month', sale_date)), 0),
 2
 ) AS mom_growth_pct,
 -- YoY: compare with same month last year
 LAG(SUM(amount), 12) OVER (ORDER BY date_trunc('month', sale_date)) AS same_month_last_year,
 ROUND(
 100.0 * (SUM(amount) - LAG(SUM(amount), 12) OVER (ORDER BY date_trunc('month', sale_date)))
 / NULLIF(LAG(SUM(amount), 12) OVER (ORDER BY date_trunc('month', sale_date)), 0),
 2
 ) AS yoy_growth_pct
FROM sales
GROUP BY date_trunc('month', sale_date)
ORDER BY month;

SELECT
 sale_date,
 amount AS daily_revenue,
 -- 5-day moving average
 AVG(amount) OVER (
 ORDER BY sale_date
 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
 ) AS ma5,
 -- 20-day moving average
 AVG(amount) OVER (
 ORDER BY sale_date
 ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
 ) AS ma60,
 -- 60-day moving average
 AVG(amount) OVER (
 ORDER BY sale_date
 ROWS BETWEEN 59 PRECEDING AND CURRENT ROW
 ) AS ma60
FROM sales
ORDER BY sale_date;

-- Aggregate every 15 minutes (ideal for high-frequency trading data)
SELECT
 time_bucket(INTERVAL '15 minutes', timestamp) AS time_slot,
 COUNT(*) AS trade_count,
 AVG(price) AS avg_price,
 MAX(price) - MIN(price) AS price_range,
 SUM(volume) AS total_volume
FROM trades
WHERE timestamp >= TIMESTAMP '2026-07-01'
GROUP BY time_bucket(INTERVAL '15 minutes', timestamp)
ORDER BY time_slot;

-- Daily aggregation
SELECT
 time_bucket(INTERVAL '1 day', sale_date) AS day,
 SUM(amount) AS daily_revenue,
 COUNT(DISTINCT customer_id) AS active_customers
FROM orders
GROUP BY day
ORDER BY day;

WITH first_login AS (
 -- Each user's first login date
 SELECT
 user_id,
 MIN(login_date) AS first_date
 FROM user_logins
 GROUP BY user_id
),
login_after_first AS (
 -- Every login after the user's first one
 SELECT
 f.user_id,
 f.first_date,
 l.login_date,
 DATEDIFF('day', f.first_date, l.login_date) AS days_since_first
 FROM first_login f
 JOIN user_logins l ON f.user_id = l.user_id
 WHERE l.login_date > f.first_date
)
SELECT
 days_since_first AS retention_day,
 COUNT(DISTINCT user_id) AS retained_users,
 ROUND(100.0 * COUNT(DISTINCT user_id) / (
 SELECT COUNT(DISTINCT user_id) FROM first_login
 ), 2) AS retention_rate_pct
FROM login_after_first
WHERE days_since_first BETWEEN 0 AND 30
GROUP BY days_since_first
ORDER BY retention_day;

WITH groups AS (
 SELECT
 user_id,
 login_date,
 -- Consecutive login dates form the same group
 login_date - INTERVAL '1 day' * ROW_NUMBER() OVER (
 PARTITION BY user_id ORDER BY login_date
 ) AS grp
 FROM user_logins
)
SELECT
 user_id,
 MIN(login_date) AS period_start,
 MAX(login_date) AS period_end,
 COUNT(*) AS consecutive_days
FROM groups
GROUP BY user_id, grp
HAVING consecutive_days >= 3
ORDER BY user_id, period_start;

import duckdb
import pandas as pd

con = duckdb.connect()

# Create sample data
con.execute("""
CREATE TABLE sales AS
SELECT
 DATE '2026-01-01' + (random() * 365)::INT AS sale_date,
 (random() * 1000)::DECIMAL(10,2) AS amount,
 ('Beijing','Shanghai','Guangzhou','Shenzhen','Hangzhou')[1 + (random()*4)::INT] AS city,
 ('Electronics','Clothing','Food','Home','Books')[1 + (random()*4)::INT] AS category
FROM range(100000)
""")

# Generate monthly sales report in one query
report = con.execute("""
WITH monthly AS (
 SELECT
 date_trunc('month', sale_date) AS month,
 SUM(amount) AS revenue,
 COUNT(*) AS order_count,
 AVG(amount) AS avg_order_value
 FROM sales
 GROUP BY month
)
SELECT
 month,
 revenue,
 order_count,
 avg_order_value,
 LAG(revenue) OVER w AS prev_month_revenue,
 ROUND(100.0 * (revenue - LAG(revenue) OVER w) / NULLIF(LAG(revenue) OVER w, 0), 2) AS mom_growth,
 ROUND(AVG(revenue) OVER (
 ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
 ), 2) AS ma3_revenue
FROM monthly
WINDOW w AS (ORDER BY month)
ORDER BY month
""").fetchdf()

print(report.to_string(index=False))

SELECT
 month,
 revenue,
 LAG(revenue) OVER w AS prev_month,
 AVG(revenue) OVER w AS ma3,
 SUM(revenue) OVER w AS cumulative
FROM monthly_sales
WINDOW w AS (ORDER BY month);