S039 · Window Functions — the Game-Changer

SELECT name, dept, salary,
       RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn_in_dept,
       AVG(salary)  OVER (PARTITION BY dept)                       AS dept_avg
FROM employees;

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM orders;

SELECT order_date, amount,
       LAG(amount, 1)  OVER (ORDER BY order_date) AS prev_amount,
       LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount,
       amount - LAG(amount, 1) OVER (ORDER BY order_date) AS day_over_day_change
FROM orders;

"""windows_lab.py — every window-function pattern you'll ever need."""
from __future__ import annotations
import sqlite3
 
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    dept TEXT NOT NULL,
    salary INTEGER,
    hired TEXT
);
INSERT INTO employees VALUES
    (1,'Alice','Eng',   120000,'2020-03-01'),
    (2,'Bob',  'Eng',    95000,'2021-06-15'),
    (3,'Carol','HR',     80000,'2019-01-10'),
    (4,'Dan',  'Eng',   140000,'2018-09-20'),
    (5,'Eve',  'Sales', 110000,'2022-02-01'),
    (6,'Frank','Sales',  60000,'2023-11-05'),
    (7,'Heidi','Eng',    95000,'2024-01-01');   -- ties with Bob
 
CREATE TABLE sales (
    day TEXT NOT NULL,
    region TEXT NOT NULL,
    amount INTEGER NOT NULL
);
INSERT INTO sales VALUES
    ('2026-01-01','South',100),('2026-01-01','North',200),
    ('2026-01-02','South',150),('2026-01-02','North',180),
    ('2026-01-03','South',300),('2026-01-03','North',210),
    ('2026-01-04','South',120),('2026-01-04','North',260),
    ('2026-01-05','South',400),('2026-01-05','North',300),
    ('2026-01-06','South',350),('2026-01-06','North',270),
    ('2026-01-07','South',450),('2026-01-07','North',330);
 
CREATE TABLE events (
    user_id INTEGER NOT NULL,
    event_time INTEGER NOT NULL       -- unix seconds
);
INSERT INTO events VALUES
    (1, 1000), (1, 1300), (1, 1500),          -- one session
    (1, 5000), (1, 5100),                     -- new session (gap > 30min)
    (2, 2000), (2, 2200), (2, 8000);          -- two sessions
""")
 
def run(label: str, sql: str) -> None:
    print(f"\n-- {label} --")
    for row in cur.execute(sql):
        print(" ", row)
 
# ---- 1. Three windows in one query ----
run("1. Rank, row_number, dept_avg — three windows, one pass", """
    SELECT name, dept, salary,
           RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS rn,
           AVG(salary)  OVER (PARTITION BY dept)                        AS dept_avg
    FROM employees
    ORDER BY dept, rk, id;
""")
 
# ---- 2. Top-2 per group — the killer pattern ----
run("2. Top 2 earners per dept (wrap in subquery, filter on rn)", """
    SELECT name, dept, salary FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS rn
        FROM employees
    ) t
    WHERE rn <= 2
    ORDER BY dept, salary DESC;
""")
 
# ---- 3. RANK vs DENSE_RANK on ties (Bob + Heidi tied at 95000) ----
run("3. RANK vs DENSE_RANK on tied salaries", """
    SELECT name, salary,
           RANK()       OVER (ORDER BY salary DESC) AS rk,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS drk
    FROM employees
    ORDER BY salary DESC, name;
""")
 
# ---- 4. Running total + 7-day moving average per region ----
run("4. Running total & 3-day moving average per region", """
    SELECT day, region, amount,
           SUM(amount) OVER (PARTITION BY region ORDER BY day
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running,
           AVG(amount) OVER (PARTITION BY region ORDER BY day
                             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma_3d
    FROM sales
    ORDER BY region, day;
""")
 
# ---- 5. LAG — day-over-day change per region ----
run("5. LAG — day-over-day delta", """
    SELECT day, region, amount,
           LAG(amount) OVER (PARTITION BY region ORDER BY day) AS prev_amt,
           amount - LAG(amount) OVER (PARTITION BY region ORDER BY day) AS delta
    FROM sales
    ORDER BY region, day;
""")
 
# ---- 6. NTILE — quartiles by salary ----
run("6. NTILE(4) — instant quartile buckets", """
    SELECT name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile
    FROM employees
    ORDER BY salary DESC;
""")
 
# ---- 7. FIRST_VALUE — attach the dept's top earner to every row ----
run("7. FIRST_VALUE — top earner column beside every row", """
    SELECT name, dept, salary,
           FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS top_earner
    FROM employees
    ORDER BY dept, salary DESC;
""")
 
# ---- 8. Sessionisation — 30-min gap rule ----
run("8. Sessionise events by 30-minute gap (1800 sec)", """
    WITH gaps AS (
        SELECT user_id, event_time,
               event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS gap
        FROM events
    ),
    flagged AS (
        SELECT user_id, event_time,
               CASE WHEN gap IS NULL OR gap > 1800 THEN 1 ELSE 0 END AS is_new_session
        FROM gaps
    )
    SELECT user_id, event_time,
           SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time
                                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
    FROM flagged
    ORDER BY user_id, event_time;
""")
 
# ---- 9. Deduplication — keep the earliest hire per (dept, salary) ----
run("9. Deduplicate: keep earliest row per (dept, salary)", """
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY dept, salary ORDER BY hired) AS rn
        FROM employees
    ) WHERE rn = 1
    ORDER BY dept, salary DESC;
""")
 
# ---- 10. TRAP: filtering on window column in same SELECT ----
try:
    cur.execute("""
        SELECT name, RANK() OVER (ORDER BY salary DESC) AS rk
        FROM employees WHERE rk <= 3;
    """).fetchall()
except sqlite3.OperationalError as e:
    print("\n-- 10. TRAP: filter on window column in same SELECT --")
    print(" Expected error:", e)
 
conn.close()