SQL · Theory

LEAD And LAG

Theory

281/439

LEAD And LAG

Window functions compute across related rows without collapsing them. ROW_NUMBER() OVER (ORDER BY marks DESC) ranks students but keeps every row. GROUP BY would squash them.

MySQL 8+: ROW_NUMBER, RANK, DENSE_RANK, SUM() OVER (PARTITION BY city). PARTITION BY is ‘GROUP BY that doesn’t hide rows’.

Trap — window functions on 5.7. Trap: using GROUP BY when you still needed each student row plus a rank.

On the example next to this theory — LEAD And LAG — wHERE combines predicates with AND/OR; LIKE does pattern match.

Exam tip

ROW_NUMBER() OVER (ORDER BY marks DESC) AS rank

LEAD And LAG — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Book', 200), (3, 'Bag', 50);
SELECT * FROM products
WHERE price BETWEEN 20 AND 150 AND name LIKE 'B%';

LEAD And LAG — wHERE combines predicates with AND/OR; LIKE does pattern match.

Short notes

DefOVER () computes a value per row using a window of rows.
RuleROW_NUMBER() OVER (ORDER BY marks DESC). MySQL 8.
TrapGROUP BY when you still need every row.

SQL · Theory

LEAD And LAG

← All stacks

Theory

281/439

LEAD And LAG

Window functions compute across related rows without collapsing them. ROW_NUMBER() OVER (ORDER BY marks DESC) ranks students but keeps every row. GROUP BY would squash them.

MySQL 8+: ROW_NUMBER, RANK, DENSE_RANK, SUM() OVER (PARTITION BY city). PARTITION BY is ‘GROUP BY that doesn’t hide rows’.

Trap — window functions on 5.7. Trap: using GROUP BY when you still needed each student row plus a rank.

On the example next to this theory — LEAD And LAG — wHERE combines predicates with AND/OR; LIKE does pattern match.

Exam tip

ROW_NUMBER() OVER (ORDER BY marks DESC) AS rank

LEAD And LAG — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Book', 200), (3, 'Bag', 50);
SELECT * FROM products
WHERE price BETWEEN 20 AND 150 AND name LIKE 'B%';

LEAD And LAG — wHERE combines predicates with AND/OR; LIKE does pattern match.

Short notes

DefOVER () computes a value per row using a window of rows.
RuleROW_NUMBER() OVER (ORDER BY marks DESC). MySQL 8.
TrapGROUP BY when you still need every row.

LEAD And LAG

LEAD And LAG — sample query

Short notes

Questions

LEAD And LAG

LEAD And LAG — sample query

Short notes

Questions