Advanced SQL: window functions, CTEs and query plans

You will learn the SQL that separates a data engineer from someone who only knows SELECT ... WHERE: window functions (ranking, running totals, comparing with the previous row), CTEs (named steps that make long queries readable) and query plans (how to see why a query is slow).

Prerequisites: basic SQL (SELECT, WHERE, GROUP BY, JOIN) and Python 3 installed. We use SQLite because it comes with Python and needs no setup. Everything here also works in PostgreSQL, MySQL 8+, BigQuery and Snowflake, with small differences in date functions that we point out.

Create a new folder with two files. setup.sql builds a tiny orders table (all prices in ₹). run_sql.py loads it into a fresh in-memory database and runs whichever query file you give it.

CREATE TABLE orders (
  order_id   INTEGER PRIMARY KEY,
  customer   TEXT NOT NULL,
  order_date TEXT NOT NULL,
  total      INTEGER NOT NULL
);

INSERT INTO orders (order_id, customer, order_date, total) VALUES
 (1, 'Asha',  '2026-10-01', 300),
 (2, 'Ravi',  '2026-10-01', 120),
 (3, 'Asha',  '2026-10-03', 180),
 (4, 'Meena', '2026-10-03', 460),
 (5, 'Ravi',  '2026-10-04', 90),
 (6, 'Asha',  '2026-10-06', 220),
 (7, 'Meena', '2026-10-07', 150),
 (8, 'Ravi',  '2026-10-07', 310);

Creates one table with 8 orders from 3 customers over 7 days.

import sqlite3
import sys

conn = sqlite3.connect(":memory:")
with open("setup.sql", encoding="utf-8") as f:
    conn.executescript(f.read())

with open(sys.argv[1], encoding="utf-8") as f:
    cursor = conn.execute(f.read())

print(" | ".join(col[0] for col in cursor.description))
for row in cursor:
    print(" | ".join(str(value) for value in row))

Builds the database from setup.sql, runs the query in the file you pass, and prints the column names and rows.

To run a query, save it in a file such as q1.sql and run python run_sql.py q1.sql. Window functions need SQLite 3.25 or newer; any recent Python has this. Check with python -c "import sqlite3; print(sqlite3.sqlite_version)".

GROUP BY collapses many rows into one. A window function calculates something across a group of related rows but keeps every row. That is why they are so useful: you can add a rank, a running total or the previous value as a new column next to the original data.

The shape is always: function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY splits rows into groups (like GROUP BY, but without collapsing). ORDER BY sets the order inside each group.

SELECT order_id, customer, total,
       ROW_NUMBER() OVER (ORDER BY total DESC) AS row_num,
       RANK()       OVER (ORDER BY total DESC) AS rnk
FROM orders
ORDER BY total DESC

Numbers every order from the biggest to the smallest total.

Here both columns agree because no two totals are equal. They differ when there are ties. Suppose two orders both have a total of 300:

ROW_NUMBER always gives unique numbers (ties are broken arbitrarily unless you add more columns to ORDER BY). RANK gives tied rows the same rank and then skips numbers. DENSE_RANK gives tied rows the same rank and does not skip.

"Find each customer's biggest order." You cannot use WHERE rn = 1 in the same SELECT that creates rn, because WHERE runs before window functions. So you compute the rank in a CTE (a WITH clause, a named temporary result) and filter in the outer query.

WITH ranked AS (
    SELECT customer, order_id, total,
           ROW_NUMBER() OVER (PARTITION BY customer ORDER BY total DESC) AS rn
    FROM orders
)
SELECT customer, order_id, total
FROM ranked
WHERE rn = 1
ORDER BY customer

PARTITION BY customer restarts the numbering for each customer; rn = 1 keeps their biggest order.

SELECT customer, order_date, total,
       SUM(total) OVER (PARTITION BY customer ORDER BY order_date, order_id) AS running_total
FROM orders
ORDER BY customer, order_date, order_id

Adds up each customer's spending order by order.

When you add ORDER BY to an aggregate inside OVER, the default is "from the first row of the partition up to the current row", which is exactly a running total. We added order_id to the ORDER BY so that two orders on the same date have a fixed order.

LAG(column) gives the value from the previous row (in the window's order). LEAD(column) gives the next row. They are the tool for "compared with yesterday" questions. First step: total revenue per day, in a CTE. Second step: compare each day with the previous one.

WITH daily AS (
    SELECT order_date, SUM(total) AS revenue
    FROM orders
    GROUP BY order_date
)
SELECT order_date, revenue,
       LAG(revenue) OVER (ORDER BY order_date) AS prev_revenue,
       revenue - LAG(revenue) OVER (ORDER BY order_date) AS change
FROM daily
ORDER BY order_date

Day-over-day change in revenue. The first day has no previous row, so LAG gives NULL.

(Python prints SQL NULL as None; in a SQL client you will see NULL.)

A frame says which rows around the current row the function can see. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the current row and the two before it: a 3-row moving average.

WITH daily AS (
    SELECT order_date, SUM(total) AS revenue
    FROM orders
    GROUP BY order_date
)
SELECT order_date, revenue,
       ROUND(AVG(revenue) OVER (
           ORDER BY order_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS avg_last_3_days
FROM daily
ORDER BY order_date

Smooths out daily ups and downs. The first rows average over fewer than 3 rows because there is no earlier data.

You have already used CTEs. A CTE names a sub-query so the main query reads top to bottom, like a recipe: first compute daily totals, then compare days. Compare this with nesting the same logic inside FROM ( ... ) three levels deep. Both give the same result, but the CTE version is much easier to read, test and fix.

You can chain several CTEs by separating them with commas, and each can use the ones above it:

WITH ordered AS (
    SELECT customer, order_date,
           LAG(order_date) OVER (PARTITION BY customer ORDER BY order_date, order_id) AS prev_date
    FROM orders
)
SELECT customer, order_date, prev_date,
       CAST(julianday(order_date) - julianday(prev_date) AS INTEGER) AS days_between
FROM ordered
WHERE prev_date IS NOT NULL
ORDER BY customer, order_date

How many days passed between each customer's orders. julianday() is SQLite's date arithmetic; in PostgreSQL use order_date - prev_date, in BigQuery DATE_DIFF(order_date, prev_date, DAY).

When SQL runs slowly, guessing is a waste of time. Ask the database how it plans to run the query. In SQLite put EXPLAIN QUERY PLAN in front. In PostgreSQL and MySQL use EXPLAIN (and EXPLAIN ANALYZE in PostgreSQL, which also runs the query and shows real timings). Warehouses such as BigQuery and Snowflake show a query profile in their web console.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer = 'Asha'

Asks SQLite how it would find Asha's orders.

SCAN orders means a full table scan: the database reads every row to find the matching ones. For 8 rows it is nothing. For 80 million rows it is slow. Now add an index. Put this line at the end of setup.sql, then run q7.sql again.

CREATE INDEX idx_orders_customer ON orders(customer);

An index is a sorted lookup structure on one column, like the index at the back of a book.

SEARCH ... USING INDEX means the database jumps straight to the matching rows. The same idea applies in every database: a plan that scans a huge table to return a few rows is the first thing to look for.

You are a senior data engineer. Explain this SQL query step by step in plain English. Then list any bugs or risks (duplicated rows from joins, NULL handling, ties, missing days). Finally suggest a version that is easier to read using CTEs.

Database: [SQLite / PostgreSQL / BigQuery / Snowflake]
Table definitions:
[paste CREATE TABLE statements, no real data]

Query:
[paste your query]

What to expect: A good answer walks through each clause, flags real risks such as ties or a missing PARTITION BY, and gives the rewrite. Run both versions on your sample data and compare results.