T-SQL ranking and offset window functions

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab at compatibility level 170. Client: sqlcmd.
-- The four ranking functions on the units of ranking_model.py: two cities, each a partition, with a tie in each.
-- product is the tie-breaker, so ROW_NUMBER gives the same numbers on every run.
SET NOCOUNT ON;
CREATE TABLE #units (city nvarchar(10) NOT NULL, product varchar(12) NOT NULL, units int NOT NULL,
                     PRIMARY KEY (city, product));
INSERT INTO #units VALUES
  (N'Pune', 'TEA-250', 90), (N'Pune', 'RICE-5K', 20), (N'Pune', 'GHEE-500', 20), (N'Pune', 'SALT-1K', 10),
  (N'Kochi', 'RICE-5K', 70), (N'Kochi', 'TEA-250', 70), (N'Kochi', 'GHEE-500', 40);

SELECT city, product, units,
       ROW_NUMBER() OVER (PARTITION BY city ORDER BY units DESC, product) AS row_number_,   -- Pune 1, 2, 3, 4
       RANK()       OVER (PARTITION BY city ORDER BY units DESC)          AS rank_,         -- Pune 1, 2, 2, 4
       DENSE_RANK() OVER (PARTITION BY city ORDER BY units DESC)          AS dense_rank_,   -- Pune 1, 2, 2, 3
       NTILE(2)     OVER (PARTITION BY city ORDER BY units DESC, product) AS half           -- Pune 1, 1, 2, 2
FROM #units
ORDER BY city, units DESC, product;
DROP TABLE #units;
GO

"""ranking_ties.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("ranking/ranking_ties.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i ranking/ranking_ties.sql
city  product  units row_number_ rank_ dense_rank_ half
----- -------- ----- ----------- ----- ----------- ----
Kochi RICE-5K     70           1     1           1    1
Kochi TEA-250     70           2     1           1    1
Kochi GHEE-500    40           3     3           2    2
Pune  TEA-250     90           1     1           1    1
Pune  GHEE-500    20           2     2           2    1
Pune  RICE-5K     20           3     2           2    2
Pune  SALT-1K     10           4     4           3    2

"""ROW_NUMBER, RANK, DENSE_RANK and NTILE(2) on the same units.

The rules are those of Microsoft's ranking function pages: ROW_NUMBER numbers
every row, RANK gives tied rows the same number and skips the next ones,
DENSE_RANK gives tied rows the same number without gaps, and NTILE(n) cuts a
partition into n groups whose sizes differ by at most one, larger groups
first. Rows are ordered by units, highest first, with the product code as
the tie-breaker. Change RICE-5K's 20 units in Pune to 90 and run it again.
"""

UNITS = [  # (city, product, units sold this month)
    ("Pune", "TEA-250", 90),
    ("Pune", "RICE-5K", 20),
    ("Pune", "GHEE-500", 20),
    ("Pune", "SALT-1K", 10),
    ("Kochi", "RICE-5K", 70),
    ("Kochi", "TEA-250", 70),
    ("Kochi", "GHEE-500", 40),
]


def ntile(n_rows, groups):
    """Group number of each row: sizes differ by one at most, larger groups first."""
    base, extra = divmod(n_rows, groups)
    numbers = []
    for g in range(groups):
        numbers += [g + 1] * (base + (1 if g < extra else 0))
    return numbers


print("city   product   units  ROW_NUMBER  RANK  DENSE_RANK  NTILE(2)")
for city in sorted({c for c, _, _ in UNITS}):
    rows = sorted((r for r in UNITS if r[0] == city), key=lambda r: (-r[2], r[1]))
    tiles = ntile(len(rows), 2)
    rank = dense = 0
    previous = None
    for n, (_, product, units) in enumerate(rows, start=1):
        if units != previous:
            rank, dense, previous = n, dense + 1, units
        print(f"{city:<6} {product:<9} {units:>5}  {n:>10}  {rank:>4}  {dense:>10}  {tiles[n - 1]:>8}")

cities = len({c for c, _, _ in UNITS})
summary = f"{len(UNITS)} rows in {cities} partitions; numbering starts again at 1 in each city."
print(summary)

city   product   units  ROW_NUMBER  RANK  DENSE_RANK  NTILE(2)
Kochi  RICE-5K      70           1     1           1         1
Kochi  TEA-250      70           2     1           1         1
Kochi  GHEE-500     40           3     3           2         2
Pune   TEA-250      90           1     1           1         1
Pune   GHEE-500     20           2     2           2         1
Pune   RICE-5K      20           3     2           2         2
Pune   SALT-1K      10           4     4           3         2
7 rows in 2 partitions; numbering starts again at 1 in each city.

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab at compatibility level 170. Client: sqlcmd.
-- LAG and LEAD over each city's monthly units of TEA-250, as lag_model.py computes them. The default 0 of
-- LAG(units, 1, 0) fills the first month of each partition; without it the first month gets NULL.
SET NOCOUNT ON;
CREATE TABLE #monthly (city nvarchar(10) NOT NULL, month_no int NOT NULL, units int NOT NULL,
                       PRIMARY KEY (city, month_no));
INSERT INTO #monthly VALUES (N'Pune', 1, 60), (N'Pune', 2, 75), (N'Pune', 3, 75), (N'Pune', 4, 90),
                            (N'Kochi', 1, 50), (N'Kochi', 2, 50), (N'Kochi', 3, 65);

SELECT city, month_no, units,
       LAG(units, 1, 0) OVER (PARTITION BY city ORDER BY month_no)         AS previous_or_0,  -- Pune 0, 60, 75, 75
       units - LAG(units) OVER (PARTITION BY city ORDER BY month_no)       AS change,         -- Pune NULL, 15, 0, 15
       LEAD(units)      OVER (PARTITION BY city ORDER BY month_no)         AS next_month,     -- Pune 75, 75, 90, NULL
       LAG(units, 2)    OVER (PARTITION BY city ORDER BY month_no)         AS two_back        -- Pune NULL, NULL, 60, 75
FROM #monthly
ORDER BY city DESC, month_no;
DROP TABLE #monthly;
GO

"""lag_lead.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("offset/lag_lead.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i offset/lag_lead.sql
city  month_no units previous_or_0 change next_month two_back
----- -------- ----- ------------- ------ ---------- --------
Pune         1    60             0   NULL         75     NULL
Pune         2    75            60     15         75     NULL
Pune         3    75            75      0         90       60
Pune         4    90            75     15       NULL       75
Kochi        1    50             0   NULL         50     NULL
Kochi        2    50            50      0         65     NULL
Kochi        3    65            50     15       NULL       50

"""LAG and LEAD over each city's monthly sales of TEA-250.

LAG(units, offset, default) reads the row offset rows before the current one
in the partition and LEAD the row after it; past the edge of the partition
they return the default, NULL unless one is given. The rules are those of
Microsoft's LAG and LEAD pages. Change the default of LAG from 0 to None and
run it again.
"""

SALES = {  # city -> units of TEA-250 in months 1, 2, 3 and 4
    "Pune": [60, 75, 75, 90],
    "Kochi": [50, 50, 65],
}
DEFAULT = 0  # the third argument of LAG(units, 1, 0)


def lag(values, i, offset=1, default=None):
    return values[i - offset] if i - offset >= 0 else default


def lead(values, i, offset=1, default=None):
    return values[i + offset] if i + offset < len(values) else default


def show(value):
    return "NULL" if value is None else str(value)


print("city   month  units  LAG(1, default)  change  LEAD  LAG(2)")
for city, units in SALES.items():
    for i, u in enumerate(units):
        before = lag(units, i, 1, DEFAULT)
        change = None if before is None else u - before
        print(f"{city:<6} {i + 1:>5}  {u:>5}  {show(before):>15}  {show(change):>6}  {show(lead(units, i)):>4}  {show(lag(units, i, 2)):>6}")

first_rows = len(SALES)
note = f"{first_rows} first months get the default as their previous value."
print(note)

city   month  units  LAG(1, default)  change  LEAD  LAG(2)
Pune       1     60                0      60    75    NULL
Pune       2     75               60      15    75    NULL
Pune       3     75               75       0    90      60
Pune       4     90               75      15  NULL      75
Kochi      1     50                0      50    50    NULL
Kochi      2     50               50       0    65    NULL
Kochi      3     65               50      15  NULL      50
2 first months get the default as their previous value.

"""Top 2 products per city with three numbering functions.

The query numbers each city's products by units, highest first, in a CTE and
keeps the rows numbered 1 or 2 in the outer query. ROW_NUMBER returns exactly
two rows a city and drops one of two tied products by its tie-breaker; RANK and
DENSE_RANK keep ties, and DENSE_RANK can return more rows still. Set TOP to 1
and run it again.
"""

UNITS = [
    ("Pune", "TEA-250", 90), ("Pune", "RICE-5K", 20), ("Pune", "GHEE-500", 20), ("Pune", "SALT-1K", 10),
    ("Kochi", "RICE-5K", 70), ("Kochi", "TEA-250", 70), ("Kochi", "GHEE-500", 40),
]
TOP = 2


def numbered(rows, kind):
    out, rank, dense, previous = [], 0, 0, None
    for n, (product, units) in enumerate(rows, start=1):
        if units != previous:
            rank, dense, previous = n, dense + 1, units
        out.append((product, units, {"ROW_NUMBER": n, "RANK": rank, "DENSE_RANK": dense}[kind]))
    return out


for kind in ("ROW_NUMBER", "RANK", "DENSE_RANK"):
    print(f"{kind} <= {TOP}")
    for city in sorted({c for c, _, _ in UNITS}):
        rows = sorted(((p, u) for c, p, u in UNITS if c == city), key=lambda r: (-r[1], r[0]))
        kept = [f"{p} ({u})" for p, u, n in numbered(rows, kind) if n <= TOP]
        print(f"  {city:<6} {len(kept)} row{'' if len(kept) == 1 else 's'}: {', '.join(kept)}")

ROW_NUMBER <= 2
  Kochi  2 rows: RICE-5K (70), TEA-250 (70)
  Pune   2 rows: TEA-250 (90), GHEE-500 (20)
RANK <= 2
  Kochi  2 rows: RICE-5K (70), TEA-250 (70)
  Pune   3 rows: TEA-250 (90), GHEE-500 (20), RICE-5K (20)
DENSE_RANK <= 2
  Kochi  3 rows: RICE-5K (70), TEA-250 (70), GHEE-500 (40)
  Pune   3 rows: TEA-250 (90), GHEE-500 (20), RICE-5K (20)

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab at compatibility level 170. Client: sqlcmd.
-- Top 2 products per city: the window function is computed in a CTE and filtered in the outer query, because a
-- WHERE cannot read it. ROW_NUMBER keeps exactly two rows a city; RANK keeps Pune's tie at second place.
SET NOCOUNT ON;
CREATE TABLE #units (city nvarchar(10) NOT NULL, product varchar(12) NOT NULL, units int NOT NULL,
                     PRIMARY KEY (city, product));
INSERT INTO #units VALUES
  (N'Pune', 'TEA-250', 90), (N'Pune', 'RICE-5K', 20), (N'Pune', 'GHEE-500', 20), (N'Pune', 'SALT-1K', 10),
  (N'Kochi', 'RICE-5K', 70), (N'Kochi', 'TEA-250', 70), (N'Kochi', 'GHEE-500', 40);

WITH ranked AS (
  SELECT city, product, units,
         ROW_NUMBER() OVER (PARTITION BY city ORDER BY units DESC, product) AS n,
         RANK()       OVER (PARTITION BY city ORDER BY units DESC)          AS place
  FROM #units
)
SELECT city, product, units, n, place
FROM ranked
WHERE place <= 2                                -- Pune: TEA-250, GHEE-500, RICE-5K; Kochi: RICE-5K, TEA-250
ORDER BY city, place, product;

-- One city only: TOP (2) WITH TIES keeps the rows that tie with the last one, as RANK <= 2 does.
SELECT TOP (2) WITH TIES product, units FROM #units WHERE city = N'Pune' ORDER BY units DESC;   -- 3 rows
DROP TABLE #units;
GO

"""top_n_per_city.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("topn/top_n_per_city.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i topn/top_n_per_city.sql
city  product  units n place
----- -------- ----- - -----
Kochi RICE-5K     70 1     1
Kochi TEA-250     70 2     1
Pune  TEA-250     90 1     1
Pune  GHEE-500    20 2     2
Pune  RICE-5K     20 3     2
product  units
-------- -----
TEA-250     90
GHEE-500    20
RICE-5K     20

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab at compatibility level 170. Client: sqlcmd.
-- The feed of dedupe_model.py: six status rows for three orders. ROW_NUMBER in a CTE numbers each order's rows
-- newest first, with event_id as the tie-breaker; the DELETE through the CTE removes every row numbered above 1 and
-- OUTPUT shows what it removed.
SET NOCOUNT ON;
CREATE TABLE #order_status (event_id int PRIMARY KEY, order_id int NOT NULL, status nvarchar(10) NOT NULL,
                            updated_at datetime2(0) NOT NULL);
INSERT INTO #order_status VALUES
  (1, 101, N'new', '2026-03-12 09:00'), (2, 101, N'paid', '2026-03-12 09:05'), (3, 101, N'paid', '2026-03-12 09:05'),
  (4, 102, N'new', '2026-03-12 09:01'), (5, 102, N'paid', '2026-03-12 09:30'), (6, 103, N'new', '2026-03-12 09:10');

WITH numbered AS (
  SELECT event_id, order_id, status,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC, event_id DESC) AS n
  FROM #order_status
)
DELETE FROM numbered
OUTPUT deleted.event_id, deleted.order_id, deleted.status                    -- events 1, 2 and 4
WHERE n > 1;

SELECT event_id, order_id, status FROM #order_status ORDER BY order_id;     -- events 3, 5 and 6
DROP TABLE #order_status;
GO

"""latest_status_per_order.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice
schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("dedupe/latest_status_per_order.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i dedupe/latest_status_per_order.sql
event_id order_id status
-------- -------- ------
       2      101 paid
       1      101 new
       4      102 new
event_id order_id status
-------- -------- ------
       3      101 paid
       5      102 paid
       6      103 new

"""Keeping the newest status of each order and deleting the rest.

A feed delivered six status rows for three orders, one of them twice at the
same minute. ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC,
event_id DESC) gives each order's newest row the number 1, with event_id as the
tie-breaker for rows of the same minute; a DELETE through the CTE removes every
row numbered above 1. Remove event_id from the ORDER BY tuple and run it again.
"""

EVENTS = [  # (event_id, order_id, status, updated_at)
    (1, 101, "new", "09:00"),
    (2, 101, "paid", "09:05"),
    (3, 101, "paid", "09:05"),
    (4, 102, "new", "09:01"),
    (5, 102, "paid", "09:30"),
    (6, 103, "new", "09:10"),
]


def order_key(event):
    event_id, _, _, updated_at = event
    return (updated_at, event_id)  # sorted descending below: newest first, then the higher id


kept, deleted = [], []
for order_id in sorted({e[1] for e in EVENTS}):
    rows = sorted((e for e in EVENTS if e[1] == order_id), key=order_key, reverse=True)
    for n, event in enumerate(rows, start=1):
        (kept if n == 1 else deleted).append((event[0], order_id, event[2], event[3], n))

for label, rows in (("kept", kept), ("deleted", deleted)):
    print(f"{label}: {len(rows)} rows")
    for event_id, order_id, status, updated_at, n in rows:
        print(f"  event {event_id}  order {order_id}  {status:<4} {updated_at}  row number {n}")

kept: 3 rows
  event 3  order 101  paid 09:05  row number 1
  event 5  order 102  paid 09:30  row number 1
  event 6  order 103  new  09:10  row number 1
deleted: 3 rows
  event 2  order 101  paid 09:05  row number 2
  event 1  order 101  new  09:00  row number 3
  event 4  order 102  new  09:01  row number 2