SQLite Window Functions

Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single summary row the way GROUP BY does. Each row in the result keeps its own identity while also seeing aggregated data from surrounding rows.

Window Function vs GROUP BY

TABLE: sales (6 rows)
rep   │ region │ amount
──────┼────────┼───────
Alice │ North  │ 1200
Bob   │ South  │  800
Alice │ North  │  500
Carol │ East   │ 1100
Bob   │ South  │ 1300
Alice │ West   │  900

GROUP BY rep → collapses to 3 rows:
  rep   │ total
  Alice │ 2600
  Bob   │ 2100
  Carol │ 1100

WINDOW FUNCTION → keeps all 6 rows, adds total column:
  rep   │ amount │ rep_total
  Alice │ 1200   │ 2600
  Bob   │  800   │ 2100
  Alice │  500   │ 2600
  Carol │ 1100   │ 1100
  Bob   │ 1300   │ 2100
  Alice │  900   │ 2600

Window Function Syntax

function() OVER (
  PARTITION BY column   -- divide rows into groups
  ORDER BY column       -- define row order within partition
  ROWS/RANGE BETWEEN ... AND ...  -- optional frame
)

ROW_NUMBER() — Sequential Row Numbers

SELECT rep, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS overall_rank
FROM sales;

rep   │ amount │ overall_rank
──────┼────────┼─────────────
Bob   │ 1300   │ 1
Alice │ 1200   │ 2
Carol │ 1100   │ 3
Alice │  900   │ 4
Bob   │  800   │ 5
Alice │  500   │ 6

RANK() and DENSE_RANK()

SELECT rep, amount,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM sales;

rep   │ amount │ rnk │ dense_rnk
──────┼────────┼─────┼──────────
Bob   │ 1300   │ 1   │ 1
Alice │ 1200   │ 2   │ 2
Carol │ 1100   │ 3   │ 3
Alice │  900   │ 4   │ 4
Bob   │  800   │ 5   │ 5
Alice │  500   │ 6   │ 6

RANK vs DENSE_RANK:
  If two rows tie for rank 2, RANK gives 2,2,4  (skips 3)
  DENSE_RANK gives               2,2,3  (no skip)

PARTITION BY — Rank Within Groups

-- Rank each sale within each rep's own sales
SELECT rep, amount,
       RANK() OVER (PARTITION BY rep ORDER BY amount DESC) AS rep_rank
FROM sales;

rep   │ amount │ rep_rank
──────┼────────┼──────────
Alice │ 1200   │ 1        ← Alice's best sale
Alice │  900   │ 2
Alice │  500   │ 3        ← Alice's weakest sale
Bob   │ 1300   │ 1        ← Bob's best sale
Bob   │  800   │ 2
Carol │ 1100   │ 1        ← Carol's only sale

SUM() OVER — Running Total

SELECT rep, amount,
       SUM(amount) OVER (
         ORDER BY rowid
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales;

rep   │ amount │ running_total
──────┼────────┼──────────────
Alice │ 1200   │ 1200
Bob   │  800   │ 2000
Alice │  500   │ 2500
Carol │ 1100   │ 3600
Bob   │ 1300   │ 4900
Alice │  900   │ 5800

AVG() OVER — Moving Average

-- 3-row moving average of sales amount
SELECT rep, amount,
       ROUND(AVG(amount) OVER (
         ORDER BY rowid
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_3
FROM sales;

rep   │ amount │ moving_avg_3
──────┼────────┼─────────────
Alice │ 1200   │ 1200.00   ← only 1 row
Bob   │  800   │ 1000.00   ← avg of 1200, 800
Alice │  500   │  833.33   ← avg of 1200, 800, 500
Carol │ 1100   │  800.00   ← avg of 800, 500, 1100
Bob   │ 1300   │  966.67   ← avg of 500, 1100, 1300
Alice │  900   │ 1100.00   ← avg of 1100, 1300, 900

LAG() and LEAD() — Look at Adjacent Rows

SELECT rep, amount,
       LAG(amount,  1, 0) OVER (ORDER BY rowid) AS prev_sale,
       LEAD(amount, 1, 0) OVER (ORDER BY rowid) AS next_sale
FROM sales;

rep   │ amount │ prev_sale │ next_sale
──────┼────────┼───────────┼──────────
Alice │ 1200   │ 0         │  800    ← next row's amount
Bob   │  800   │ 1200      │  500
Alice │  500   │  800      │ 1100
Carol │ 1100   │  500      │ 1300
Bob   │ 1300   │ 1100      │  900
Alice │  900   │ 1300      │  0      ← last row

NTILE() — Divide Into Buckets

-- Split sales into 3 performance tiers
SELECT rep, amount,
       NTILE(3) OVER (ORDER BY amount DESC) AS tier
FROM sales;

rep   │ amount │ tier
──────┼────────┼──────
Bob   │ 1300   │ 1    ← top tier
Alice │ 1200   │ 1
Carol │ 1100   │ 2    ← mid tier
Alice │  900   │ 2
Bob   │  800   │ 3    ← bottom tier
Alice │  500   │ 3

Key Takeaways

• Window functions compute values across related rows without collapsing them
• PARTITION BY divides rows into independent groups for the calculation
• ROW_NUMBER(), RANK(), DENSE_RANK() assign sequential numbers or ranks
• SUM() OVER (ORDER BY ... ROWS BETWEEN ...) computes running totals
• LAG() and LEAD() access the previous or next row's value