These exercises use window functions, CTEs and a few classic patterns. They use the employees table, plus small tables of their own where needed.
query.sql
SQL
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
manager_id INT NULL,
hired_on DATE NOT NULL
);
INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
('Asha Rao', 'management', 150000.00, NULL, '2020-01-15'),
('Ben Carter', 'engineering', 120000.00, 1, '2021-03-01'),
('Chitra Nair', 'engineering', 95000.00, 2, '2022-07-11'),
('Dev Malhotra', 'engineering', 88000.00, 2, '2023-01-09'),
('Esha Gupta', 'sales', 70000.00, 1, '2021-09-20'),
('Farid Khan', 'sales', 64000.00, 5, '2022-11-02'),
('Gita Menon', 'sales', 64000.00, 5, '2024-02-14'),
('Hari Iyer', 'support', 52000.00, 1, '2023-05-30');
Output
OUTPUT
Query OK, 0 rows affected
Query OK, 8 rows affected
Records: 8 Duplicates: 0 Warnings: 0Exercise 1: rank within the department
Rank the employees by salary inside each department, highest first. Show ROW_NUMBER, RANK and DENSE_RANK side by side, and watch what happens with the two salaries of 64000 in sales.
Show a possible solution
query.sql
SQL
SELECT department, name, salary,
ROW_NUMBER() OVER w AS row_no,
RANK() OVER w AS rank_no,
DENSE_RANK() OVER w AS dense_no
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC)
ORDER BY department, salary DESC, name;
Output
OUTPUT
+-------------+--------------+-----------+--------+---------+----------+
| department | name | salary | row_no | rank_no | dense_no |
+-------------+--------------+-----------+--------+---------+----------+
| engineering | Ben Carter | 120000.00 | 1 | 1 | 1 |
| engineering | Chitra Nair | 95000.00 | 2 | 2 | 2 |
| engineering | Dev Malhotra | 88000.00 | 3 | 3 | 3 |
| management | Asha Rao | 150000.00 | 1 | 1 | 1 |
| sales | Esha Gupta | 70000.00 | 1 | 1 | 1 |
| sales | Farid Khan | 64000.00 | 2 | 2 | 2 |
| sales | Gita Menon | 64000.00 | 3 | 2 | 2 |
| support | Hari Iyer | 52000.00 | 1 | 1 | 1 |
+-------------+--------------+-----------+--------+---------+----------+
8 rows in setROW_NUMBER gives different numbers to ties (arbitrary between them), RANK gives ties the same number and skips the next, DENSE_RANK gives ties the same number and does not skip (Rank Functions).
Exercise 2: top two per department
Show the two highest paid employees of each department.
Show a possible solution
query.sql
SQL
WITH ranked AS (
SELECT department, name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name) AS position_in_department
FROM employees
)
SELECT department, name, salary FROM ranked WHERE position_in_department <= 2 ORDER BY department, position_in_department;
Output
OUTPUT
+-------------+-------------+-----------+
| department | name | salary |
+-------------+-------------+-----------+
| engineering | Ben Carter | 120000.00 |
| engineering | Chitra Nair | 95000.00 |
| management | Asha Rao | 150000.00 |
| sales | Esha Gupta | 70000.00 |
| sales | Farid Khan | 64000.00 |
| support | Hari Iyer | 52000.00 |
+-------------+-------------+-----------+
6 rows in setA window function cannot appear in WHERE, so the ranking is computed in a CTE and filtered outside (Top N per Group).
Exercise 3: above the department average
List the employees who earn more than the average salary of their own department.
Show a possible solution
query.sql
SQL
SELECT name, department, salary, ROUND(avg_salary) AS department_average
FROM (SELECT name, department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary FROM employees) x
WHERE salary > avg_salary
ORDER BY department, salary DESC;
Output
OUTPUT
+------------+-------------+-----------+--------------------+
| name | department | salary | department_average |
+------------+-------------+-----------+--------------------+
| Ben Carter | engineering | 120000.00 | 101000 |
| Esha Gupta | sales | 70000.00 | 66000 |
+------------+-------------+-----------+--------------------+
2 rows in setExercise 4: running total
Show the employees in order of hiring date, with a running total of salaries.
Show a possible solution
query.sql
SQL
SELECT hired_on, name, salary, SUM(salary) OVER (ORDER BY hired_on, id) AS running_payroll FROM employees ORDER BY hired_on, id;
Output
OUTPUT
+------------+--------------+-----------+-----------------+
| hired_on | name | salary | running_payroll |
+------------+--------------+-----------+-----------------+
| 2020-01-15 | Asha Rao | 150000.00 | 150000.00 |
| 2021-03-01 | Ben Carter | 120000.00 | 270000.00 |
| 2021-09-20 | Esha Gupta | 70000.00 | 340000.00 |
| 2022-07-11 | Chitra Nair | 95000.00 | 435000.00 |
| 2022-11-02 | Farid Khan | 64000.00 | 499000.00 |
| 2023-01-09 | Dev Malhotra | 88000.00 | 587000.00 |
| 2023-05-30 | Hari Iyer | 52000.00 | 639000.00 |
| 2024-02-14 | Gita Menon | 64000.00 | 703000.00 |
+------------+--------------+-----------+-----------------+
8 rows in setExercise 5: compared with the previous hire
Within each department, show each employee’s salary and the difference from the salary of the previous hire in the same department (NULL for the first).
Show a possible solution
query.sql
SQL
SELECT department, name, hired_on, salary,
salary - LAG(salary) OVER (PARTITION BY department ORDER BY hired_on) AS change_from_previous_hire
FROM employees
ORDER BY department, hired_on;
Output
OUTPUT
+-------------+--------------+------------+-----------+---------------------------+
| department | name | hired_on | salary | change_from_previous_hire |
+-------------+--------------+------------+-----------+---------------------------+
| engineering | Ben Carter | 2021-03-01 | 120000.00 | NULL |
| engineering | Chitra Nair | 2022-07-11 | 95000.00 | -25000.00 |
| engineering | Dev Malhotra | 2023-01-09 | 88000.00 | -7000.00 |
| management | Asha Rao | 2020-01-15 | 150000.00 | NULL |
| sales | Esha Gupta | 2021-09-20 | 70000.00 | NULL |
| sales | Farid Khan | 2022-11-02 | 64000.00 | -6000.00 |
| sales | Gita Menon | 2024-02-14 | 64000.00 | 0.00 |
| support | Hari Iyer | 2023-05-30 | 52000.00 | NULL |
+-------------+--------------+------------+-----------+---------------------------+
8 rows in setExercise 6: the second highest salary
Find the second highest distinct salary in the company. (If two people earn the highest, the second highest is still the next lower value.)
Show a possible solution
query.sql
SQL
SELECT DISTINCT salary AS second_highest
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) x
WHERE r = 2;
Output
OUTPUT
+----------------+
| second_highest |
+----------------+
| 120000.00 |
+----------------+
1 row in setChange r = 2 to r = 3 for the third highest, and so on. Without window functions: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1.
Exercise 7: the org chart
Print the management tree, with each person indented below their manager, using a recursive CTE.
Show a possible solution
query.sql
SQL
WITH RECURSIVE chart AS (
SELECT id, name, 0 AS level, CAST(LPAD(id, 3, '0') AS CHAR(100)) AS sort_key FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, c.level + 1, CONCAT(c.sort_key, '.', LPAD(e.id, 3, '0')) FROM employees e JOIN chart c ON e.manager_id = c.id
)
SELECT CONCAT(REPEAT(' ', level), name) AS employee FROM chart ORDER BY sort_key;
Output
OUTPUT
+------------------+
| employee |
+------------------+
| Asha Rao |
| Ben Carter |
| Chitra Nair |
| Dev Malhotra |
| Esha Gupta |
| Farid Khan |
| Gita Menon |
| Hari Iyer |
+------------------+
8 rows in setExercise 8: share of the department payroll
Show each employee’s salary as a percentage of the total salary of their department (one decimal).
Show a possible solution
query.sql
SQL
SELECT department, name, salary, ROUND(salary / SUM(salary) OVER (PARTITION BY department) * 100, 1) AS pct_of_department
FROM employees
ORDER BY department, salary DESC, name;
Output
OUTPUT
+-------------+--------------+-----------+-------------------+
| department | name | salary | pct_of_department |
+-------------+--------------+-----------+-------------------+
| engineering | Ben Carter | 120000.00 | 39.6 |
| engineering | Chitra Nair | 95000.00 | 31.4 |
| engineering | Dev Malhotra | 88000.00 | 29.0 |
| management | Asha Rao | 150000.00 | 100.0 |
| sales | Esha Gupta | 70000.00 | 35.4 |
| sales | Farid Khan | 64000.00 | 32.3 |
| sales | Gita Menon | 64000.00 | 32.3 |
| support | Hari Iyer | 52000.00 | 100.0 |
+-------------+--------------+-----------+-------------------+
8 rows in setExercise 9: find and remove duplicates
The table below has duplicate emails. First list the duplicated emails, then delete the extra rows, keeping the one with the lowest id.
query.sql
SQL
CREATE TABLE subscribers (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(50) NOT NULL);
INSERT INTO subscribers (email) VALUES ('a@example.com'), ('b@example.com'), ('a@example.com'), ('c@example.com'), ('b@example.com'), ('a@example.com');
Output
OUTPUT
Query OK, 0 rows affected
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0Show a possible solution
query.sql
SQL
SELECT email, COUNT(*) AS copies FROM subscribers GROUP BY email HAVING COUNT(*) > 1 ORDER BY email;
DELETE s FROM subscribers s
JOIN (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS copy_no FROM subscribers) d ON d.id = s.id
WHERE d.copy_no > 1;
SELECT id, email FROM subscribers ORDER BY id;
Output
OUTPUT
+---------------+--------+
| email | copies |
+---------------+--------+
| a@example.com | 3 |
| b@example.com | 2 |
+---------------+--------+
2 rows in set
+----+---------------+
| id | email |
+----+---------------+
| 1 | a@example.com |
| 2 | b@example.com |
| 4 | c@example.com |
+----+---------------+
3 rows in setMySQL does not let you delete directly from a CTE, so the DELETE joins to a derived table that numbers the copies. To prevent the problem, add UNIQUE (email) (UNIQUE).
Exercise 10: consecutive days
A table records the days on which users logged in. Find, for each user, the longest streak of consecutive days.
query.sql
SQL
CREATE TABLE logins (user_id INT NOT NULL, day DATE NOT NULL, PRIMARY KEY (user_id, day));
INSERT INTO logins VALUES
(1, '2026-03-01'), (1, '2026-03-02'), (1, '2026-03-03'), (1, '2026-03-05'), (1, '2026-03-06'),
(2, '2026-03-01'), (2, '2026-03-03'), (2, '2026-03-04'), (2, '2026-03-05'), (2, '2026-03-06');
Output
OUTPUT
Query OK, 0 rows affected
Query OK, 10 rows affected
Records: 10 Duplicates: 0 Warnings: 0Show a possible solution
query.sql
SQL
WITH numbered AS (
SELECT user_id, day, DATE_SUB(day, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) DAY) AS grp FROM logins
),
streaks AS (
SELECT user_id, grp, COUNT(*) AS days, MIN(day) AS first_day, MAX(day) AS last_day FROM numbered GROUP BY user_id, grp
)
SELECT user_id, days AS longest_streak, first_day, last_day
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY days DESC, first_day) AS r FROM streaks) x
WHERE r = 1
ORDER BY user_id;
Output
OUTPUT
+---------+----------------+------------+------------+
| user_id | longest_streak | first_day | last_day |
+---------+----------------+------------+------------+
| 1 | 3 | 2026-03-01 | 2026-03-03 |
| 2 | 4 | 2026-03-03 | 2026-03-06 |
+---------+----------------+------------+------------+
2 rows in setThe trick of gaps and islands: subtracting the row number (in days) from a date gives the same value for consecutive days, so the groups are the streaks (Gaps and Islands).
Exercise 11: the median
Find the median salary of each department (for an even count, the average of the two middle values).
Show a possible solution
query.sql
SQL
SELECT department, ROUND(AVG(salary), 2) AS median_salary
FROM (
SELECT department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) AS rn,
COUNT(*) OVER (PARTITION BY department) AS cnt
FROM employees
) x
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
GROUP BY department
ORDER BY department;
Output
OUTPUT
+-------------+---------------+
| department | median_salary |
+-------------+---------------+
| engineering | 95000.00 |
| management | 150000.00 |
| sales | 64000.00 |
| support | 52000.00 |
+-------------+---------------+
4 rows in setMySQL has no built-in MEDIAN() function. For an odd count, both numbers in the IN list are the same middle row. For an even count, they are the two middle rows, and AVG combines them.
lightbulb
Window functions do not reduce rows
GROUP BY collapses rows. A window function keeps every row and adds a calculation next to it. If you need both the detail and a group value, you need a window function (Window Functions).