Window Functions

How did this month compare with last month? What's the top product in each city? Those questions seem simple, yet plain group by can't answer them. Window functions can. They look at neighbouring rows, without collapsing the table. They're the most common advanced SQL question in interviews.

• This month versus last month
• Top product in each city
• The classic interview question

Comparing with the previous row

The with clause creates a named step, which makes queries readable. Inside, we get monthly revenue. Then lag looks back one row, within the order that we've given it. Run it. We now have the percentage change for each month. That's a report that every company wants, and it's three lines of real logic.

import duckdb

print(duckdb.sql("""
    WITH monthly AS (
        SELECT DATE_TRUNC('month', date) AS month, SUM(revenue) AS revenue
        FROM 'sales.csv' GROUP BY 1
    )
    SELECT strftime(month, '%Y-%m') AS month, ROUND(revenue) AS revenue,
           ROUND(100 * (revenue / LAG(revenue) OVER (ORDER BY month) - 1), 1) AS change
    FROM monthly ORDER BY month DESC LIMIT 4
""").df().to_string(index=False))

  month  revenue  change
2025-12 356262.0     8.4
2025-11 328751.0     7.8
2025-10 304887.0    14.5
2025-09 266337.0     4.7

Top item per group

What's the most profitable product in each city? Row number gives ranks. Partition by restarts the numbering in each city. And then we keep only rank one. Run it. One best product per city, and it's latte in each of these. Memorise this pattern. It's probably the single most common SQL interview question.

import duckdb

print(duckdb.sql("""
    SELECT city, product, profit FROM (
        SELECT city, product, ROUND(SUM(profit)) AS profit,
               ROW_NUMBER() OVER (PARTITION BY city ORDER BY SUM(profit) DESC) AS place
        FROM 'sales.csv' GROUP BY city, product
    ) WHERE place = 1
    ORDER BY profit DESC LIMIT 4
""").df().to_string(index=False))

  city product   profit
 Osaka   Latte 151187.0
 Milan   Latte 129973.0
Bergen   Latte 123368.0
 Tokyo   Latte 119664.0

SQL or pandas?

Which one should you use? Both, and they work well together. Here, Duck DB and pandas do the same job, and we time them. Run it. Pandas wins here, because this file only has seventy thousand rows. But SQL engines run in parallel on columns, and they can work on data that won't fit in memory, so with hundreds of millions of rows, the result flips. A good pattern is to use SQL to get your data down to a sensible size, then pandas for analysis and charts.

import time
import duckdb
import pandas as pd

t = time.time()
pd.read_csv("sales.csv").groupby("city")["revenue"].sum()
print(f"pandas: {time.time() - t:.3f}s")

t = time.time()
duckdb.sql("SELECT city, SUM(revenue) FROM 'sales.csv' GROUP BY city").df()
print(f"DuckDB: {time.time() - t:.3f}s")

pandas: 0.015s
DuckDB: 0.094s

Summary