Your boss never asks for the grand total. They ask which product is number one in each category, and how this month compares to last. Answering that without losing the row-level detail is exactly what SQL window functions are for. This post builds up from GROUP BY and CTEs to ROW_NUMBER, RANK, LAG, and running totals, all on the SQLite that ships with Python.

“Programming isn’t about what you know; it’s about what you can figure out.”

Chris Pine, Learn to Program

Last Updated: July 2026 | Tested on: Python 3.14.6, SQLite 3.50 | Difficulty: Intermediate | Reading Time: 23 minutes

Once you can write a basic SELECT, the next wall you hit is analytics. Your boss does not ask “what is the total?” They ask “which product is number one in each category?”, “how did this month compare to last month?”, and “show me a running total by day.” Those questions all share a shape: you want to keep every row visible, but tag each one with something computed across a group. That is what window functions are for, and they are the single biggest jump in SQL skill you can make after learning SELECT.

Here is the mental split that makes the whole topic click. GROUP BY is a juicer: you put in ten oranges and get one glass of juice, the individual oranges are gone. A window function is a labeller: the ten oranges roll past on a belt and each one gets a sticker saying “3rd heaviest” or “running weight so far.” Same fruit, still ten of them, now each carries extra information. We will use both in this post, on a tiny grocery shop database you build in one script.

All of this is ANSI standard. SQL window functions were standardised back in SQL:2003 and today they work the same in PostgreSQL, MySQL 8, SQL Server, BigQuery, DuckDB, and the SQLite bundled with Python. At the time of writing, Python 3.14.6 ships SQLite 3.50, which has had window functions since version 3.25, so nothing here needs an install. Learn the syntax once and it travels with you to every database you ever touch.

The diagram above is the one picture to hold in your head for the rest of the post. A window function splits the rows into partitions (here, one per category), orders them, then slides a frame down the ordered rows building up a result like a running total. The key difference from GROUP BY is on the right: every original row survives, and a new column is bolted on. Keep glancing back at it as the queries get bigger.

First, the shop. Run this once to create a sales table with two dozen rows spread across categories, regions, and three months. Every query in the rest of the post reads from this same file, so keep it around.

📄 setup.py: build the shop database once

import sqlite3, os

if os.path.exists("shop.db"):
    os.remove("shop.db")

conn = sqlite3.connect("shop.db")
conn.execute("""
    CREATE TABLE sales (
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
        product  TEXT    NOT NULL,
        category TEXT    NOT NULL,
        region   TEXT    NOT NULL,
        sale_month TEXT  NOT NULL,   -- 'YYYY-MM'
        amount   INTEGER NOT NULL    -- rupees
    )
""")

rows = [
    ("Paneer",  "Dairy",     "North", "2026-01", 4200),
    ("Paneer",  "Dairy",     "North", "2026-02", 4800),
    ("Paneer",  "Dairy",     "North", "2026-03", 5100),
    ("Curd",    "Dairy",     "South", "2026-01", 2600),
    ("Curd",    "Dairy",     "South", "2026-02", 2400),
    ("Curd",    "Dairy",     "South", "2026-03", 3000),
    ("Butter",  "Dairy",     "North", "2026-01", 1800),
    ("Butter",  "Dairy",     "North", "2026-02", 2100),
    ("Spinach", "Vegetable", "North", "2026-01", 1200),
    ("Spinach", "Vegetable", "North", "2026-02", 1500),
    ("Spinach", "Vegetable", "North", "2026-03", 1400),
    ("Tomato",  "Vegetable", "South", "2026-01", 2200),
    ("Tomato",  "Vegetable", "South", "2026-02", 2600),
    ("Tomato",  "Vegetable", "South", "2026-03", 3100),
    ("Okra",    "Vegetable", "East",  "2026-01", 900),
    ("Okra",    "Vegetable", "East",  "2026-02", 1100),
    ("Mango",   "Fruit",     "West",  "2026-02", 5400),
    ("Mango",   "Fruit",     "West",  "2026-03", 6200),
    ("Banana",  "Fruit",     "South", "2026-01", 1600),
    ("Banana",  "Fruit",     "South", "2026-02", 1700),
    ("Banana",  "Fruit",     "South", "2026-03", 1750),
    ("Apple",   "Fruit",     "North", "2026-01", 3000),
    ("Apple",   "Fruit",     "North", "2026-02", 2800),
    ("Apple",   "Fruit",     "North", "2026-03", 3300),
]
conn.executemany(
    "INSERT INTO sales (product, category, region, sale_month, amount) VALUES (?, ?, ?, ?, ?)",
    rows,
)
conn.commit()

count = conn.execute("SELECT COUNT(*) FROM sales").fetchone()[0]
print(f"Seeded shop.db with {count} sales rows.")
conn.close()

▶ Output

Seeded shop.db with 24 sales rows.

What happened here: You now have a file called shop.db next to your script with 24 rows in a sales table. Each row is one product selling a certain rupee amount in one region in one month. That is enough shape to ask real questions: totals per category, rankings per product, and month-over-month trends. From here on, every script just opens this file and runs SQL against it.

GROUP BY and HAVING: Filter Before vs Filter After

GROUP BY takes many rows that share a value and squashes them into one summary row. Think of sorting a pile of grocery bills into stacks by category, then writing one total on a sticky note per stack. After a GROUP BY, you no longer see individual sales, you see one line per category with a SUM, COUNT, or AVG across it.

The part that trips everyone up is where the filtering goes. WHERE runs before the grouping and throws out individual rows. HAVING runs after the grouping and throws out whole groups based on their totals. The order is the whole point: WHERE decides which oranges go into the juicer, HAVING decides which glasses of juice you keep.

📄 groupby.py: GROUP BY, then WHERE before vs HAVING after

import sqlite3

conn = sqlite3.connect("shop.db")
conn.row_factory = sqlite3.Row

# GROUP BY: one row per category, with totals rolled up
print("Total sales per category:")
q1 = """
    SELECT category,
           COUNT(*)    AS num_rows,
           SUM(amount) AS total
    FROM sales
    GROUP BY category
    ORDER BY total DESC
"""
for r in conn.execute(q1):
    print(f"  {r['category']:10} rows={r['num_rows']}  total={r['total']}")

# WHERE filters rows BEFORE grouping; HAVING filters groups AFTER
print("\nCategories whose total sales cross 12000 (HAVING),")
print("counting only sales of 2000 or more (WHERE):")
q2 = """
    SELECT category,
           SUM(amount) AS big_total
    FROM sales
    WHERE amount >= 2000
    GROUP BY category
    HAVING SUM(amount) > 12000
    ORDER BY big_total DESC
"""
for r in conn.execute(q2):
    print(f"  {r['category']:10} big_total={r['big_total']}")

conn.close()

▶ Output

Total sales per category:
  Dairy      rows=8  total=26000
  Fruit      rows=8  total=25750
  Vegetable  rows=8  total=14000

Categories whose total sales cross 12000 (HAVING),
counting only sales of 2000 or more (WHERE):
  Dairy      big_total=24200
  Fruit      big_total=20700

What happened here: The first query is a plain rollup: three categories, one total each. The second query shows the two filters working together. WHERE amount >= 2000 ran first and dropped every small sale before grouping, so the totals shrank. Then HAVING SUM(amount) > 12000 ran on those shrunken totals and dropped the Vegetable group entirely, because its qualifying sales did not clear 12000. A rule that saves you a lot of confusion: you cannot use WHERE to filter on SUM or COUNT, because those do not exist yet when WHERE runs. Anything about a group total belongs in HAVING.

CTEs: Naming Your Steps with WITH

A CTE, or Common Table Expression, is a temporary named result you define with WITH at the top of a query and then use like a table. It does not save anything to disk, it just lives for the length of that one query. The reason to care is readability: without CTEs, multi-step logic turns into subqueries nested inside subqueries, and you end up reading the query inside-out.

Think of it like prepping a recipe. Instead of one giant run-on instruction, you write “Step 1: make the totals. Step 2: use the totals.” Each step has a name, and anyone reading it follows top to bottom. Here is the same real question, find the categories that beat the average category total, written both ways so you can feel the difference.

📄 cte.py: a nested-subquery monster, then the same query with a CTE

import sqlite3

conn = sqlite3.connect("shop.db")
conn.row_factory = sqlite3.Row

# The nested-subquery version: reads inside-out, computes totals twice
nested = """
    SELECT category, SUM(amount) AS total
    FROM sales
    GROUP BY category
    HAVING SUM(amount) > (
        SELECT AVG(cat_total) FROM (
            SELECT SUM(amount) AS cat_total
            FROM sales
            GROUP BY category
        )
    )
    ORDER BY total DESC
"""
print("Nested-subquery version:")
for r in conn.execute(nested):
    print(f"  {r['category']:10} total={r['total']}")

# The same query with a CTE: one named step, read top to bottom
cte = """
    WITH category_totals AS (
        SELECT category, SUM(amount) AS total
        FROM sales
        GROUP BY category
    )
    SELECT category, total
    FROM category_totals
    WHERE total > (SELECT AVG(total) FROM category_totals)
    ORDER BY total DESC
"""
print("\nCTE version (same result, readable):")
for r in conn.execute(cte):
    print(f"  {r['category']:10} total={r['total']}")

conn.close()

▶ Output

Nested-subquery version:
  Dairy      total=26000
  Fruit      total=25750

CTE version (same result, readable):
  Dairy      total=26000
  Fruit      total=25750

What happened here: Both queries return the same two categories, Dairy and Fruit, because the average category total is about 21,917 and only those two clear it. But look at the two SQL blocks. The nested version defines the per-category total twice and forces your eye to jump to the innermost parenthesis first. The CTE version names the totals once as category_totals, then reads like a sentence: give me categories from category_totals where the total beats the average of category_totals. You can stack several CTEs by separating them with commas, and each one can refer to the ones above it, which is how you build a genuinely complex query nobody has to reverse-engineer later.

Window Functions: Keep Every Row, Add a Column

Now the main event. A window function computes something across a set of rows related to the current row, but unlike GROUP BY it does not collapse anything. Every input row still comes out, just with an extra column attached. The syntax is a function followed by an OVER (...) clause, and inside the parentheses you say how to slice and order the rows.

The everyday version is a class ranking. The teacher does not delete any students, everyone stays on the list, but each student gets a rank based on their marks. PARTITION BY is “rank them within each class separately” and ORDER BY inside the OVER clause is “sort by marks to decide the rank.” Below we rank every product by its total sales, restarting the count inside each category.

📄 window.py: ROW_NUMBER and RANK inside each category

import sqlite3

conn = sqlite3.connect("shop.db")
conn.row_factory = sqlite3.Row

# GROUP BY collapses rows; a window function keeps every row and adds a column.
ranked = """
    WITH product_totals AS (
        SELECT category, product, SUM(amount) AS total
        FROM sales
        GROUP BY category, product
    )
    SELECT
        category,
        product,
        total,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY total DESC) AS row_num,
        RANK()       OVER (PARTITION BY category ORDER BY total DESC) AS rnk
    FROM product_totals
    ORDER BY category, total DESC
"""
print(f"  {'category':10} {'product':8} {'total':>6} {'row_num':>8} {'rank':>5}")
for r in conn.execute(ranked):
    print(f"  {r['category']:10} {r['product']:8} {r['total']:>6} {r['row_num']:>8} {r['rnk']:>5}")

conn.close()

▶ Output

  category   product   total  row_num  rank
  Dairy      Paneer    14100        1     1
  Dairy      Curd       8000        2     2
  Dairy      Butter     3900        3     3
  Fruit      Mango     11600        1     1
  Fruit      Apple      9100        2     2
  Fruit      Banana     5050        3     3
  Vegetable  Tomato     7900        1     1
  Vegetable  Spinach    4100        2     2
  Vegetable  Okra       2000        3     3

What happened here: First a CTE rolled each product up to a single total. Then two window functions ranked those totals. Notice the count restarts at 1 for every category, that is PARTITION BY category doing its job. ROW_NUMBER always gives a strict 1, 2, 3 with no ties, so it is your pick when you need exactly one row per position.

RANK looks identical here only because no two products tie, but if two products had the same total, RANK would give them both the same number and then skip the next one (1, 1, 3), while ROW_NUMBER would still force a 1, 2, 3. There is also DENSE_RANK, which ties without skipping (1, 1, 2). Pick based on how you want ties handled.

Running Totals and LAG: Sliding Over Ordered Rows

Ranking is one job. The other big one is comparing a row to its neighbours over time. Two functions carry most of this work. A running total is SUM(...) OVER (ORDER BY ...), which adds up everything from the start of the partition down to the current row, like a bank balance that grows with each transaction. LAG reaches back to a previous row so you can compare this month to last month, and its twin LEAD reaches forward.

The frame clause, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, is the sliding window from the diagram written out in words: start at the very first row of the partition and include everything up to where you are now. Here it is on monthly totals, computed separately inside each category.

📄 window2.py: running total and LAG inside each category

import sqlite3

conn = sqlite3.connect("shop.db")
conn.row_factory = sqlite3.Row

q = """
    WITH monthly AS (
        SELECT category, sale_month, SUM(amount) AS month_total
        FROM sales
        GROUP BY category, sale_month
    )
    SELECT
        category,
        sale_month,
        month_total,
        SUM(month_total) OVER (
            PARTITION BY category ORDER BY sale_month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_total,
        LAG(month_total) OVER (
            PARTITION BY category ORDER BY sale_month
        ) AS prev_month
    FROM monthly
    ORDER BY category, sale_month
"""
print(f"  {'category':10} {'month':8} {'total':>6} {'running':>8} {'prev':>6}")
for r in conn.execute(q):
    prev = r['prev_month'] if r['prev_month'] is not None else '-'
    print(f"  {r['category']:10} {r['sale_month']:8} {r['month_total']:>6} {r['running_total']:>8} {str(prev):>6}")

conn.close()

▶ Output

  category   month     total  running   prev
  Dairy      2026-01    8600     8600      -
  Dairy      2026-02    9300    17900   8600
  Dairy      2026-03    8100    26000   9300
  Fruit      2026-01    4600     4600      -
  Fruit      2026-02    9900    14500   4600
  Fruit      2026-03   11250    25750   9900
  Vegetable  2026-01    4300     4300      -
  Vegetable  2026-02    5200     9500   4300
  Vegetable  2026-03    4500    14000   5200

What happened here: Read the Dairy rows top to bottom. The running_total column grows 8600, then 17900, then 26000, each value being the sum so far within Dairy. When the partition switches to Fruit, the running total resets to 4600 and starts climbing again, because PARTITION BY category keeps each category’s window separate. The prev column is LAG pulling in the previous month’s total, which is exactly what you need for a month-over-month comparison. The first row of every partition shows a dash because there is no earlier row to look back to, so LAG returns NULL. That NULL is normal and you handle it in the next section.

Three Queries Every Analyst Writes Weekly

These three patterns show up in real dashboards constantly. If you learn nothing else from this post, learn these, because they are the difference between “I know SQL syntax” and “I can actually answer business questions.” The first two share a trick: rank the rows with a window function inside a CTE, then filter on that rank in the outer query.

Top-N per group and month-over-month change

📄 analyst.py: top 2 per category, and month-over-month percentage

import sqlite3

conn = sqlite3.connect("shop.db")
conn.row_factory = sqlite3.Row

# 1) TOP-N PER GROUP: the top 2 products in every category
top_n = """
    WITH product_totals AS (
        SELECT category, product, SUM(amount) AS total
        FROM sales
        GROUP BY category, product
    ), ranked AS (
        SELECT category, product, total,
               ROW_NUMBER() OVER (PARTITION BY category ORDER BY total DESC) AS rn
        FROM product_totals
    )
    SELECT category, product, total
    FROM ranked
    WHERE rn <= 2
    ORDER BY category, total DESC
"""
print("1) Top 2 products per category:")
for r in conn.execute(top_n):
    print(f"   {r['category']:10} {r['product']:8} {r['total']}")

# 2) MONTH-OVER-MONTH change, as a signed percentage
mom = """
    WITH monthly AS (
        SELECT sale_month, SUM(amount) AS total
        FROM sales
        GROUP BY sale_month
    )
    SELECT
        sale_month,
        total,
        LAG(total) OVER (ORDER BY sale_month) AS prev,
        ROUND(
            100.0 * (total - LAG(total) OVER (ORDER BY sale_month))
            / LAG(total) OVER (ORDER BY sale_month), 1
        ) AS pct_change
    FROM monthly
    ORDER BY sale_month
"""
print("\n2) Month-over-month change (whole shop):")
for r in conn.execute(mom):
    pct = f"{r['pct_change']:+.1f}%" if r['pct_change'] is not None else "  -"
    print(f"   {r['sale_month']}  total={r['total']:>6}  change={pct}")

conn.close()

▶ Output

1) Top 2 products per category:
   Dairy      Paneer   14100
   Dairy      Curd     8000
   Fruit      Mango    11600
   Fruit      Apple    9100
   Vegetable  Tomato   7900
   Vegetable  Spinach  4100

2) Month-over-month change (whole shop):
   2026-01  total= 17500  change=  -
   2026-02  total= 24400  change=+39.4%
   2026-03  total= 23850  change=-2.3%

What happened here: The top-N query is the pattern to memorise. You cannot put a window function directly in a WHERE clause, because WHERE runs before the window is computed. So you compute ROW_NUMBER inside a CTE, then filter WHERE rn <= 2 in the outer query. Change the 2 to any N and you have top-N per group. The month-over-month query uses LAG to fetch the previous month’s total, then does the classic percentage change formula. February jumped 39.4 percent over January, then March slipped 2.3 percent. January shows a dash because there is no earlier month to compare against.

Deduplication: keep one row per key

The third weekly query is cleaning duplicates, and it is the same rank-then-filter trick. Say a signup form double-submitted and you have the same email twice. You want to keep the earliest signup and drop the rest. Number the rows within each email by join date, then keep only number one.

📄 dedup.py: keep the first signup per email

import sqlite3

# A fresh in-memory table with accidental duplicate signups
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.execute("CREATE TABLE signups (id INTEGER PRIMARY KEY, email TEXT, joined TEXT)")
conn.executemany(
    "INSERT INTO signups (id, email, joined) VALUES (?, ?, ?)",
    [
        (1, "aditi@shop.in",  "2026-01-05"),
        (2, "anvay@shop.in",  "2026-01-06"),
        (3, "aditi@shop.in",  "2026-02-01"),  # duplicate email
        (4, "aviraj@shop.in", "2026-02-03"),
        (5, "anvay@shop.in",  "2026-03-10"),  # duplicate email
    ],
)

# Keep only the FIRST signup per email; ROW_NUMBER numbers each email's rows.
dedup = """
    WITH numbered AS (
        SELECT id, email, joined,
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY joined) AS rn
        FROM signups
    )
    SELECT id, email, joined
    FROM numbered
    WHERE rn = 1
    ORDER BY id
"""
print("One row per email (earliest kept):")
for r in conn.execute(dedup):
    print(f"   id={r['id']}  {r['email']:16} joined={r['joined']}")

conn.close()

▶ Output

One row per email (earliest kept):
   id=1  aditi@shop.in    joined=2026-01-05
   id=2  anvay@shop.in    joined=2026-01-06
   id=4  aviraj@shop.in   joined=2026-02-03

What happened here: Five rows went in with two repeated emails, three rows came out. PARTITION BY email ORDER BY joined numbered each person’s signups starting from their earliest date, so the first ever signup for each email got rn = 1. Filtering WHERE rn = 1 kept exactly one row per email and dropped the later duplicates for Aditi and Anvay. Swap the ORDER BY to joined DESC and you would keep the most recent one instead. This is the standard SQL way to deduplicate, and it beats guessing with GROUP BY because you get to choose exactly which row survives.

From SQL Windows to Pandas

If you already know pandas, or you are heading into the data chapters of this series, here is the good news: every one of these SQL window functions has a direct pandas twin built on groupby. The mental model carries straight over. PARTITION BY becomes groupby, and the window function becomes a method that returns one value per row instead of one value per group.

Here is the running total and rank from earlier, redone in pandas so you can see the same numbers appear.

📄 pandas_equiv.py: the same window results with groupby

import pandas as pd

# The same monthly-per-category data, now as a DataFrame
df = pd.DataFrame({
    "category":    ["Dairy", "Dairy", "Dairy", "Fruit", "Fruit", "Fruit"],
    "sale_month":  ["2026-01", "2026-02", "2026-03", "2026-01", "2026-02", "2026-03"],
    "month_total": [8600, 9300, 8100, 4600, 9900, 11250],
}).sort_values(["category", "sale_month"])

# SQL:  SUM(...) OVER (PARTITION BY category ORDER BY sale_month)
df["running_total"] = df.groupby("category")["month_total"].cumsum()

# SQL:  LAG(month_total) OVER (PARTITION BY category ORDER BY sale_month)
df["prev_month"] = df.groupby("category")["month_total"].shift(1)

# SQL:  RANK() OVER (PARTITION BY category ORDER BY month_total DESC)
df["rank"] = df.groupby("category")["month_total"].rank(ascending=False, method="min").astype(int)

print(df.to_string(index=False))

▶ Output

category sale_month  month_total  running_total  prev_month  rank
   Dairy    2026-01         8600           8600         NaN     2
   Dairy    2026-02         9300          17900      8600.0     1
   Dairy    2026-03         8100          26000      9300.0     3
   Fruit    2026-01         4600           4600         NaN     3
   Fruit    2026-02         9900          14500      4600.0     2
   Fruit    2026-03        11250          25750      9900.0     1

What happened here: The running_total column matches the SQL output row for row: 8600, 17900, 26000 for Dairy, then a reset to 4600 for Fruit. That reset is groupby('category') doing the same job as PARTITION BY category. The prev_month column shows NaN on the first row of each group, which is pandas’ version of the SQL NULL from LAG. Same idea, same numbers, two different tools. Knowing both means you can pull analytics out of a database with SQL or out of a DataFrame with pandas, and switch based on where the data already lives. This example needs pandas installed (pip install pandas), unlike the SQLite scripts which run on the standard library alone.

Common Mistakes

Mistake 1: Putting a window function in WHERE

You write WHERE ROW_NUMBER() OVER (...) <= 3 and the database rejects it. This is not a quirk, it is the order of operations. WHERE runs before window functions are calculated, so the rank does not exist yet when WHERE looks for it. The fix is always the same: compute the window function in a CTE (or subquery), then filter on its result in the outer query. Every top-N query in this post uses that two-step shape for exactly this reason.

Mistake 2: Forgetting ORDER BY inside a running total

A running total without an ORDER BY in the OVER clause is meaningless, because “the sum up to here” needs a defined order to know what “here” means. Leave it out and different databases do different things, some sum the whole partition on every row. If you want a cumulative value, always order the window. If you want a plain group total repeated on every row, that is a different intent, and leaving ORDER BY out on purpose is how you get it.

Mistake 3: Confusing WHERE and HAVING

Trying to filter on SUM(amount) > 12000 in a WHERE clause fails, because the sum is not computed until after grouping. Group totals belong in HAVING, individual row conditions belong in WHERE. When a query errors with something about an aggregate not being allowed, that is almost always a HAVING condition sitting in the WHERE by mistake.

Best Practices

• DO reach for a window function when the question keeps every row and adds a comparison, like a rank or a running total. Reach for GROUP BY when you genuinely want fewer rows.
• DO name your steps with CTEs once a query has more than one stage. Future you, reading it in six months, will be grateful.
• DO always put an ORDER BY inside the OVER clause for running totals, LAG, LEAD, and ROW_NUMBER. The order defines the answer.
• DO handle the NULL that LAG and LEAD return on the edge rows, either with COALESCE or by letting the dash show, as we did here.
• DON’T filter on a window function or an aggregate inside WHERE. Use a CTE plus outer filter for windows, and HAVING for aggregates.
• DON’T nest subqueries three deep when a couple of CTEs would read top to bottom. The database does not care, but the next human does.

Conclusion

You now have the analyst core of SQL in your hands. GROUP BY with HAVING rolls rows up into totals and filters those totals. CTEs give your multi-step logic names so it reads like a recipe instead of a puzzle. And SQL window functions, the real prize, let you rank, run totals, and compare each row to its neighbours while keeping every row on screen. Top-N per group, deduplication, and month-over-month change are the three queries you will write again and again, and all three are just rank-then-filter or LAG on ordered rows.

Because this is standard SQL, none of it is throwaway knowledge. The same OVER (PARTITION BY ... ORDER BY ...) you just ran on SQLite works unchanged on PostgreSQL, MySQL, BigQuery, and Snowflake. And when the data lives in a DataFrame instead of a table, the pandas groupby twins do the same job. Master the window and you have one idea that pays off in the database, in pandas, and in every analytics interview.

Next up is SQL indexes and query performance, where you learn why some of these queries are instant and others crawl, and how an index fixes it. For the pandas side of the story, jump to Pandas GroupBy: Split-Apply-Combine. And whenever you want the full roadmap, from beginner basics through the AI and ML chapters, visit the Python + AI/ML tutorial series home.

Practice Exercises

1. Exercise 1: Using the sales table, write a query that returns each region with its total sales, but only for regions whose total is above 8000. Use GROUP BY and HAVING.
2. Exercise 2: Write a CTE that computes each product’s total, then in the outer query return only products that sold more than the average product total.
3. Exercise 3: Rank every sale within its region by amount using RANK, and return only the single top sale per region (the rank-then-filter pattern).
4. Exercise 4: For the Dairy category, compute the month-over-month change in rupees (not percentage) using LAG, and show a dash for the first month.

Frequently Asked Questions

Interview Questions on SQL Window Functions

These come from real screens and onsites. Practice answering before you read each answer.

Q: You need the top 3 selling products in each category. Walk me through the query.

This is the classic top-N-per-group problem, and it is a two-step query. First, in a CTE, assign each product a number within its category using ROW_NUMBER() OVER (PARTITION BY category ORDER BY total DESC). Second, in the outer query, filter WHERE rn <= 3. You need the CTE because a window function cannot go directly in a WHERE clause, since WHERE runs before the window is computed. If ties should all be kept, swap ROW_NUMBER for RANK.

Q: Two products tie for the top revenue spot. Walk through what ROW_NUMBER, RANK, and DENSE_RANK each assign to the row after the tie, and when the difference bites.

ROW_NUMBER ignores the tie entirely: it still hands out 1, 2, 3, choosing arbitrarily between the tied rows, so the row after the tie gets 3. RANK and DENSE_RANK both give tied rows the same rank, but they differ on what comes next. If two rows tie for first, RANK gives 1, 1, then 3, skipping the 2. DENSE_RANK gives 1, 1, then 2, with no gap. Use RANK when you want the numbering to reflect how many rows were ahead, like standings where two golds mean no silver. Use DENSE_RANK when you want compact tiers with no gaps, like grouping salaries into distinct bands.

Q: How would you compute a month-over-month percentage change in pure SQL?

Aggregate to monthly totals first, usually in a CTE. Then use LAG(total) OVER (ORDER BY month) to fetch the previous month’s total on each row, and compute 100.0 * (total - prev) / prev. The 100.0 forces floating-point division so you do not get integer truncation. The first month returns NULL because LAG has nothing to look back to, which you handle with COALESCE or just display as a dash.

Q: A query has three levels of nested subqueries and nobody can read it. How do you improve it?

Rewrite it with CTEs. Each nested subquery usually maps to one named CTE, defined at the top with WITH, and later CTEs can reference earlier ones. This flattens the inside-out reading order into a top-to-bottom sequence of named steps. It rarely changes performance, since the database planner treats them similarly, but it makes the logic reviewable and lets you test each step in isolation.

Q: What does PARTITION BY do, and how is it different from GROUP BY?

PARTITION BY divides the rows into groups for a window function to work within, restarting the calculation for each group, but it does not reduce the row count. GROUP BY also groups, but it collapses each group into a single output row. So PARTITION BY category gives you a running total that resets per category while every row survives, whereas GROUP BY category gives you one total row per category and nothing else.

Q: How do you remove duplicate rows but keep the most recent one per key?

Use ROW_NUMBER() OVER (PARTITION BY key ORDER BY created_at DESC) inside a CTE, which numbers each key’s rows from newest to oldest. Then keep WHERE rn = 1 in the outer query to select the most recent row per key. To keep the oldest instead, order ascending. This is more precise than GROUP BY because you explicitly choose which row wins, and to actually delete you wrap the same logic in a DELETE against the row ids you did not keep.

Further reading: for the full reference, see SQL reference (SQLite).

Related Posts

Previous: SQL Basics for Python Developers: SELECT, WHERE, and JOINs

Next: SQL Indexes, Query Plans, and Schema Design That Scales

Series Home: Python + AI/ML Tutorial Series