SQL Window Functions

SQL Tutorial › Window Functions › Window Functions

SQL Window Functions

AdvancedLesson 45 of 6914 min read9 queries run on MySQL 8.4

A window function calculates a value across a set of related rows (the window) without collapsing them into one row the way GROUP BY does. You keep every employee row and still see the department average, the rank inside the department or the running total. Window functions are the most asked advanced topic in SQL interviews, and MySQL supports them from version 8.0.

In this lesson
  • How OVER() differs from GROUP BY
  • Split rows into windows with PARTITION BY
  • Order a window with ORDER BY for running results
  • Reuse a window with WINDOW and control the frame

GROUP BY vs window function

GROUP BY returns one row per department:

SQL
SELECT dept_id, ROUND(AVG(salary)) AS dept_avgFROM employeesWHERE dept_id IN (1, 2)GROUP BY dept_id;
ResultMySQL 8.4
dept_iddept_avg
178400
269000
2 rows in set

A window function keeps every row and adds the department average to each one:

SQL
SELECT first_name, dept_id, salary,       ROUND(AVG(salary) OVER (PARTITION BY dept_id)) AS dept_avgFROM employeesWHERE dept_id IN (1, 2)ORDER BY dept_id, salary DESC;
ResultMySQL 8.4
first_namedept_idsalarydept_avg
Asha112500078400
Rahul19800078400
Priya17200078400
Karan17200078400
Amit12500078400
Vikram210500069000
Neha25400069000
Arjun24800069000
8 rows in set
How this query works, step by step
  1. OVER (...) turns AVG into a window function.
  2. PARTITION BY dept_id splits the rows into windows, one per department.
  3. For each row, AVG is computed over that row’s window: Engineering rows all get 78,400 and Sales rows 69,000.
  4. No row disappears: 8 rows in, 8 rows out.

Window function syntax

MySQL
function_name(args) OVER (  [PARTITION BY columns]  [ORDER BY columns]  [frame clause])
PartDoes
PARTITION BYdivides rows into independent groups; without it the whole result is one window
ORDER BYorders rows inside each window; needed for ranking, LAG/LEAD and running totals
framewhich rows around the current one to include, e.g. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Sample data

employees · 12 rows (some columns)
emp_idfirst_namedept_idsalaryhire_date
100MeeraNULL2500002015-01-05
101Asha11250002018-04-12
102Rahul1980002019-07-01
103Priya1720002021-01-18
104Karan1720002022-08-29
105Vikram21050002017-11-20
106Neha2540002022-03-14
107Arjun2480002023-06-05
108Kavya3880002020-09-09
109Rohan3450002024-01-22
110Sneha4780002019-02-25
111Amit1250002025-06-02

OVER() with no partition

An empty OVER () makes the whole result one window. Each salary as a share of the total payroll:

MySQL
SELECT first_name, salary,       SUM(salary) OVER () AS total_payroll,       ROUND(100 * salary / SUM(salary) OVER (), 1) AS pct_of_payrollFROM employeesORDER BY salary DESCLIMIT 5;
ResultMySQL 8.4
first_namesalarytotal_payrollpct_of_payroll
Meera250000106000023.6
Asha125000106000011.8
Vikram10500010600009.9
Rahul9800010600009.2
Kavya8800010600008.3
5 rows in set
How this query works, step by step
  1. SUM(salary) OVER () is the sum of all 12 salaries, repeated on every row: 1,060,000.
  2. Dividing each salary by it gives its share.
  3. Window functions are computed before ORDER BY and LIMIT, so the total still covers all 12 rows even though only 5 are shown.

Ranking functions

SQL
SELECT first_name, dept_id, salary,       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,       RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rnkFROM employeesWHERE dept_id = 1;
ResultMySQL 8.4
first_namedept_idsalaryrow_numrnkdense_rnk
Asha1125000111
Rahul198000222
Priya172000333
Karan172000433
Amit125000554
5 rows in set
How this query works, step by step
  1. ORDER BY salary DESC inside OVER ranks from the highest salary.
  2. Priya and Karan tie at 72,000: ROW_NUMBER still gives them 3 and 4, RANK gives both 3 and skips 4, DENSE_RANK gives both 3 without a gap.
  3. Full lesson: ROW_NUMBER, RANK, DENSE_RANK.

ORDER BY in the window: running totals

Adding ORDER BY to an aggregate’s window makes it cumulative:

SQL
SELECT first_name, hire_date, salary,       SUM(salary) OVER (ORDER BY hire_date) AS running_payroll,       COUNT(*)    OVER (ORDER BY hire_date) AS headcountFROM employeesORDER BY hire_date;
ResultMySQL 8.4
first_namehire_datesalaryrunning_payrollheadcount
Meera2015-01-052500002500001
Vikram2017-11-201050003550002
Asha2018-04-121250004800003
Sneha2019-02-25780005580004
Rahul2019-07-01980006560005
Kavya2020-09-09880007440006
Priya2021-01-18720008160007
Neha2022-03-14540008700008
Karan2022-08-29720009420009
Arjun2023-06-054800099000010
Rohan2024-01-2245000103500011
Amit2025-06-0225000106000012
12 rows in set
How this query works, step by step
  1. Rows are ordered by hire_date inside the window.
  2. For each row, SUM adds the salaries of everyone hired up to and including that date.
  3. COUNT(*) the same way gives the headcount over time. More in Running Totals.

Values from other rows: LAG and LEAD

SQL
SELECT first_name, hire_date,       LAG(first_name)  OVER (ORDER BY hire_date) AS hired_before,       LEAD(hire_date)  OVER (ORDER BY hire_date) AS next_hire_dateFROM employeesORDER BY hire_dateLIMIT 6;
ResultMySQL 8.4
first_namehire_datehired_beforenext_hire_date
Meera2015-01-05NULL2017-11-20
Vikram2017-11-20Meera2018-04-12
Asha2018-04-12Vikram2019-02-25
Sneha2019-02-25Asha2019-07-01
Rahul2019-07-01Sneha2020-09-09
Kavya2020-09-09Rahul2021-01-18
6 rows in set

Full lesson: LAG & LEAD.

Named windows

When several functions use the same window, define it once with WINDOW:

SQL
SELECT first_name, dept_id, salary,       MIN(salary) OVER w AS dept_min,       MAX(salary) OVER w AS dept_max,       salary - MIN(salary) OVER w AS above_minFROM employeesWHERE dept_id IS NOT NULLWINDOW w AS (PARTITION BY dept_id)ORDER BY dept_id, salary;
ResultMySQL 8.4
first_namedept_idsalarydept_mindept_maxabove_min
Amit125000250001250000
Priya1720002500012500047000
Karan1720002500012500047000
Rahul1980002500012500073000
Asha112500025000125000100000
Arjun248000480001050000
Neha254000480001050006000
Vikram21050004800010500057000
Rohan34500045000880000
Kavya388000450008800043000
Sneha47800078000780000
11 rows in set

Where window functions are allowed

Window functions run after WHERE, GROUP BY and HAVING, so they can only appear in SELECT and ORDER BY. To filter on one, wrap the query in a CTE or subquery:

MySQL
SELECT first_name, RANK() OVER (ORDER BY salary DESC) AS rnkFROM employeesWHERE rnk <= 3;
ResultMySQL 8.4
ERROR 1054 (42S22): Unknown column 'rnk' in 'where clause'
SQL
WITH ranked AS (  SELECT first_name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk  FROM employees)SELECT * FROM ranked WHERE rnk <= 3;
ResultMySQL 8.4
first_namesalaryrnk
Meera2500001
Asha1250002
Vikram1050003
3 rows in set

Window functions in MySQL

GroupFunctions
RankingROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE
ValueLAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
AggregateSUM, AVG, COUNT, MIN, MAX (and others) with OVER

Exercise: Salary vs department maximum

Show each employee’s first_name, dept_id, salary and the gap to the highest salary in their department, for employees with a department.

Expected resultMySQL 8.4
first_namedept_idsalarygap_to_top
Asha11250000
Rahul19800027000
Priya17200053000
Karan17200053000
Amit125000100000
Vikram21050000
Neha25400051000
Arjun24800057000
Kavya3880000
Rohan34500043000
Sneha4780000
11 rows in set
Hint 1

MAX(salary) OVER (PARTITION BY dept_id) - salary.

Quick quiz

1. What is the main difference between GROUP BY and a window function?

2. What does PARTITION BY dept_id do?

3. Can you use a window function in WHERE?

4. Which MySQL version introduced window functions?

Window functions FAQ

What are window functions in SQL?

Functions that compute a value over a set of rows related to the current row, defined with OVER(), without collapsing the rows into groups.

What is the difference between PARTITION BY and GROUP BY?

GROUP BY returns one row per group. PARTITION BY defines groups for a window function while keeping every row in the result.

Does MySQL support window functions?

Yes, from MySQL 8.0, including ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE and aggregates with OVER.

Why can’t I filter on a window function in WHERE?

Because window functions are evaluated after WHERE. Compute them in a CTE or subquery and filter in the outer query.

MySQL Users and Roles

SQL Tutorial › Security › Users & Roles

MySQL Users and Roles

IntermediateLesson 66 of 6910 min read4 queries run on MySQL 8.4

Every connection to MySQL logs in as an account, and each account has its own privileges. Applications, testers, analysts and administrators should never share the root account. This lesson creates accounts, sets passwords, groups privileges into roles and shows how to inspect and remove accounts.

In this lesson
  • Create accounts with CREATE USER and understand user@host
  • Change passwords, lock and drop accounts
  • Bundle privileges into roles
  • Design accounts with least privilege

Accounts are user@host

A MySQL account is a user name plus the host it connects from: 'app'@'localhost' and 'app'@'%' are two different accounts.

Host partMeaning
'localhost'only from the database server itself
'10.0.5.%'any machine in that network
'%'from anywhere (use with care)

CREATE USER

MySQL
CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt#2026';CREATE USER 'shop_app'@'10.0.5.%' IDENTIFIED BY 'Sh0p#App2026';SELECT user, host, account_locked FROM mysql.userWHERE user IN ('report_reader', 'shop_app');
ResultMySQL 8.4
mysql> CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt
Query OK, 0 rows affected
mysql> CREATE USER 'shop_app'@'10.0.5.%' IDENTIFIED BY 'Sh0p
Query OK, 0 rows affected
mysql> SELECT user, host, account_locked FROM mysql.user
userhostaccount_locked
shop_app10.0.5.%N
report_readerlocalhostN
2 rows in set
How this query works, step by step
  1. Each CREATE USER creates an account with a password. New accounts can log in but have no privileges on any data yet.
  2. mysql.user is the system table that lists accounts.
Watch out

The passwords in this lesson are examples. Use long, unique passwords stored in a secrets manager, and never put real passwords in scripts or source code.

A new account sees only its own grant of USAGE (meaning “no privileges”):

MySQL
CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt#2026';SHOW GRANTS FOR 'report_reader'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'report_reader'@'localhost'
Grants for report_reader@localhost
GRANT USAGE ON *.* TO `report_reader`@`localhost`
1 row in set

Change, lock and drop

MySQL
CREATE USER 'intern'@'localhost' IDENTIFIED BY 'Int3rn#2026';ALTER USER 'intern'@'localhost' IDENTIFIED BY 'N3w#Passw0rd';ALTER USER 'intern'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;ALTER USER 'intern'@'localhost' ACCOUNT LOCK;SELECT user, host, account_locked, password_lifetime FROM mysql.user WHERE user = 'intern';DROP USER 'intern'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'intern'@'localhost' IDENTIFIED BY 'Int3rn
Query OK, 0 rows affected
mysql> ALTER USER 'intern'@'localhost' IDENTIFIED BY 'N3w
Query OK, 0 rows affected
mysql> ALTER USER 'intern'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY
Query OK, 0 rows affected
mysql> ALTER USER 'intern'@'localhost' ACCOUNT LOCK
Query OK, 0 rows affected
mysql> SELECT user, host, account_locked, password_lifetime FROM mysql.user W…
userhostaccount_lockedpassword_lifetime
internlocalhostY90
1 row in set
mysql> DROP USER 'intern'@'localhost'
Query OK, 0 rows affected
How this query works, step by step
  1. ALTER USER ... IDENTIFIED BY changes the password.
  2. PASSWORD EXPIRE INTERVAL 90 DAY forces a change every 90 days.
  3. ACCOUNT LOCK blocks logins without deleting the account (use ACCOUNT UNLOCK to allow them again).
  4. DROP USER removes the account and its privileges.

Roles

A role is a named bundle of privileges. Grant privileges to the role once, then grant the role to people:

MySQL
CREATE ROLE 'analyst', 'developer';GRANT SELECT ON company.* TO 'analyst';GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'developer'; CREATE USER 'priya'@'localhost' IDENTIFIED BY 'Pr1ya#2026';GRANT 'analyst' TO 'priya'@'localhost';SET DEFAULT ROLE 'analyst' TO 'priya'@'localhost'; SHOW GRANTS FOR 'priya'@'localhost' USING 'analyst';
ResultMySQL 8.4
mysql> CREATE ROLE 'analyst', 'developer'
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.* TO 'analyst'
Query OK, 0 rows affected
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'developer'
Query OK, 0 rows affected
mysql> CREATE USER 'priya'@'localhost' IDENTIFIED BY 'Pr1ya
Query OK, 0 rows affected
mysql> GRANT 'analyst' TO 'priya'@'localhost'
Query OK, 0 rows affected
mysql> SET DEFAULT ROLE 'analyst' TO 'priya'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'priya'@'localhost' USING 'analyst'
Grants for priya@localhost
GRANT USAGE ON *.* TO `priya`@`localhost`
GRANT SELECT ON `company`.* TO `priya`@`localhost`
GRANT `analyst`@`%` TO `priya`@`localhost`
3 rows in set
How this query works, step by step
  1. CREATE ROLE creates two roles; they are locked accounts that nobody logs in as.
  2. Privileges are granted to the roles (see GRANT).
  3. Priya receives the analyst role. SET DEFAULT ROLE makes it active automatically when she logs in; otherwise she would have to run SET ROLE 'analyst';.
  4. SHOW GRANTS ... USING 'analyst' shows what she can actually do through the role.

When Priya joins the development team, one statement changes her access: GRANT 'developer' TO 'priya'@'localhost'; and later REVOKE 'analyst' FROM 'priya'@'localhost';.

Least privilege

AccountShould have
Web applicationSELECT, INSERT, UPDATE, DELETE on its own database only
Reporting / BI toolSELECT only, ideally on views
Test automationfull rights on a test database, nothing on production
Migrations / deploysCREATE, ALTER, INDEX, DROP on the app database
DBAadministrative privileges, with strong authentication

Never connect applications as root. If an application with only SELECT is attacked through SQL injection, the attacker cannot drop tables.

Quick quiz

1. Are ‘app’@’localhost’ and ‘app’@’%’ the same account?

2. What privileges does a new user have right after CREATE USER?

3. Why use roles?

Users and roles FAQ

How do I create a user in MySQL?

CREATE USER 'name'@'host' IDENTIFIED BY 'password'; then grant privileges with GRANT.

How do I see all users in MySQL?

SELECT user, host FROM mysql.user;

What is a role in MySQL?

A named collection of privileges (MySQL 8.0+). Grant privileges to the role and the role to users; activate it with SET DEFAULT ROLE or SET ROLE.

How do I change a MySQL user’s password?

ALTER USER 'name'@'host' IDENTIFIED BY 'new password';

SQL UNION and UNION ALL

SQL Tutorial › Joins & Set Operators › UNION & UNION ALL

SQL UNION and UNION ALL

IntermediateLesson 38 of 699 min read5 queries run on MySQL 8.4

A join puts columns side by side; UNION stacks rows on top of each other. Use it to merge similar lists from different tables or conditions: all people in a mailing list (customers and employees), or a report row plus a total row. UNION removes duplicates, UNION ALL keeps them.

In this lesson
  • Stack the results of two queries with UNION
  • Know the column rules
  • Choose between UNION and UNION ALL
  • Sort and label the combined result

UNION syntax

MySQL
SELECT column1, column2 FROM table1UNION [ALL]SELECT column1, column2 FROM table2[ORDER BY ...];

Rules:

  • every SELECT must return the same number of columns;
  • columns are matched by position, and should have compatible types;
  • the column names come from the first SELECT;
  • one ORDER BY at the very end sorts the combined result.

Combine two lists

Every city where we have an employee or a customer:

SQL
SELECT city FROM employeesUNIONSELECT city FROM customersORDER BY city;
ResultMySQL 8.4
city
Bengaluru
Chennai
Delhi
Goa
Hyderabad
Kolkata
Mumbai
Pune
8 rows in set
How this query works, step by step
  1. The first query returns 12 employee cities, the second 7 customer cities.
  2. UNION merges them and removes duplicates, such as Mumbai, which appears in both.
  3. ORDER BY city sorts the final list.

UNION ALL keeps duplicates

SQL
SELECT city FROM employeesUNION ALLSELECT city FROM customersORDER BY city;
ResultMySQL 8.4
city
Bengaluru
Bengaluru
Bengaluru
Bengaluru
Bengaluru
Chennai
Delhi
Delhi
Goa
Hyderabad
Kolkata
Kolkata
Mumbai
Mumbai
Mumbai
Mumbai
Pune
Pune
Pune
19 rows in set
How this query works, step by step
  1. UNION ALL simply appends the second result to the first: 12 + 7 = 19 rows.
  2. No duplicate check is done, so it is faster. Use it whenever duplicates are impossible or wanted.

Label where each row came from

A contact list from two tables, with a type column:

SQL
SELECT CONCAT(first_name, ' ', last_name) AS name, city, 'Employee' AS typeFROM employeesWHERE city = 'Mumbai'UNION ALLSELECT customer_name, city, 'Customer'FROM customersWHERE city = 'Mumbai'ORDER BY type, name;
ResultMySQL 8.4
namecitytype
Ananya SharmaMumbaiCustomer
Meera KapoorMumbaiEmployee
Neha GuptaMumbaiEmployee
Vikram SinghMumbaiEmployee
4 rows in set
How this query works, step by step
  1. Both queries return three columns: a name, a city and a constant label.
  2. The second query’s columns have no aliases; the names name, city and type come from the first query.
  3. ORDER BY type, name uses those names.

A total row

SQL
SELECT category, SUM(stock) AS unitsFROM productsGROUP BY categoryUNION ALLSELECT 'TOTAL', SUM(stock)FROM products;
ResultMySQL 8.4
categoryunits
Electronics165
Furniture65
Stationery550
TOTAL780
4 rows in set

Column count must match

MySQL
SELECT first_name, city FROM employeesUNIONSELECT customer_name FROM customers;
ResultMySQL 8.4
ERROR 1222 (21000): The used SELECT statements have a different number of columns

UNION vs JOIN

UNIONJOIN
Combinesrows (vertically)columns (horizontally)
Needssame number of columnsa matching condition
Typical usemerge similar listsrelate different tables

Exercise: All people named with A

List every employee first name and every customer name that starts with “A”, in one alphabetical list without duplicates.

Expected resultMySQL 8.4
name
Amit
Ananya Sharma
Arjun
Asha
4 rows in set
Hint 1

Two SELECTs with LIKE 'A%', combined with UNION.

Quick quiz

1. What is the difference between UNION and UNION ALL?

2. Where do the column names of a UNION result come from?

3. Which is faster when duplicates do not matter?

UNION FAQ

What does UNION do in SQL?

It combines the result rows of two or more SELECT statements into one result and removes duplicates.

Can I use ORDER BY with UNION?

Yes, once, at the end, to sort the combined result. To sort or limit one part only, wrap it in parentheses.

Do the column names have to match in a UNION?

No. Only the number of columns and compatible types matter; the result uses the first query’s names.

SQL Subqueries

SQL Tutorial › Subqueries & CTEs › Subqueries

SQL Subqueries

IntermediateLesson 40 of 6912 min read8 queries run on MySQL 8.4

A subquery is a query inside another query. It lets you use one result as an input to the next question: “employees who earn more than the average”, “customers who placed an order in March”, “the average order value per customer”. This lesson shows the three places a subquery can go, and runs each one step by step.

In this lesson
  • Use a scalar subquery that returns one value
  • Filter with IN (subquery)
  • Query a derived table in FROM
  • Add a subquery column in SELECT, and know when to use a JOIN instead

Three places for a subquery

WhereReturnsExample use
WHERE / HAVINGone value, or a listabove-average salaries, customers who ordered
FROM (derived table)a whole tableaggregate first, then filter or join
SELECT listone value per rowthe company average next to each salary

The inner query is always written in parentheses.

Sample data

employees · 12 rows (some columns)
emp_idfirst_namedept_idsalary
100MeeraNULL250000
101Asha1125000
102Rahul198000
103Priya172000
104Karan172000
105Vikram2105000
106Neha254000
107Arjun248000
108Kavya388000
109Rohan345000
110Sneha478000
111Amit125000

Scalar subquery: one value

Employees who earn more than the company average:

SQL
SELECT first_name, salaryFROM employeesWHERE salary > (SELECT AVG(salary) FROM employees)ORDER BY salary DESC;
ResultMySQL 8.4
first_namesalary
Meera250000
Asha125000
Vikram105000
Rahul98000
4 rows in set
How this query works, step by step
  1. MySQL runs the inner query first: AVG(salary) is 88,333.3333.
  2. The outer query becomes WHERE salary > 88333.3333.
  3. Four employees earn more: Meera, Asha, Vikram and Rahul.

Why not WHERE salary > AVG(salary)? Because aggregates are not allowed in WHERE; the subquery computes the average separately.

MySQL
SELECT first_name FROM employees WHERE salary > AVG(salary);
ResultMySQL 8.4
ERROR 1111 (HY000): Invalid use of group function

The highest-paid person in Engineering:

SQL
SELECT first_name, salaryFROM employeesWHERE dept_id = 1  AND salary = (SELECT MAX(salary) FROM employees WHERE dept_id = 1);
ResultMySQL 8.4
first_namesalary
Asha125000
1 row in set

IN subquery: a list of values

Customers who have placed at least one delivered order:

SQL
SELECT customer_id, customer_nameFROM customersWHERE customer_id IN (  SELECT customer_id FROM orders WHERE status = 'Delivered')ORDER BY customer_id;
ResultMySQL 8.4
customer_idcustomer_name
1Ananya Sharma
2Rohit Kumar
3Fatima Khan
4David Fernandes
5Ishita Bose
5 rows in set
How this query works, step by step
  1. The inner query lists the customer_id of every delivered order: 1, 2, 1, 4, 2, 3, 5.
  2. IN checks each customer against that list; repeats in the list do not matter.
  3. Five customers match.

NOT IN asks the opposite question. Products that have never been ordered:

SQL
SELECT product_nameFROM productsWHERE product_id NOT IN (SELECT product_id FROM order_items);
ResultMySQL 8.4
product_name
Monitor Arm
1 row in set
Watch out

NOT IN returns nothing if the subquery returns a NULL. order_items.product_id is part of the primary key and cannot be NULL, so it is safe here; in general prefer NOT EXISTS.

Subquery in FROM: derived tables

A subquery in FROM produces a temporary table that the outer query reads. It must have an alias. Average order value per customer:

SQL
SELECT t.customer_id,       COUNT(*)           AS orders,       ROUND(AVG(t.total)) AS avg_order_valueFROM (  SELECT o.order_id, o.customer_id, SUM(oi.quantity * p.price) AS total  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  GROUP BY o.order_id, o.customer_id) AS tGROUP BY t.customer_idORDER BY avg_order_value DESC;
ResultMySQL 8.4
customer_idordersavg_order_value
3211249
1310013
224899
523321
413286
5 rows in set
How this query works, step by step
  1. The inner query computes the total of each order: one row per order.
  2. AS t names that result so the outer query can use it like a table.
  3. The outer query groups the order totals by customer and averages them.
  4. Two levels of grouping (lines → orders → customers) are exactly what derived tables are for.

Subquery in SELECT

Show each salary next to the department average:

SQL
SELECT e.first_name, e.dept_id, e.salary,       (SELECT ROUND(AVG(x.salary))          FROM employees x         WHERE x.dept_id = e.dept_id) AS dept_avgFROM employees eWHERE e.dept_id IN (1, 2)ORDER BY e.dept_id, e.salary DESC;
ResultMySQL 8.4
first_namedept_idsalarydept_avg
Asha112500078400
Rahul19800078400
Priya17200078400
Karan17200078400
Amit12500078400
Vikram210500069000
Neha25400069000
Arjun24800069000
8 rows in set
How this query works, step by step
  1. For each outer row, the subquery calculates the average salary of that row’s department.
  2. Because it refers to e.dept_id from the outer query, it is a correlated subquery.
  3. The same result is usually written more efficiently with a window function: AVG(salary) OVER (PARTITION BY dept_id).

Subquery in HAVING

Departments whose average salary is above the company average:

SQL
SELECT dept_id, ROUND(AVG(salary)) AS avg_salaryFROM employeesWHERE dept_id IS NOT NULLGROUP BY dept_idHAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
ResultMySQL 8.4
Empty set
How this query works, step by step
  1. The subquery returns the company average, 88,333.3333, which includes the CEO’s 250,000.
  2. Each department’s average is compared with it: Engineering 78,400, Sales 69,000, Marketing 66,500, HR 78,000.
  3. None is higher, so the result is an Empty set. One very high salary pulls the company average above every department: always check for outliers before trusting an average.

Subquery or JOIN?

Many subqueries can be rewritten as joins, and MySQL 8 often converts them internally anyway. Choose for clarity:

  • “rows that have / do not have a match” reads well as IN, EXISTS or NOT EXISTS;
  • when you need columns from the other table, use a join;
  • when a query has several steps, a CTE is easier to read than nested subqueries.

Exercise: Cheaper than average

List products that cost less than the average product price.

Expected resultMySQL 8.4
product_nameprice
Gel Pens199
Notebook Pack249
Wireless Mouse799
Desk Lamp1499
USB-C Hub1999
Monitor Arm2799
Mechanical Keyboard3499
7 rows in set
Hint 1

WHERE price < (SELECT AVG(price) FROM products).

Exercise: Orders from Mumbai customers

List the orders of customers who live in Mumbai, using a subquery.

Expected resultMySQL 8.4
order_idcustomer_idorder_date
100112025-01-05
100312025-02-02
100812025-04-28
3 rows in set
Hint 1

customer_id IN (SELECT customer_id FROM customers WHERE city = 'Mumbai').

Quick quiz

1. Which runs first in WHERE salary > (SELECT AVG(salary) FROM employees)?

2. What must a subquery in FROM have?

3. A scalar subquery returns two rows. What happens?

Subquery FAQ

What is a subquery in SQL?

A subquery (inner query) is a SELECT nested inside another statement, used to supply a value, a list or a table to the outer query.

What is the difference between a subquery and a join?

A join combines columns of tables row by row. A subquery computes a separate result that the outer query uses. Many problems can be solved either way.

What is a derived table?

A subquery in the FROM clause. It behaves like a temporary table for the duration of the query and must have an alias.

Can a subquery return multiple columns?

Yes, in FROM, or in comparisons such as WHERE (a, b) IN (SELECT x, y FROM ...).

SQL Top N per Group

SQL Tutorial › Window Functions › Top N per Group

SQL Top N per Group

AdvancedLesson 49 of 6910 min read6 queries run on MySQL 8.4

“The top 3 products in each category“, “the latest order of each customer“, “the two best-paid people per department“: top-N-per-group is one of the most common real-world and interview SQL problems. LIMIT cannot do it, because it applies to the whole result. Window functions solve it in one clear pattern.

In this lesson
  • Solve top N per group with ROW_NUMBER in a CTE
  • Decide how ties should be handled (RANK vs ROW_NUMBER)
  • Get the latest or first row per group
  • Know the older correlated-subquery approach

Why LIMIT is not enough

SQL
SELECT dept_id, first_name, salaryFROM employeesORDER BY salary DESCLIMIT 2;
ResultMySQL 8.4
dept_idfirst_namesalary
NULLMeera250000
1Asha125000
2 rows in set

This returns the two best-paid people in the company, not two per department. We need the numbering to restart for each group.

The pattern

MySQL
WITH ranked AS (  SELECT t.*,         ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC) AS rn  FROM t)SELECT * FROM ranked WHERE rn <= N;

Top 2 earners per department

SQL
WITH ranked AS (  SELECT dept_id, first_name, salary,         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS rn  FROM employees  WHERE dept_id IS NOT NULL)SELECT dept_id, first_name, salary, rnFROM rankedWHERE rn <= 2ORDER BY dept_id, rn;
ResultMySQL 8.4
dept_idfirst_namesalaryrn
1Asha1250001
1Rahul980002
2Vikram1050001
2Neha540002
3Kavya880001
3Rohan450002
4Sneha780001
7 rows in set
How this query works, step by step
  1. PARTITION BY dept_id numbers each department separately.
  2. ORDER BY salary DESC, emp_id puts the highest salary first; emp_id breaks ties so the numbering is repeatable.
  3. The outer query keeps rows numbered 1 and 2 in each department.
  4. HR has only one employee, so it contributes one row.

Ties: ROW_NUMBER or RANK?

Top 3 salaries in Engineering. Priya and Karan tie for third place:

SQL
WITH ranked AS (  SELECT first_name, salary,         ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS rn,         RANK()       OVER (ORDER BY salary DESC)         AS rnk  FROM employees  WHERE dept_id = 1)SELECT first_name, salary, rn, rnk FROM ranked WHERE rnk <= 3;
ResultMySQL 8.4
first_namesalaryrnrnk
Asha12500011
Rahul9800022
Priya7200033
Karan7200043
4 rows in set
How this query works, step by step
  1. Filtering on rn <= 3 would return exactly three people and silently drop Karan.
  2. Filtering on rnk <= 3 returns four, because both tied people are third.
  3. Decide with the business which is right: “exactly N rows” (ROW_NUMBER) or “everyone in the top N positions” (RANK / DENSE_RANK).

Best-selling product per category

MySQL
WITH sales AS (  SELECT p.category, p.product_name, SUM(oi.quantity) AS units  FROM order_items oi  JOIN products p ON p.product_id = oi.product_id  GROUP BY p.category, p.product_name),ranked AS (  SELECT sales.*,         RANK() OVER (PARTITION BY category ORDER BY units DESC) AS rnk  FROM sales)SELECT category, product_name, unitsFROM rankedWHERE rnk = 1ORDER BY category;
ResultMySQL 8.4
categoryproduct_nameunits
ElectronicsMechanical Keyboard3
FurnitureOffice Chair3
FurnitureDesk Lamp3
StationeryNotebook Pack18
4 rows in set
How this query works, step by step
  1. sales totals the units sold per product.
  2. ranked ranks products inside each category by units.
  3. Keeping rnk = 1 returns each category’s best seller, and both products if two tie.

Latest order per customer

SQL
WITH ranked AS (  SELECT o.*,         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn  FROM orders o)SELECT customer_id, order_id, order_date, statusFROM rankedWHERE rn = 1ORDER BY customer_id;
ResultMySQL 8.4
customer_idorder_idorder_datestatus
110082025-04-28Pending
210072025-04-10Delivered
310092025-05-15Delivered
410052025-03-08Delivered
510102025-06-01Delivered
5 rows in set

The pre-MySQL 8 way

Without window functions, top-1 per group used a correlated subquery or a join to the group maximum (Correlated Subqueries). Top N > 1 needed a counting subquery:

SQL
SELECT e.dept_id, e.first_name, e.salaryFROM employees eWHERE (SELECT COUNT(*) FROM employees x       WHERE x.dept_id = e.dept_id AND x.salary > e.salary) < 2  AND e.dept_id IS NOT NULLORDER BY e.dept_id, e.salary DESC;
ResultMySQL 8.4
dept_idfirst_namesalary
1Asha125000
1Rahul98000
2Vikram105000
2Neha54000
3Kavya88000
3Rohan45000
4Sneha78000
7 rows in set
How this query works, step by step
  1. For each employee, count colleagues in the same department who earn more.
  2. Fewer than 2 such colleagues means the employee is in the top 2.
  3. It works, but it is harder to read and slower than the window-function pattern.

Exercise: Two cheapest products per category

List the two cheapest products of each category.

Expected resultMySQL 8.4
categoryproduct_nameprice
ElectronicsWireless Mouse799
ElectronicsUSB-C Hub1999
FurnitureDesk Lamp1499
FurnitureMonitor Arm2799
StationeryGel Pens199
StationeryNotebook Pack249
6 rows in set
Hint 1

ROW_NUMBER() OVER (PARTITION BY category ORDER BY price) and keep rn <= 2.

Quick quiz

1. Why can’t ORDER BY ... LIMIT 3 give the top 3 per group?

2. Which function returns everyone tied in the top N positions?

3. Why add emp_id to the window’s ORDER BY?

Top N per group FAQ

How do I select the top N rows per group in SQL?

Number rows within each group with ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) in a CTE, then keep the rows where the number is at most N.

How do I get the latest record for each group?

Use ROW_NUMBER() OVER (PARTITION BY group ORDER BY date DESC) and keep row number 1.

How do I handle ties in top N queries?

Use ROW_NUMBER with a tie-breaker for exactly N rows, or RANK / DENSE_RANK to include every row tied at the cut-off.

SQL Running Totals and Moving Averages

SQL Tutorial › Window Functions › Running Totals & Moving Averages

SQL Running Totals and Moving Averages

AdvancedLesson 48 of 6911 min read7 queries run on MySQL 8.4

A running total adds each row to everything before it: revenue to date, stock level after each movement, headcount over time. A moving average smooths a series over the last few rows. Both are one window function each, once you understand the frame: which rows around the current one are included.

In this lesson
  • Build running totals with SUM() OVER (ORDER BY ...)
  • Restart totals per group with PARTITION BY
  • Control the frame with ROWS BETWEEN
  • Calculate moving averages and percent of total

Daily revenue

All examples use the revenue per order date, built in a CTE:

SQL
WITH daily AS (  SELECT o.order_date, SUM(oi.quantity * p.price) AS revenue  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.status <> 'Cancelled'  GROUP BY o.order_date)SELECT * FROM daily ORDER BY order_date;
ResultMySQL 8.4
order_daterevenue
2025-01-054744
2025-01-178999
2025-02-024298
2025-03-083286
2025-03-225497
2025-04-10799
2025-04-2820996
2025-05-153499
2025-06-011145
9 rows in set

Running total

SQL
WITH daily AS (  SELECT o.order_date, SUM(oi.quantity * p.price) AS revenue  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.status <> 'Cancelled'  GROUP BY o.order_date)SELECT order_date, revenue,       SUM(revenue) OVER (ORDER BY order_date) AS revenue_to_dateFROM dailyORDER BY order_date;
ResultMySQL 8.4
order_daterevenuerevenue_to_date
2025-01-0547444744
2025-01-17899913743
2025-02-02429818041
2025-03-08328621327
2025-03-22549726824
2025-04-1079927623
2025-04-282099648619
2025-05-15349952118
2025-06-01114553263
9 rows in set
How this query works, step by step
  1. The CTE gives one row per day with that day’s revenue.
  2. SUM(revenue) OVER (ORDER BY order_date) adds the revenue of the current day and every earlier day.
  3. The last row’s running total is the grand total of all non-cancelled orders.

Running total per group

Each customer’s cumulative spend, order by order:

SQL
WITH order_totals AS (  SELECT o.customer_id, o.order_id, o.order_date, SUM(oi.quantity * p.price) AS total  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.status <> 'Cancelled'  GROUP BY o.customer_id, o.order_id, o.order_date)SELECT customer_id, order_id, order_date, total,       SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS customer_to_dateFROM order_totalsORDER BY customer_id, order_date;
ResultMySQL 8.4
customer_idorder_idorder_datetotalcustomer_to_date
110012025-01-0547444744
110032025-02-0242989042
110082025-04-282099630038
210022025-01-1789998999
210072025-04-107999798
310092025-05-1534993499
410052025-03-0832863286
510062025-03-2254975497
510102025-06-0111456642
9 rows in set
How this query works, step by step
  1. PARTITION BY customer_id restarts the running total for each customer.
  2. Inside each customer, ORDER BY order_date accumulates order by order.

Window frames: ROWS and RANGE

The frame decides which rows the function sees for the current row:

FrameIncludes
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWfirst row up to the current row
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWthe current row and the two before it
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGprevious, current and next
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGthe whole partition
Note

With ORDER BY and no explicit frame, MySQL uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same ORDER BY value as one step, so tied rows get the same running total. Write ROWS when you want strictly row-by-row totals.

SQL
SELECT first_name, salary,       SUM(salary) OVER (ORDER BY salary) AS range_total,       SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_totalFROM employeesWHERE dept_id = 1ORDER BY salary, emp_id;
ResultMySQL 8.4
first_namesalaryrange_totalrows_total
Amit250002500025000
Priya7200016900097000
Karan72000169000169000
Rahul98000267000267000
Asha125000392000392000
5 rows in set
How this query works, step by step
  1. Priya and Karan both earn 72,000.
  2. With the default RANGE frame, both tied rows include each other: they both show 169,000.
  3. With ROWS, the first of the pair shows 97,000 and the second 169,000.

Moving average

A 3-order moving average of order values smooths out spikes:

SQL
WITH order_totals AS (  SELECT o.order_id, o.order_date, SUM(oi.quantity * p.price) AS total  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  GROUP BY o.order_id, o.order_date)SELECT order_id, order_date, total,       ROUND(AVG(total) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) AS moving_avg_3FROM order_totalsORDER BY order_date;
ResultMySQL 8.4
order_idorder_datetotalmoving_avg_3
10012025-01-0547444744
10022025-01-1789996872
10032025-02-0242986014
10042025-02-141899910765
10052025-03-0832868861
10062025-03-2254979261
10072025-04-107993194
10082025-04-28209969097
10092025-05-1534998431
10102025-06-0111458547
10 rows in set
How this query works, step by step
  1. For each order, the frame holds that order and the two before it.
  2. AVG over the frame is the moving average.
  3. The first two rows have fewer than three orders in the frame, so their average uses one and two orders.

Percent of total and cumulative percent

Which categories make up 80% of revenue (a Pareto analysis)?

MySQL
WITH cat AS (  SELECT p.category, SUM(oi.quantity * p.price) AS revenue  FROM order_items oi  JOIN products p ON p.product_id = oi.product_id  GROUP BY p.category)SELECT category, revenue,       ROUND(100 * revenue / SUM(revenue) OVER (), 1) AS pct,       ROUND(100 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER (), 1) AS cumulative_pctFROM catORDER BY revenue DESC;
ResultMySQL 8.4
categoryrevenuepctcumulative_pct
Furniture5049369.969.9
Electronics1609322.392.1
Stationery56767.9100.0
3 rows in set

Running total without window functions

Before MySQL 8, running totals needed a correlated subquery, which re-adds all earlier rows for every row (slow on big tables):

SQL
SELECT o.order_id, o.order_date,       (SELECT COUNT(*) FROM orders x WHERE x.order_date <= o.order_date) AS orders_to_dateFROM orders oORDER BY o.order_date;
ResultMySQL 8.4
order_idorder_dateorders_to_date
10012025-01-051
10022025-01-172
10032025-02-023
10042025-02-144
10052025-03-085
10062025-03-226
10072025-04-107
10082025-04-288
10092025-05-159
10102025-06-0110
10 rows in set

Exercise: Cumulative headcount

List employees by hire_date with a running count of employees hired so far.

Expected resultMySQL 8.4
first_namehire_dateheadcount
Meera2015-01-051
Vikram2017-11-202
Asha2018-04-123
Sneha2019-02-254
Rahul2019-07-015
Kavya2020-09-096
Priya2021-01-187
Neha2022-03-148
Karan2022-08-299
Arjun2023-06-0510
Rohan2024-01-2211
Amit2025-06-0212
12 rows in set
Hint 1

COUNT(*) OVER (ORDER BY hire_date).

Quick quiz

1. Which window gives a running total?

2. What frame gives a 3-row moving average?

3. Why do two tied rows show the same running total by default?

Running total FAQ

How do I calculate a running total in SQL?

Use SUM(amount) OVER (ORDER BY date_column). Add PARTITION BY to restart the total for each group.

What is the difference between ROWS and RANGE?

ROWS counts physical rows. RANGE works on values, so rows with the same ORDER BY value are treated together.

How do I calculate a moving average in MySQL?

AVG(value) OVER (ORDER BY date ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW) gives an n-row moving average.

SQL Project: QA Test Results Tracker

SQL Tutorial › SQL Projects › Project: QA Test Results Tracker

SQL Project: QA Test Results Tracker

ProjectProject 5 of 530 min read13 queries run on MySQL 8.4

Test automation produces thousands of results, and SQL turns them into answers: did this build get better or worse, which tests are flaky, what broke between two builds, how much is automated, and how fast do we fix defects? In this project you are the QA lead of a web, API and mobile product with six nightly runs stored in MySQL. Every query is one a real test team runs.

In this lesson
  • Calculate pass rates per run and per suite
  • Find the tests that fail now, flip between results, or newly regressed
  • Measure execution time and automation coverage
  • Report defect counts and time to fix

The database

TableOne row perNotes
test_suitessuiteplatform: Web, API, Mobile
test_casestest casepriority P1-P3, automated 1 or 0
test_runsnightly runthe build it tested, environment
test_resultscase in a runstatus: PASSED, FAILED, SKIPPED; duration_ms NULL when skipped
defectsbug linked to a caseseverity, status, opened_on, closed_on
test_suites · 5 rows
suite_idsuite_nameplatform
1Login & AuthWeb
2CheckoutWeb
3SearchWeb
4Payments APIAPI
5Mobile SmokeMobile
test_runs · 6 rows
run_idbuildstarted_atenvironment
12.4.02025-09-01 02:00:00staging
22.4.12025-09-08 02:00:00staging
32.5.02025-09-15 02:00:00uat
42.5.12025-09-22 02:00:00staging
52.6.02025-09-29 02:00:00staging
62.6.12025-10-06 02:00:00uat
test_cases · 22 rows
case_idsuite_idtitlepriorityautomated
11Valid loginP11
21Invalid passwordP21
31Locked accountP20
41Password reset emailP21
51Logout clears sessionP10
62Add to cartP30
72Apply couponP31
82Remove itemP20
92Guest checkoutP11
102Order confirmation emailP11
113Search by keywordP21
123Filter by priceP11
133Sort by ratingP21
143No results messageP10
154Create payment 201P21
164Refund paymentP21
174Invalid card 402P21
184Idempotency keyP31
195App launchP20
205Biometric loginP21
215Push notificationP21
223Voice searchP30
test_results · first 10 of 126 rows
run_idcase_idstatusduration_ms
11PASSED5969
12PASSED968
13PASSED6333
14PASSED1927
15PASSED2640
16PASSED2146
17FAILED6356
18PASSED8730
19PASSED3101
110PASSED2746
defects · 6 rows
defect_idcase_idseveritystatusopened_onclosed_on
118CriticalOpen2025-09-29NULL
27MajorOpen2025-09-10NULL
313MinorClosed2025-09-092025-09-15
42MajorClosed2025-09-022025-09-04
510CriticalClosed2025-09-162025-09-17
621MinorOpen2025-10-06NULL
qa.sql
-- sqatools.in SQL project 5: QA test results tracker (generated by gen_projects.py)CREATE TABLE test_suites (  suite_id   INT PRIMARY KEY,  suite_name VARCHAR(30) NOT NULL,  platform   VARCHAR(10) NOT NULL);CREATE TABLE test_cases (  case_id   INT PRIMARY KEY,  suite_id  INT NOT NULL,  title     VARCHAR(40) NOT NULL,  priority  VARCHAR(2) NOT NULL,  automated INT NOT NULL,  FOREIGN KEY (suite_id) REFERENCES test_suites (suite_id));CREATE TABLE test_runs (  run_id      INT PRIMARY KEY,  build       VARCHAR(10) NOT NULL,  started_at  DATETIME NOT NULL,  environment VARCHAR(10) NOT NULL);CREATE TABLE test_results (  run_id      INT,  case_id     INT,  status      VARCHAR(8) NOT NULL,  duration_ms INT,  PRIMARY KEY (run_id, case_id),  FOREIGN KEY (run_id) REFERENCES test_runs (run_id),  FOREIGN KEY (case_id) REFERENCES test_cases (case_id));CREATE TABLE defects (  defect_id INT PRIMARY KEY,  case_id   INT NOT NULL,  severity  VARCHAR(10) NOT NULL,  status    VARCHAR(8) NOT NULL,  opened_on DATE NOT NULL,  closed_on DATE,  FOREIGN KEY (case_id) REFERENCES test_cases (case_id));INSERT INTO test_suites VALUES(1, 'Login & Auth', 'Web'),(2, 'Checkout', 'Web'),(3, 'Search', 'Web'),(4, 'Payments API', 'API'),(5, 'Mobile Smoke', 'Mobile');INSERT INTO test_cases VALUES(1, 1, 'Valid login', 'P1', 1),(2, 1, 'Invalid password', 'P2', 1),(3, 1, 'Locked account', 'P2', 0),(4, 1, 'Password reset email', 'P2', 1),(5, 1, 'Logout clears session', 'P1', 0),(6, 2, 'Add to cart', 'P3', 0),(7, 2, 'Apply coupon', 'P3', 1),(8, 2, 'Remove item', 'P2', 0),(9, 2, 'Guest checkout', 'P1', 1),(10, 2, 'Order confirmation email', 'P1', 1),(11, 3, 'Search by keyword', 'P2', 1),(12, 3, 'Filter by price', 'P1', 1),(13, 3, 'Sort by rating', 'P2', 1),(14, 3, 'No results message', 'P1', 0),(15, 4, 'Create payment 201', 'P2', 1),(16, 4, 'Refund payment', 'P2', 1),(17, 4, 'Invalid card 402', 'P2', 1),(18, 4, 'Idempotency key', 'P3', 1),(19, 5, 'App launch', 'P2', 0),(20, 5, 'Biometric login', 'P2', 1),(21, 5, 'Push notification', 'P2', 1),(22, 3, 'Voice search', 'P3', 0);INSERT INTO test_runs VALUES(1, '2.4.0', '2025-09-01 02:00:00', 'staging'),(2, '2.4.1', '2025-09-08 02:00:00', 'staging'),(3, '2.5.0', '2025-09-15 02:00:00', 'uat'),(4, '2.5.1', '2025-09-22 02:00:00', 'staging'),(5, '2.6.0', '2025-09-29 02:00:00', 'staging'),(6, '2.6.1', '2025-10-06 02:00:00', 'uat');INSERT INTO test_results VALUES(1, 1, 'PASSED', 5969),(1, 2, 'PASSED', 968),(1, 3, 'PASSED', 6333),(1, 4, 'PASSED', 1927),(1, 5, 'PASSED', 2640),(1, 6, 'PASSED', 2146),(1, 7, 'FAILED', 6356),(1, 8, 'PASSED', 8730),(1, 9, 'PASSED', 3101),(1, 10, 'PASSED', 2746),(1, 11, 'PASSED', 7067),(1, 12, 'FAILED', 3760),(1, 13, 'FAILED', 5395),(1, 14, 'PASSED', 1147),(1, 15, 'PASSED', 8947),(1, 16, 'PASSED', 8762),(1, 17, 'PASSED', 7227),(1, 18, 'PASSED', 2634),(1, 19, 'PASSED', 30092),(1, 20, 'PASSED', 33695),(1, 21, 'PASSED', 33975),(2, 1, 'PASSED', 2131),(2, 2, 'PASSED', 978),(2, 3, 'SKIPPED', NULL),(2, 4, 'PASSED', 3910),(2, 5, 'SKIPPED', NULL),(2, 6, 'SKIPPED', NULL),(2, 7, 'FAILED', 1852),(2, 8, 'SKIPPED', NULL),(2, 9, 'PASSED', 8013),(2, 10, 'PASSED', 2729),(2, 11, 'PASSED', 2948),(2, 12, 'PASSED', 6213),(2, 13, 'PASSED', 4826),(2, 14, 'SKIPPED', NULL),(2, 15, 'PASSED', 3753),(2, 16, 'PASSED', 8448),(2, 17, 'PASSED', 1503),(2, 18, 'PASSED', 975),(2, 19, 'SKIPPED', NULL),(2, 20, 'PASSED', 32236),(2, 21, 'PASSED', 30448),(3, 1, 'PASSED', 1993),(3, 2, 'PASSED', 2459),(3, 3, 'PASSED', 6402),(3, 4, 'PASSED', 4759),(3, 5, 'PASSED', 6222),(3, 6, 'PASSED', 7457),(3, 7, 'PASSED', 5799),(3, 8, 'PASSED', 2869),(3, 9, 'FAILED', 4755),(3, 10, 'PASSED', 3893),(3, 11, 'PASSED', 8181),(3, 12, 'PASSED', 4826),(3, 13, 'PASSED', 6064),(3, 14, 'PASSED', 5385),(3, 15, 'PASSED', 2439),(3, 16, 'PASSED', 5068),(3, 17, 'PASSED', 1965),(3, 18, 'PASSED', 1934),(3, 19, 'PASSED', 28607),(3, 20, 'PASSED', 32897),(3, 21, 'PASSED', 28175),(4, 1, 'PASSED', 8998),(4, 2, 'PASSED', 6061),(4, 3, 'SKIPPED', NULL),(4, 4, 'PASSED', 6423),(4, 5, 'SKIPPED', NULL),(4, 6, 'SKIPPED', NULL),(4, 7, 'PASSED', 1813),(4, 8, 'SKIPPED', NULL),(4, 9, 'PASSED', 8461),(4, 10, 'PASSED', 6125),(4, 11, 'PASSED', 2249),(4, 12, 'PASSED', 8424),(4, 13, 'FAILED', 7935),(4, 14, 'SKIPPED', NULL),(4, 15, 'PASSED', 5023),(4, 16, 'PASSED', 5049),(4, 17, 'PASSED', 2330),(4, 18, 'PASSED', 6865),(4, 19, 'SKIPPED', NULL),(4, 20, 'PASSED', 29325),(4, 21, 'PASSED', 33969),(5, 1, 'PASSED', 3634),(5, 2, 'PASSED', 6497),(5, 3, 'PASSED', 8728),(5, 4, 'PASSED', 3981),(5, 5, 'PASSED', 8592),(5, 6, 'PASSED', 4813),(5, 7, 'FAILED', 7004),(5, 8, 'PASSED', 7863),(5, 9, 'PASSED', 2223),(5, 10, 'PASSED', 8987),(5, 11, 'PASSED', 4101),(5, 12, 'PASSED', 7382),(5, 13, 'PASSED', 5341),(5, 14, 'PASSED', 8458),(5, 15, 'PASSED', 2612),(5, 16, 'FAILED', 8287),(5, 17, 'PASSED', 6899),(5, 18, 'FAILED', 5129),(5, 19, 'PASSED', 29258),(5, 20, 'PASSED', 30288),(5, 21, 'PASSED', 27586),(6, 1, 'PASSED', 8385),(6, 2, 'PASSED', 8038),(6, 3, 'PASSED', 5577),(6, 4, 'PASSED', 3822),(6, 5, 'PASSED', 6710),(6, 6, 'PASSED', 2270),(6, 7, 'FAILED', 7986),(6, 8, 'PASSED', 8345),(6, 9, 'PASSED', 2902),(6, 10, 'PASSED', 2769),(6, 11, 'PASSED', 8079),(6, 12, 'PASSED', 8427),(6, 13, 'FAILED', 2514),(6, 14, 'PASSED', 5783),(6, 15, 'PASSED', 7571),(6, 16, 'PASSED', 8342),(6, 17, 'PASSED', 6743),(6, 18, 'FAILED', 3983),(6, 19, 'PASSED', 26334),(6, 20, 'PASSED', 29237),(6, 21, 'PASSED', 33799);INSERT INTO defects VALUES(1, 18, 'Critical', 'Open', '2025-09-29', NULL),(2, 7, 'Major', 'Open', '2025-09-10', NULL),(3, 13, 'Minor', 'Closed', '2025-09-09', '2025-09-15'),(4, 2, 'Major', 'Closed', '2025-09-02', '2025-09-04'),(5, 10, 'Critical', 'Closed', '2025-09-16', '2025-09-17'),(6, 21, 'Minor', 'Open', '2025-10-06', NULL);

Task 1: Pass rate per run

MySQL
SELECT r.run_id, r.build, r.environment,       COUNT(*)                     AS executed,       SUM(tr.status = 'PASSED')    AS passed,       SUM(tr.status = 'FAILED')    AS failed,       ROUND(100 * SUM(tr.status = 'PASSED') / COUNT(*), 1) AS pass_rateFROM test_runs rJOIN test_results tr ON tr.run_id = r.run_idWHERE tr.status <> 'SKIPPED'GROUP BY r.run_id, r.build, r.environmentORDER BY r.run_id;
ResultMySQL 8.4
run_idbuildenvironmentexecutedpassedfailedpass_rate
12.4.0staging2118385.7
22.4.1staging1514193.3
32.5.0uat2120195.2
42.5.1staging1514193.3
52.6.0staging2118385.7
62.6.1uat2118385.7
6 rows in set
How this query works, step by step
  1. Skipped tests are excluded: pass rate is usually measured over tests that actually ran.
  2. SUM(status = 'PASSED') counts passes because a true comparison is 1.
  3. The trend down the column shows whether quality is improving build by build.

Task 2: Pass rate per suite in the latest run

MySQL
SELECT s.suite_name, s.platform,       COUNT(*) AS executed,       ROUND(100 * SUM(tr.status = 'PASSED') / COUNT(*), 1) AS pass_rateFROM test_results trJOIN test_cases c  ON c.case_id = tr.case_idJOIN test_suites s ON s.suite_id = c.suite_idWHERE tr.run_id = (SELECT MAX(run_id) FROM test_runs)  AND tr.status <> 'SKIPPED'GROUP BY s.suite_name, s.platformORDER BY pass_rate, s.suite_name;
ResultMySQL 8.4
suite_nameplatformexecutedpass_rate
Payments APIAPI475.0
SearchWeb475.0
CheckoutWeb580.0
Login & AuthWeb5100.0
Mobile SmokeMobile3100.0
5 rows in set

The subquery (SELECT MAX(run_id) FROM test_runs) always picks the newest run, so the report needs no editing after each night.

Task 3: What is failing right now?

SQL
SELECT c.case_id, s.suite_name, c.title, c.priority, tr.duration_msFROM test_results trJOIN test_cases c  ON c.case_id = tr.case_idJOIN test_suites s ON s.suite_id = c.suite_idWHERE tr.run_id = (SELECT MAX(run_id) FROM test_runs)  AND tr.status = 'FAILED'ORDER BY c.priority, s.suite_name;
ResultMySQL 8.4
case_idsuite_nametitlepriorityduration_ms
13SearchSort by ratingP22514
7CheckoutApply couponP37986
18Payments APIIdempotency keyP33983
3 rows in set

Task 4: Newly broken tests (regressions)

A regression is a test that passed in the previous run and fails in this one. LAG puts each result next to the same test’s previous result:

SQL
WITH history AS (  SELECT tr.case_id, tr.run_id, tr.status,         LAG(tr.status) OVER (PARTITION BY tr.case_id ORDER BY tr.run_id) AS previous_status  FROM test_results tr)SELECT h.run_id, r.build, c.title, h.previous_status, h.statusFROM history hJOIN test_cases c ON c.case_id = h.case_idJOIN test_runs r  ON r.run_id = h.run_idWHERE h.previous_status = 'PASSED' AND h.status = 'FAILED'ORDER BY h.run_id, c.title;
ResultMySQL 8.4
run_idbuildtitleprevious_statusstatus
32.5.0Guest checkoutPASSEDFAILED
42.5.1Sort by ratingPASSEDFAILED
52.6.0Apply couponPASSEDFAILED
52.6.0Idempotency keyPASSEDFAILED
52.6.0Refund paymentPASSEDFAILED
62.6.1Sort by ratingPASSEDFAILED
6 rows in set
How this query works, step by step
  1. PARTITION BY case_id ORDER BY run_id makes a timeline per test.
  2. LAG(status) reads the test’s status in the run before.
  3. Keeping PASSED → FAILED rows lists every regression and the build that introduced it (LAG & LEAD).

Task 5: Flaky tests

A flaky test changes result between runs without a code reason. Count how many times each test flipped status:

SQL
WITH history AS (  SELECT case_id, run_id, status,         LAG(status) OVER (PARTITION BY case_id ORDER BY run_id) AS prev  FROM test_results  WHERE status <> 'SKIPPED')SELECT c.case_id, c.title,       SUM(h.status <> h.prev)    AS flips,       SUM(h.status = 'FAILED')   AS failures,       COUNT(*)                   AS runsFROM history hJOIN test_cases c ON c.case_id = h.case_idGROUP BY c.case_id, c.titleHAVING flips >= 2ORDER BY flips DESC, c.case_id;
ResultMySQL 8.4
case_idtitleflipsfailuresruns
13Sort by rating436
7Apply coupon246
9Guest checkout216
16Refund payment216
4 rows in set
How this query works, step by step
  1. LAG compares each result with the previous executed result of the same test.
  2. SUM(status <> prev) counts changes. The first row’s prev is NULL, so the comparison is NULL and adds nothing.
  3. Two or more flips over six runs is a strong flakiness signal; a test that broke once and stayed broken flips only once, so it is not reported here.

Task 6: Slowest tests

MySQL
SELECT c.title, s.platform,       COUNT(tr.duration_ms)                AS timed_runs,       ROUND(AVG(tr.duration_ms) / 1000, 1) AS avg_seconds,       ROUND(MAX(tr.duration_ms) / 1000, 1) AS max_secondsFROM test_results trJOIN test_cases c  ON c.case_id = tr.case_idJOIN test_suites s ON s.suite_id = c.suite_idGROUP BY c.case_id, c.title, s.platformORDER BY avg_seconds DESCLIMIT 5;
ResultMySQL 8.4
titleplatformtimed_runsavg_secondsmax_seconds
Push notificationMobile631.334.0
Biometric loginMobile631.333.7
App launchMobile428.630.1
Refund paymentAPI67.38.8
Remove itemWeb47.08.7
5 rows in set

AVG and COUNT(duration_ms) skip the NULL durations of skipped tests.

Task 7: Run duration trend

MySQL
SELECT r.run_id, r.build,       ROUND(SUM(tr.duration_ms) / 60000, 1) AS total_minutes,       ROUND(SUM(tr.duration_ms) / 60000             - LAG(SUM(tr.duration_ms) / 60000) OVER (ORDER BY r.run_id), 1) AS change_minutesFROM test_runs rJOIN test_results tr ON tr.run_id = r.run_idGROUP BY r.run_id, r.buildORDER BY r.run_id;
ResultMySQL 8.4
run_idbuildtotal_minuteschange_minutes
12.4.03.1NULL
22.4.11.8-1.2
32.5.02.91.0
42.5.12.3-0.6
52.6.03.31.0
62.6.13.30.0
6 rows in set

Task 8: Automation coverage

MySQL
SELECT s.suite_name,       COUNT(*)                  AS cases,       SUM(c.automated)          AS automated,       ROUND(100 * SUM(c.automated) / COUNT(*)) AS automation_pctFROM test_suites sJOIN test_cases c ON c.suite_id = s.suite_idGROUP BY s.suite_id, s.suite_nameORDER BY automation_pct, s.suite_name;
ResultMySQL 8.4
suite_namecasesautomatedautomation_pct
Checkout5360
Login & Auth5360
Search5360
Mobile Smoke3267
Payments API44100
5 rows in set

Test cases that have never been executed at all:

SQL
SELECT c.case_id, c.title, c.automatedFROM test_cases cLEFT JOIN test_results tr ON tr.case_id = c.case_idWHERE tr.case_id IS NULL;
ResultMySQL 8.4
case_idtitleautomated
22Voice search0
1 row in set

Task 9: High-priority health

P1 and P2 tests that failed at least once in the last three runs:

MySQL
SELECT c.title, c.priority,       GROUP_CONCAT(CONCAT('run ', tr.run_id, ': ', tr.status) ORDER BY tr.run_id SEPARATOR ', ') AS last_threeFROM test_results trJOIN test_cases c ON c.case_id = tr.case_idWHERE c.priority IN ('P1', 'P2')  AND tr.run_id > (SELECT MAX(run_id) - 3 FROM test_runs)GROUP BY c.case_id, c.title, c.priorityHAVING SUM(tr.status = 'FAILED') > 0;
ResultMySQL 8.4
titleprioritylast_three
Sort by ratingP2run 4: FAILED, run 5: PASSED, run 6: FAILED
Refund paymentP2run 4: PASSED, run 5: FAILED, run 6: PASSED
2 rows in set
How this query works, step by step
  1. run_id > MAX(run_id) - 3 keeps the three most recent runs.
  2. GROUP_CONCAT lists each test’s recent history in one cell.
  3. HAVING keeps only tests with at least one failure among them.

Task 10: Defect metrics

MySQL
SELECT severity,       COUNT(*)                      AS total,       SUM(status = 'Open')          AS still_open,       ROUND(AVG(DATEDIFF(closed_on, opened_on)), 1) AS avg_days_to_fixFROM defectsGROUP BY severityORDER BY FIELD(severity, 'Critical', 'Major', 'Minor');
ResultMySQL 8.4
severitytotalstill_openavg_days_to_fix
Critical211.0
Major212.0
Minor216.0
3 rows in set
How this query works, step by step
  1. AVG(DATEDIFF(closed_on, opened_on)) uses only closed defects, because DATEDIFF with a NULL closed_on is NULL and AVG skips it.
  2. ORDER BY FIELD(...) sorts by business severity, not alphabetically.

Open defects whose test still fails in the latest run, which are the ones to discuss in stand-up:

MySQL
SELECT d.defect_id, d.severity, c.title, d.opened_on,       DATEDIFF('2025-10-06', d.opened_on) AS days_openFROM defects dJOIN test_cases c    ON c.case_id = d.case_idJOIN test_results tr ON tr.case_id = d.case_id AND tr.run_id = (SELECT MAX(run_id) FROM test_runs)WHERE d.status = 'Open' AND tr.status = 'FAILED'ORDER BY FIELD(d.severity, 'Critical', 'Major', 'Minor'), days_open DESC;
ResultMySQL 8.4
defect_idseveritytitleopened_ondays_open
1CriticalIdempotency key2025-09-297
2MajorApply coupon2025-09-1026
2 rows in set

Task 11: Results matrix (pivot)

One row per test, one column per run: the classic test-results grid:

MySQL
SELECT c.case_id, c.title,       MAX(CASE WHEN tr.run_id = 4 THEN LEFT(tr.status, 1) END) AS run_4,       MAX(CASE WHEN tr.run_id = 5 THEN LEFT(tr.status, 1) END) AS run_5,       MAX(CASE WHEN tr.run_id = 6 THEN LEFT(tr.status, 1) END) AS run_6FROM test_cases cJOIN test_results tr ON tr.case_id = c.case_idWHERE c.suite_id IN (2, 4)GROUP BY c.case_id, c.titleORDER BY c.case_id;
ResultMySQL 8.4
case_idtitlerun_4run_5run_6
6Add to cartSPP
7Apply couponPFF
8Remove itemSPP
9Guest checkoutPPP
10Order confirmation emailPPP
15Create payment 201PPP
16Refund paymentPFP
17Invalid card 402PPP
18Idempotency keyPFF
9 rows in set
How this query works, step by step
  1. Each MAX(CASE WHEN run_id = n THEN ... END) picks that run’s status for the test (there is only one, so MAX simply returns it).
  2. LEFT(status, 1) shortens PASSED / FAILED / SKIPPED to P / F / S.
  3. This is the conditional-aggregation pivot from CASE.

Your turn

Exercise: Failures per platform

Count failed results per platform across all runs.

Expected resultMySQL 8.4
platformfailures
Web9
API3
2 rows in set
Hint 1

Join results → cases → suites, filter status = 'FAILED', group by platform.

Exercise: Tests that never failed

List automated tests that passed every time they ran.

Expected resultMySQL 8.4
case_idtitle
1Valid login
2Invalid password
4Password reset email
10Order confirmation email
11Search by keyword
15Create payment 201
17Invalid card 402
20Biometric login
21Push notification
9 rows in set
Hint 1

Group by test and use HAVING SUM(status = 'FAILED') = 0.

What you practised

TaskSQL skills
Pass rates, coverage, defectsconditional SUM, ROUND, AVG(DATEDIFF), FIELD
Latest run reportsscalar subquery (SELECT MAX(run_id) ...)
Regressions, flaky tests, run trendLAG() OVER (PARTITION BY ... ORDER BY ...)
Never executedLEFT JOIN ... IS NULL
Results matrixpivot with MAX(CASE ...)
Tip

Testers use SQL beyond reports: to prepare test data, to verify what an application really stored after a UI action, and to check database constraints. See the SQL tutorial for every topic and the 150+ SQL interview questions.

Project FAQ

Why should testers learn SQL?

Testers use SQL to set up and clean test data, verify backend results after UI or API actions, check data integrity rules, and analyse automation results like the queries in this project.

How do you find flaky tests with SQL?

Compare each result with the previous result of the same test using LAG(status) OVER (PARTITION BY case_id ORDER BY run_id), count the status changes, and flag tests that change repeatedly.

How do you calculate test pass rate in SQL?

100 * SUM(status = 'PASSED') / COUNT(*) over executed (non-skipped) results, grouped by run, suite or build.

SQL ROW_NUMBER, RANK and DENSE_RANK

SQL Tutorial › Window Functions › ROW_NUMBER, RANK, DENSE_RANK

SQL ROW_NUMBER, RANK and DENSE_RANK

AdvancedLesson 46 of 6911 min read7 queries run on MySQL 8.4

ROW_NUMBER, RANK and DENSE_RANK all number rows in order, and they differ only when there are ties. Interviewers love that difference, and real reports depend on it: should two people with the same score share second place, and is the next one third or fourth? This lesson shows all three side by side on data with a tie.

In this lesson
  • The exact difference between ROW_NUMBER, RANK and DENSE_RANK
  • Rank inside groups with PARTITION BY
  • Find the Nth highest value correctly with ties
  • Split rows into buckets with NTILE

Sample data

Priya and Karan both earn 72,000: the tie that makes the functions differ.

employees · 12 rows (some columns)
emp_idfirst_namedept_idsalary
100MeeraNULL250000
101Asha1125000
102Rahul198000
103Priya172000
104Karan172000
105Vikram2105000
106Neha254000
107Arjun248000
108Kavya388000
109Rohan345000
110Sneha478000
111Amit125000

The three functions side by side

SQL
SELECT first_name, salary,       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,       RANK()       OVER (ORDER BY salary DESC) AS rnk,       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnkFROM employeesORDER BY salary DESC;
ResultMySQL 8.4
first_namesalaryrow_numrnkdense_rnk
Meera250000111
Asha125000222
Vikram105000333
Rahul98000444
Kavya88000555
Sneha78000666
Priya72000777
Karan72000877
Neha54000998
Arjun4800010109
Rohan45000111110
Amit25000121211
12 rows in set
How this query works, step by step
  1. All three order the rows by salary, highest first.
  2. ROW_NUMBER gives every row a unique number: the tied pair gets 7 and 8 in an arbitrary order.
  3. RANK gives tied rows the same rank (7) and then skips: the next salary is ranked 9, like positions in a race.
  4. DENSE_RANK gives tied rows the same rank (7) and does not skip: the next is 8.
FunctionTies getAfter a tieUse for
ROW_NUMBER()different numberscontinuesunique numbering, pagination, de-duplication
RANK()the same numberskips (1, 2, 2, 4)competition ranking
DENSE_RANK()the same numberno gap (1, 2, 2, 3)“Nth highest value” questions
Watch out

With ties, ROW_NUMBER decides the order of tied rows arbitrarily. Add a tie-breaker, such as ORDER BY salary DESC, emp_id, when the numbering must be repeatable.

Rank inside each group

PARTITION BY restarts the numbering for each department:

SQL
SELECT dept_id, first_name, salary,       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_deptFROM employeesWHERE dept_id IS NOT NULLORDER BY dept_id, rank_in_dept;
ResultMySQL 8.4
dept_idfirst_namesalaryrank_in_dept
1Asha1250001
1Rahul980002
1Priya720003
1Karan720003
1Amit250004
2Vikram1050001
2Neha540002
2Arjun480003
3Kavya880001
3Rohan450002
4Sneha780001
11 rows in set
How this query works, step by step
  1. Each department is ranked separately; every department’s top earner gets 1.
  2. In Engineering, Priya and Karan share rank 3 and Amit is 4.

Nth highest salary

The interview classic: the third highest distinct salary. With DENSE_RANK, a tie does not swallow a position:

SQL
WITH ranked AS (  SELECT first_name, salary,         DENSE_RANK() OVER (ORDER BY salary DESC) AS dr  FROM employees)SELECT first_name, salaryFROM rankedWHERE dr = 3;
ResultMySQL 8.4
first_namesalary
Vikram105000
1 row in set
How this query works, step by step
  1. The distinct salaries in order are 250,000, 125,000, 105,000, 98,000…
  2. The third distinct salary is 105,000, and dr = 3 returns the person who earns it: Vikram.
  3. Unlike LIMIT 1 OFFSET 2, this returns every person at that salary if several share it.

Second highest salary per department:

SQL
WITH ranked AS (  SELECT dept_id, first_name, salary,         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr  FROM employees  WHERE dept_id IS NOT NULL)SELECT dept_id, first_name, salaryFROM rankedWHERE dr = 2ORDER BY dept_id;
ResultMySQL 8.4
dept_idfirst_namesalary
1Rahul98000
2Neha54000
3Rohan45000
3 rows in set

HR has only one employee, so it has no second salary and does not appear.

Remove duplicates with ROW_NUMBER

Keep only the first row of each duplicate group. Here, one employee per salary value (the lowest emp_id):

SQL
WITH numbered AS (  SELECT emp_id, first_name, salary,         ROW_NUMBER() OVER (PARTITION BY salary ORDER BY emp_id) AS rn  FROM employees)SELECT emp_id, first_name, salaryFROM numberedWHERE rn = 1ORDER BY salary DESC;
ResultMySQL 8.4
emp_idfirst_namesalary
100Meera250000
101Asha125000
105Vikram105000
102Rahul98000
108Kavya88000
110Sneha78000
103Priya72000
106Neha54000
107Arjun48000
109Rohan45000
111Amit25000
11 rows in set
How this query works, step by step
  1. PARTITION BY salary groups rows with the same salary.
  2. ROW_NUMBER numbers them 1, 2, … by emp_id.
  3. Keeping rn = 1 keeps one row per salary: Karan (104) is dropped because Priya (103) comes first.
  4. To actually delete duplicates, delete the rows whose rn > 1.

NTILE: split into buckets

NTILE(n) divides the ordered rows into n groups of (nearly) equal size, for quartiles or “top third”:

SQL
SELECT first_name, salary,       NTILE(4) OVER (ORDER BY salary DESC) AS quartileFROM employeesORDER BY salary DESC;
ResultMySQL 8.4
first_namesalaryquartile
Meera2500001
Asha1250001
Vikram1050001
Rahul980002
Kavya880002
Sneha780002
Priya720003
Karan720003
Neha540003
Arjun480004
Rohan450004
Amit250004
12 rows in set
How this query works, step by step
  1. 12 rows split into 4 buckets of 3.
  2. Quartile 1 holds the top three salaries, quartile 4 the bottom three.
  3. If the rows do not divide evenly, the first buckets get one extra row.

PERCENT_RANK and CUME_DIST

SQL
SELECT first_name, salary,       ROUND(PERCENT_RANK() OVER (ORDER BY salary), 2) AS pct_rank,       ROUND(CUME_DIST()    OVER (ORDER BY salary), 2) AS cumulative_distFROM employeesWHERE dept_id = 1;
ResultMySQL 8.4
first_namesalarypct_rankcumulative_dist
Amit2500000.2
Priya720000.250.6
Karan720000.250.6
Rahul980000.750.8
Asha12500011
5 rows in set
  • PERCENT_RANK = (rank − 1) / (rows − 1): 0 for the lowest, 1 for the highest.
  • CUME_DIST = share of rows with a value less than or equal to the current one.

Exercise: Rank products by price

Rank products by price from the most expensive within each category with RANK(), and show category, product name, price and rank.

Expected resultMySQL 8.4
categoryproduct_namepriceprice_rank
ElectronicsMechanical Keyboard34991
ElectronicsUSB-C Hub19992
ElectronicsWireless Mouse7993
FurnitureStanding Desk189991
FurnitureOffice Chair89992
FurnitureMonitor Arm27993
FurnitureDesk Lamp14994
StationeryNotebook Pack2491
StationeryGel Pens1992
9 rows in set
Hint 1

RANK() OVER (PARTITION BY category ORDER BY price DESC).

Quick quiz

1. Salaries 100, 90, 90, 80. What does RANK() give the 80?

2. Same salaries. What does DENSE_RANK() give the 80?

3. Which function never gives two rows the same number?

4. Which is best for “the 2nd highest distinct salary”?

Ranking FAQ

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same rank. RANK then skips numbers (1, 2, 2, 4); DENSE_RANK continues without gaps (1, 2, 2, 3).

What is the difference between ROW_NUMBER and RANK?

ROW_NUMBER always gives unique, consecutive numbers, even to ties. RANK gives ties the same number.

How do I find the Nth highest salary in SQL?

Use DENSE_RANK() OVER (ORDER BY salary DESC) in a CTE and filter on the rank you need. Add PARTITION BY dept_id for the Nth highest per department.

How do I delete duplicate rows using ROW_NUMBER?

Number the rows of each duplicate group with ROW_NUMBER() OVER (PARTITION BY duplicate_columns ORDER BY id) and delete the rows with a number greater than 1.

SQL Recursive CTE

SQL Tutorial › Subqueries & CTEs › Recursive CTE

SQL Recursive CTE

AdvancedLesson 44 of 6912 min read6 queries run on MySQL 8.4

A recursive CTE is a CTE that refers to itself. It starts from a few rows and keeps adding new rows built from the previous ones until nothing new appears. That is exactly what you need to walk a hierarchy of any depth (who reports to whom), to generate a series of numbers or dates, or to follow a chain of links.

In this lesson
  • Write WITH RECURSIVE with an anchor and a recursive part
  • Generate number and date series
  • Walk an employee hierarchy and show levels and paths
  • Stop runaway recursion

How a recursive CTE works

MySQL
WITH RECURSIVE cte_name AS (  SELECT ...                    -- 1. anchor: the starting rows  UNION ALL  SELECT ... FROM cte_name ...  -- 2. recursive part: new rows from the previous ones)SELECT * FROM cte_name;
  1. MySQL runs the anchor once.
  2. It runs the recursive part on the rows produced in the previous round.
  3. It repeats step 2 until a round produces no rows.
  4. All rounds together are the CTE’s result.

Count from 1 to 10

SQL
WITH RECURSIVE numbers AS (  SELECT 1 AS n  UNION ALL  SELECT n + 1 FROM numbers WHERE n < 10)SELECT n FROM numbers;
ResultMySQL 8.4
n
1
2
3
4
5
6
7
8
9
10
10 rows in set
How this query works, step by step
  1. The anchor produces one row: n = 1.
  2. Round 1 reads that row and produces n = 2; round 2 produces 3, and so on.
  3. When n reaches 10, WHERE n < 10 produces no new row, so the recursion stops.
  4. Without that WHERE the recursion would never end (MySQL stops it at 1,000 rounds with an error).

A date series

Every day from 1 to 10 March 2025, joined with the orders, so days without orders show 0:

MySQL
WITH RECURSIVE days AS (  SELECT DATE '2025-03-01' AS d  UNION ALL  SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2025-03-10')SELECT days.d, COUNT(o.order_id) AS ordersFROM daysLEFT JOIN orders o ON o.order_date = days.dGROUP BY days.dORDER BY days.d;
ResultMySQL 8.4
dorders
2025-03-010
2025-03-020
2025-03-030
2025-03-040
2025-03-050
2025-03-060
2025-03-070
2025-03-081
2025-03-090
2025-03-100
10 rows in set
How this query works, step by step
  1. The CTE generates 10 consecutive dates.
  2. The LEFT JOIN keeps every date, even those without orders.
  3. Only 8 March has an order (order 1005).

Walk the employee hierarchy

Everyone in the reporting line under the CEO, with their level:

SQL
WITH RECURSIVE org AS (  SELECT emp_id, first_name, manager_id, 1 AS level  FROM employees  WHERE manager_id IS NULL  UNION ALL  SELECT e.emp_id, e.first_name, e.manager_id, org.level + 1  FROM employees e  JOIN org ON e.manager_id = org.emp_id)SELECT emp_id, first_name, manager_id, levelFROM orgORDER BY level, emp_id;
ResultMySQL 8.4
emp_idfirst_namemanager_idlevel
100MeeraNULL1
101Asha1002
105Vikram1002
108Kavya1002
110Sneha1002
102Rahul1013
103Priya1013
106Neha1053
107Arjun1053
109Rohan1083
111Amit1013
104Karan1024
12 rows in set
How this query works, step by step
  1. The anchor finds the top of the tree: the employee with no manager (Meera, level 1).
  2. Round 1 joins employees to the rows found so far and finds people whose manager is Meera: Asha, Vikram, Kavya, Sneha (level 2).
  3. Round 2 finds their reports: Rahul, Priya, Amit, Neha, Arjun, Rohan (level 3).
  4. Round 3 finds Karan, who reports to Rahul (level 4). Round 4 finds nobody, and the recursion ends.

Show the full path

Carry a growing string through the recursion to build an org-chart path:

MySQL
WITH RECURSIVE org AS (  SELECT emp_id, first_name, CAST(first_name AS CHAR(200)) AS path, 0 AS depth  FROM employees  WHERE manager_id IS NULL  UNION ALL  SELECT e.emp_id, e.first_name, CONCAT(org.path, ' > ', e.first_name), org.depth + 1  FROM employees e  JOIN org ON e.manager_id = org.emp_id)SELECT CONCAT(REPEAT('    ', depth), first_name) AS org_chart, pathFROM orgORDER BY path;
ResultMySQL 8.4
org_chartpath
MeeraMeera
AshaMeera > Asha
AmitMeera > Asha > Amit
PriyaMeera > Asha > Priya
RahulMeera > Asha > Rahul
KaranMeera > Asha > Rahul > Karan
KavyaMeera > Kavya
RohanMeera > Kavya > Rohan
SnehaMeera > Sneha
VikramMeera > Vikram
ArjunMeera > Vikram > Arjun
NehaMeera > Vikram > Neha
12 rows in set
How this query works, step by step
  1. The anchor’s path is CAST(... AS CHAR(200)): the column type is fixed by the anchor, so it must be wide enough for the longest path.
  2. Each round appends > name to the parent’s path.
  3. Sorting by the path puts every person directly under their manager; REPEAT indents by depth.
Watch out

If the anchor column were just first_name (VARCHAR(30)), longer paths would fail with “Data too long”. Always widen string columns in the anchor of a recursive CTE.

Everyone under one manager

All employees below Asha (101), at any depth:

SQL
WITH RECURSIVE team AS (  SELECT emp_id, first_name FROM employees WHERE manager_id = 101  UNION ALL  SELECT e.emp_id, e.first_name FROM employees e JOIN team t ON e.manager_id = t.emp_id)SELECT * FROM team ORDER BY emp_id;
ResultMySQL 8.4
emp_idfirst_name
102Rahul
103Priya
104Karan
111Amit
4 rows in set

Limits and safety

  • MySQL stops after cte_max_recursion_depth rounds (default 1,000) with an error.
  • Always have a condition that ends the recursion (WHERE n < 10, or data that ends, like the bottom of a tree).
  • If the data could contain a loop (A reports to B, B reports to A), track the path and stop when an id is already in it.
MySQL
WITH RECURSIVE forever AS (  SELECT 1 AS n  UNION ALL  SELECT n + 1 FROM forever)SELECT COUNT(*) FROM forever;
ResultMySQL 8.4
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

Exercise: Powers of two

Generate the powers of two from 1 to 1024 (1, 2, 4, … 1024).

Expected resultMySQL 8.4
n
1
2
4
8
16
32
64
128
256
512
1024
11 rows in set
Hint 1

Anchor 1; recursive part n * 2 while n < 1024.

Quick quiz

1. What are the two parts of a recursive CTE?

2. When does the recursion stop?

3. Why cast the path column in the anchor to CHAR(200)?

Recursive CTE FAQ

What is a recursive CTE?

A CTE that references itself. It combines a starting query with a query that repeatedly builds new rows from the previous ones until no more rows are produced.

How do I query a hierarchy in MySQL?

Use WITH RECURSIVE: start from the top (or a chosen node) and join the table to the CTE on the parent column, adding a level or path column as you go.

What is the maximum recursion depth in MySQL?

cte_max_recursion_depth defaults to 1,000. You can raise it for a session with SET SESSION cte_max_recursion_depth = 5000;.

SQL Project: Library Management System

SQL Tutorial › SQL Projects › Project: Library Management System

SQL Project: Library Management System

ProjectProject 2 of 530 min read13 queries run on MySQL 8.4

A city library tracks its books, members and loans in MySQL. In this project you answer the questions the librarians ask every day: which copies are on the shelf, which loans are overdue, how much each member owes in fines, which books are popular, and you build a view and a stored procedure that issues books safely. The reference date for “today” is 30 June 2025.

In this lesson
  • Compute availability from loans that are still open
  • Find overdue loans and calculate fines with date functions
  • Rank popular books and active members
  • Package logic in a view and a transaction-safe stored procedure

The database

TableOne row perNotes
bookstitle in the cataloguecopies = how many physical copies the library owns
memberslibrary membermember_type: Student, Regular, Senior
loansone book lent to one memberreturn_date is NULL while the book is still out
books · 12 rows
book_idtitleauthorgenrepublished_yearcopies
1The Pragmatic ProgrammerHunt & ThomasTechnology19993
2Clean CodeRobert MartinTechnology20082
3Atomic HabitsJames ClearSelf-help20184
4SapiensYuval Noah HarariHistory20112
5The AlchemistPaulo CoelhoFiction19883
6Wings of FireA. P. J. Abdul KalamBiography19992
7The GuideR. K. NarayanFiction19581
8Thinking, Fast and SlowDaniel KahnemanPsychology20112
9Deep WorkCal NewportSelf-help20162
10A Brief History of TimeStephen HawkingScience19881
11The God of Small ThingsArundhati RoyFiction19972
12Lessons in TestingCem KanerTechnology20011
members · 10 rows
member_idfull_namemember_typejoined_on
1Rhea KulkarniSenior2024-04-26
2Om PrakashSenior2023-12-27
3Tara SenStudent2023-09-09
4Neil D'SouzaSenior2024-10-09
5Ira MalhotraStudent2024-01-06
6Dev ChauhanSenior2024-08-09
7Noor FatimaSenior2023-06-19
8Kunal BhattRegular2023-09-12
9Asha PillaiStudent2024-02-09
10Yash RawatStudent2024-08-18
loans · first 10 of 55 rows
loan_idbook_idmember_idloan_datedue_datereturn_date
1812025-01-052025-01-192025-01-17
2372025-01-052025-01-192025-01-10
3812025-01-132025-01-272025-01-29
4172025-01-172025-01-312025-02-02
51032025-01-202025-02-032025-02-03
63102025-01-212025-02-042025-02-10
7182025-01-272025-02-102025-02-10
8512025-02-162025-03-022025-02-25
9162025-02-202025-03-062025-03-01
10622025-02-212025-03-072025-03-09

Rules: a loan lasts 14 days (due_date); a late return costs ₹5 per day; a member may hold at most 3 books at a time.

library.sql
-- sqatools.in SQL project 2: library management system (generated by gen_projects.py)CREATE TABLE books (  book_id        INT PRIMARY KEY,  title          VARCHAR(60) NOT NULL,  author         VARCHAR(40) NOT NULL,  genre          VARCHAR(20) NOT NULL,  published_year INT,  copies         INT NOT NULL);CREATE TABLE members (  member_id   INT PRIMARY KEY,  full_name   VARCHAR(40) NOT NULL,  member_type VARCHAR(10) NOT NULL,  joined_on   DATE NOT NULL);CREATE TABLE loans (  loan_id     INT PRIMARY KEY,  book_id     INT NOT NULL,  member_id   INT NOT NULL,  loan_date   DATE NOT NULL,  due_date    DATE NOT NULL,  return_date DATE,  FOREIGN KEY (book_id) REFERENCES books (book_id),  FOREIGN KEY (member_id) REFERENCES members (member_id));INSERT INTO books VALUES(1, 'The Pragmatic Programmer', 'Hunt & Thomas', 'Technology', 1999, 3),(2, 'Clean Code', 'Robert Martin', 'Technology', 2008, 2),(3, 'Atomic Habits', 'James Clear', 'Self-help', 2018, 4),(4, 'Sapiens', 'Yuval Noah Harari', 'History', 2011, 2),(5, 'The Alchemist', 'Paulo Coelho', 'Fiction', 1988, 3),(6, 'Wings of Fire', 'A. P. J. Abdul Kalam', 'Biography', 1999, 2),(7, 'The Guide', 'R. K. Narayan', 'Fiction', 1958, 1),(8, 'Thinking, Fast and Slow', 'Daniel Kahneman', 'Psychology', 2011, 2),(9, 'Deep Work', 'Cal Newport', 'Self-help', 2016, 2),(10, 'A Brief History of Time', 'Stephen Hawking', 'Science', 1988, 1),(11, 'The God of Small Things', 'Arundhati Roy', 'Fiction', 1997, 2),(12, 'Lessons in Testing', 'Cem Kaner', 'Technology', 2001, 1);INSERT INTO members VALUES(1, 'Rhea Kulkarni', 'Senior', '2024-04-26'),(2, 'Om Prakash', 'Senior', '2023-12-27'),(3, 'Tara Sen', 'Student', '2023-09-09'),(4, 'Neil D''Souza', 'Senior', '2024-10-09'),(5, 'Ira Malhotra', 'Student', '2024-01-06'),(6, 'Dev Chauhan', 'Senior', '2024-08-09'),(7, 'Noor Fatima', 'Senior', '2023-06-19'),(8, 'Kunal Bhatt', 'Regular', '2023-09-12'),(9, 'Asha Pillai', 'Student', '2024-02-09'),(10, 'Yash Rawat', 'Student', '2024-08-18');INSERT INTO loans VALUES(1, 8, 1, '2025-01-05', '2025-01-19', '2025-01-17'),(2, 3, 7, '2025-01-05', '2025-01-19', '2025-01-10'),(3, 8, 1, '2025-01-13', '2025-01-27', '2025-01-29'),(4, 1, 7, '2025-01-17', '2025-01-31', '2025-02-02'),(5, 10, 3, '2025-01-20', '2025-02-03', '2025-02-03'),(6, 3, 10, '2025-01-21', '2025-02-04', '2025-02-10'),(7, 1, 8, '2025-01-27', '2025-02-10', '2025-02-10'),(8, 5, 1, '2025-02-16', '2025-03-02', '2025-02-25'),(9, 1, 6, '2025-02-20', '2025-03-06', '2025-03-01'),(10, 6, 2, '2025-02-21', '2025-03-07', '2025-03-09'),(11, 4, 5, '2025-02-21', '2025-03-07', '2025-03-05'),(12, 3, 4, '2025-02-22', '2025-03-08', '2025-03-03'),(13, 5, 6, '2025-02-22', '2025-03-08', '2025-03-06'),(14, 2, 9, '2025-02-24', '2025-03-10', '2025-03-01'),(15, 10, 10, '2025-02-26', '2025-03-12', '2025-03-12'),(16, 6, 3, '2025-02-27', '2025-03-13', '2025-03-13'),(17, 10, 5, '2025-02-27', '2025-03-13', '2025-03-15'),(18, 7, 3, '2025-03-04', '2025-03-18', '2025-03-20'),(19, 5, 3, '2025-03-06', '2025-03-20', '2025-03-20'),(20, 5, 3, '2025-03-07', '2025-03-21', '2025-04-03'),(21, 1, 7, '2025-03-09', '2025-03-23', '2025-03-14'),(22, 11, 3, '2025-03-15', '2025-03-29', '2025-03-28'),(23, 4, 5, '2025-03-17', '2025-03-31', '2025-04-02'),(24, 7, 4, '2025-03-21', '2025-04-04', '2025-04-06'),(25, 7, 10, '2025-03-24', '2025-04-07', '2025-04-02'),(26, 9, 7, '2025-03-26', '2025-04-09', '2025-04-07'),(27, 8, 6, '2025-03-30', '2025-04-13', '2025-04-13'),(28, 9, 10, '2025-03-31', '2025-04-14', '2025-04-16'),(29, 8, 4, '2025-04-01', '2025-04-15', '2025-04-06'),(30, 10, 4, '2025-04-10', '2025-04-24', '2025-04-30'),(31, 1, 5, '2025-04-15', '2025-04-29', '2025-05-01'),(32, 9, 5, '2025-04-21', '2025-05-05', '2025-05-18'),(33, 1, 10, '2025-04-27', '2025-05-11', '2025-05-13'),(34, 11, 9, '2025-05-01', '2025-05-15', '2025-05-13'),(35, 6, 8, '2025-05-01', '2025-05-15', '2025-05-13'),(36, 4, 7, '2025-05-01', '2025-05-15', '2025-05-06'),(37, 5, 8, '2025-05-02', '2025-05-16', '2025-05-29'),(38, 7, 6, '2025-05-03', '2025-05-17', '2025-05-15'),(39, 7, 10, '2025-05-06', '2025-05-20', '2025-05-22'),(40, 11, 2, '2025-05-09', '2025-05-23', '2025-05-29'),(41, 9, 10, '2025-05-12', '2025-05-26', '2025-06-01'),(42, 8, 7, '2025-05-19', '2025-06-02', '2025-05-28'),(43, 11, 8, '2025-05-22', '2025-06-05', '2025-05-31'),(44, 2, 10, '2025-05-23', '2025-06-06', '2025-06-19'),(45, 5, 3, '2025-05-25', '2025-06-08', '2025-06-06'),(46, 3, 1, '2025-06-02', '2025-06-16', '2025-06-16'),(47, 8, 8, '2025-06-09', '2025-06-23', '2025-06-23'),(48, 10, 7, '2025-06-09', '2025-06-23', NULL),(49, 5, 4, '2025-06-13', '2025-06-27', NULL),(50, 10, 3, '2025-06-14', '2025-06-28', '2025-06-19'),(51, 7, 8, '2025-06-19', '2025-07-03', NULL),(52, 1, 2, '2025-06-21', '2025-07-05', NULL),(53, 4, 4, '2025-06-21', '2025-07-05', NULL),(54, 4, 5, '2025-06-22', '2025-07-06', NULL),(55, 2, 6, '2025-06-25', '2025-07-09', NULL);

Task 1: Catalogue overview

SQL
SELECT genre, COUNT(*) AS titles, SUM(copies) AS copiesFROM booksGROUP BY genreORDER BY copies DESC, genre;
ResultMySQL 8.4
genretitlescopies
Fiction36
Self-help26
Technology36
Biography12
History12
Psychology12
Science11
7 rows in set

Task 2: Copies available right now

Available = copies owned − loans that are still open:

SQL
SELECT b.book_id, b.title, b.copies,       COUNT(l.loan_id)            AS on_loan,       b.copies - COUNT(l.loan_id) AS availableFROM books bLEFT JOIN loans l ON l.book_id = b.book_id AND l.return_date IS NULLGROUP BY b.book_id, b.title, b.copiesORDER BY available, b.title;
ResultMySQL 8.4
book_idtitlecopieson_loanavailable
10A Brief History of Time110
4Sapiens220
7The Guide110
2Clean Code211
12Lessons in Testing101
9Deep Work202
5The Alchemist312
11The God of Small Things202
1The Pragmatic Programmer312
8Thinking, Fast and Slow202
6Wings of Fire202
3Atomic Habits404
12 rows in set
How this query works, step by step
  1. The LEFT JOIN attaches only open loans (return_date IS NULL); the condition is in ON so books with no open loan are kept.
  2. COUNT(l.loan_id) counts the open loans of each book (0 if none).
  3. Subtracting from copies gives the copies on the shelf. Books with 0 available come first.

Task 3: Overdue loans today

MySQL
SELECT l.loan_id, m.full_name, b.title, l.due_date,       DATEDIFF('2025-06-30', l.due_date) AS days_overdueFROM loans lJOIN members m ON m.member_id = l.member_idJOIN books b   ON b.book_id = l.book_idWHERE l.return_date IS NULL  AND l.due_date < '2025-06-30'ORDER BY days_overdue DESC;
ResultMySQL 8.4
loan_idfull_nametitledue_datedays_overdue
48Noor FatimaA Brief History of Time2025-06-237
49Neil D'SouzaThe Alchemist2025-06-273
2 rows in set
How this query works, step by step
  1. A loan is overdue when it is still open and its due date has passed.
  2. DATEDIFF(today, due_date) gives the number of days late.
  3. In a live system replace '2025-06-30' with CURDATE().

Task 4: Fines per member

Late days are counted up to the return date for returned books, and up to today for books still out:

MySQL
WITH late AS (  SELECT l.member_id,         GREATEST(DATEDIFF(COALESCE(l.return_date, '2025-06-30'), l.due_date), 0) AS days_late  FROM loans l)SELECT m.member_id, m.full_name,       SUM(late.days_late > 0) AS late_loans,       SUM(late.days_late) * 5 AS fine_rupeesFROM lateJOIN members m ON m.member_id = late.member_idGROUP BY m.member_id, m.full_nameHAVING fine_rupees > 0ORDER BY fine_rupees DESC;
ResultMySQL 8.4
member_idfull_namelate_loansfine_rupees
10Yash Rawat6155
5Ira Malhotra495
3Tara Sen275
8Kunal Bhatt165
4Neil D'Souza355
7Noor Fatima245
2Om Prakash240
1Rhea Kulkarni110
8 rows in set
How this query works, step by step
  1. COALESCE(return_date, '2025-06-30') uses today for loans that are still open.
  2. DATEDIFF(that date, due_date) counts the days after the due date; a negative number means the book came back early.
  3. GREATEST(..., 0) turns early returns into 0 days late.
  4. The fine is the total late days × ₹5; HAVING keeps only members who owe something.
SQL
SELECT b.title, b.genre, COUNT(*) AS loans,       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS popularity_rankFROM loans lJOIN books b ON b.book_id = l.book_idGROUP BY b.book_id, b.title, b.genreORDER BY loans DESC, b.titleLIMIT 5;
ResultMySQL 8.4
titlegenreloanspopularity_rank
The AlchemistFiction71
The Pragmatic ProgrammerTechnology71
A Brief History of TimeScience62
The GuideFiction62
Thinking, Fast and SlowPsychology62
5 rows in set

A window function can rank the grouped rows: DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) runs after GROUP BY.

Task 6: Books nobody has borrowed

SQL
SELECT b.book_id, b.title, b.authorFROM books bWHERE NOT EXISTS (SELECT 1 FROM loans l WHERE l.book_id = b.book_id);
ResultMySQL 8.4
book_idtitleauthor
12Lessons in TestingCem Kaner
1 row in set

Task 7: Member activity

MySQL
SELECT m.member_type,       COUNT(DISTINCT m.member_id) AS members,       COUNT(l.loan_id)            AS loans,       ROUND(COUNT(l.loan_id) / COUNT(DISTINCT m.member_id), 1) AS loans_per_memberFROM members mLEFT JOIN loans l ON l.member_id = m.member_idGROUP BY m.member_typeORDER BY loans_per_member DESC;
ResultMySQL 8.4
member_typemembersloansloans_per_member
Regular166.0
Student4246.0
Senior5255.0
3 rows in set

Task 8: Average loan length by genre

MySQL
SELECT b.genre,       COUNT(*) AS returned_loans,       ROUND(AVG(DATEDIFF(l.return_date, l.loan_date)), 1) AS avg_days_kept,       SUM(l.return_date > l.due_date) AS returned_lateFROM loans lJOIN books b ON b.book_id = l.book_idWHERE l.return_date IS NOT NULLGROUP BY b.genreORDER BY avg_days_kept DESC;
ResultMySQL 8.4
genrereturned_loansavg_days_keptreturned_late
Self-help815.44
Fiction1514.96
Biography314.01
Science513.82
Technology813.54
Psychology611.71
History311.01
7 rows in set

Task 9: Loans per month

MySQL
SELECT DATE_FORMAT(loan_date, '%Y-%m') AS month,       COUNT(*) AS loans,       COUNT(DISTINCT member_id) AS active_membersFROM loansGROUP BY monthORDER BY month;
ResultMySQL 8.4
monthloansactive_members
2025-0175
2025-02108
2025-03116
2025-0453
2025-05127
2025-06108
6 rows in set

Task 10: Members at their borrowing limit

MySQL
SELECT m.member_id, m.full_name, COUNT(*) AS books_out,       GROUP_CONCAT(b.title ORDER BY l.loan_date SEPARATOR '; ') AS titlesFROM loans lJOIN members m ON m.member_id = l.member_idJOIN books b   ON b.book_id = l.book_idWHERE l.return_date IS NULLGROUP BY m.member_id, m.full_nameHAVING COUNT(*) >= 2ORDER BY books_out DESC;
ResultMySQL 8.4
member_idfull_namebooks_outtitles
4Neil D'Souza2The Alchemist; Sapiens
1 row in set

Task 11: A view for the front desk

Save the availability query as a view, so the desk application only has to SELECT from it:

SQL
CREATE VIEW available_books ASSELECT b.book_id, b.title, b.author,       b.copies - COUNT(l.loan_id) AS availableFROM books bLEFT JOIN loans l ON l.book_id = b.book_id AND l.return_date IS NULLGROUP BY b.book_id, b.title, b.author, b.copies; SELECT * FROM available_books WHERE available > 0 AND title LIKE '%Habit%';
ResultMySQL 8.4
mysql> CREATE VIEW available_books AS
Query OK, 0 rows affected
mysql> SELECT * FROM available_books WHERE available > 0 AND title LIKE '%Hab…
book_idtitleauthoravailable
3Atomic HabitsJames Clear4
1 row in set

Task 12: Issue a book safely (stored procedure)

Issuing a book must check two rules and then insert the loan, as one unit of work:

MySQL
DELIMITER //CREATE PROCEDURE issue_book(IN p_book_id INT, IN p_member_id INT, IN p_date DATE)BEGIN  DECLARE v_available INT;  DECLARE v_out INT;  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;   START TRANSACTION;  SELECT b.copies - (SELECT COUNT(*) FROM loans WHERE book_id = p_book_id AND return_date IS NULL)    INTO v_available    FROM books b WHERE b.book_id = p_book_id FOR UPDATE;  SELECT COUNT(*) INTO v_out FROM loans WHERE member_id = p_member_id AND return_date IS NULL;   IF v_available IS NULL OR v_available < 1 THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No copy available';  END IF;  IF v_out >= 3 THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Member already has 3 books';  END IF;   INSERT INTO loans (loan_id, book_id, member_id, loan_date, due_date, return_date)  SELECT MAX(loan_id) + 1, p_book_id, p_member_id, p_date, p_date + INTERVAL 14 DAY, NULL FROM loans;  COMMIT;END //DELIMITER ; CALL issue_book(12, 1, '2025-06-30');SELECT * FROM loans ORDER BY loan_id DESC LIMIT 1;
ResultMySQL 8.4
mysql> CREATE PROCEDURE issue_book(IN p_book_id INT, IN p_member_id INT, IN p…
Query OK, 0 rows affected
mysql> CALL issue_book(12, 1, '2025-06-30')
Query OK, 0 rows affected
mysql> SELECT * FROM loans ORDER BY loan_id DESC LIMIT 1
loan_idbook_idmember_idloan_datedue_datereturn_date
561212025-06-302025-07-14NULL
1 row in set
How this query works, step by step
  1. FOR UPDATE locks the book row, so two librarians cannot hand out the last copy at the same moment.
  2. Two SIGNALs enforce the rules: a copy must be free, and the member must hold fewer than 3 books.
  3. If anything fails, the handler rolls back and passes the error on.
  4. Book 12 had never been borrowed, so member 1 can take it: a new open loan due in 14 days.

Asking for a book with no copy left fails, and nothing is inserted:

MySQL
CALL issue_book(12, 2, '2025-06-30');
ResultMySQL 8.4
ERROR 1644 (45000): No copy available

Your turn

Exercise: Members with nothing borrowed right now

List members who currently have no book out (no open loan).

Expected resultMySQL 8.4
member_idfull_name
1Rhea Kulkarni
3Tara Sen
9Asha Pillai
10Yash Rawat
4 rows in set
Hint 1

NOT EXISTS with a subquery on open loans (return_date IS NULL).

Exercise: Late returns per book

For each book, count how many loans were returned after the due date.

Expected resultMySQL 8.4
titlelate_returns
Deep Work3
The Guide3
The Pragmatic Programmer3
A Brief History of Time2
The Alchemist2
Atomic Habits1
Clean Code1
Sapiens1
The God of Small Things1
Thinking, Fast and Slow1
Wings of Fire1
11 rows in set
Hint 1

Filter returned loans with return_date > due_date, group by title.

What you practised

TaskSQL skills
Availability, front-desk viewLEFT JOIN with a condition in ON, COUNT, views
Overdue loans and finesDATEDIFF, COALESCE, GREATEST, CTE, HAVING
Popular books, membersGROUP BY, DENSE_RANK() OVER, GROUP_CONCAT
Never borrowedNOT EXISTS, anti-join
Issue bookstored procedure, SIGNAL, transaction, FOR UPDATE

Project FAQ

What tables does a library management system database need?

At least books (the catalogue), members, and loans that link a book to a member with loan, due and return dates. Larger systems add copies, reservations, fines and staff.

How do you calculate overdue fines in SQL?

Compute the days between the due date and the return date (or today, for open loans) with DATEDIFF, clamp negatives to zero with GREATEST, and multiply by the daily rate.

How do you check book availability in SQL?

Subtract the number of open loans (return_date IS NULL) from the number of copies, using a LEFT JOIN so books with no loans are included.