SQL · Theory

Hackerrank MySQL

Theory

400/439

Hackerrank MySQL

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.

On the example next to this theory: Ranking Functions: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

On the example next to this theory: Hackerrank MySQL: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

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

Hackerrank MySQL — sample query

-- Hackerrank MySQL
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Hackerrank MySQL: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

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

Hackerrank MySQL

← All stacks

Theory

400/439

Hackerrank MySQL

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.

On the example next to this theory: Ranking Functions: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

On the example next to this theory: Hackerrank MySQL: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

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

Hackerrank MySQL — sample query

-- Hackerrank MySQL
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Hackerrank MySQL: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

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.

Hackerrank MySQL

Hackerrank MySQL — sample query

Short notes

Questions

Hackerrank MySQL

Hackerrank MySQL — sample query

Short notes

Questions