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

SQL Project: QA Test Results Tracker

Q: 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.

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

Table	One row per	Notes
`test_suites`	suite	`platform`: Web, API, Mobile
`test_cases`	test case	`priority` P1-P3, `automated` 1 or 0
`test_runs`	nightly run	the `build` it tested, `environment`
`test_results`	case in a run	`status`: PASSED, FAILED, SKIPPED; `duration_ms` NULL when skipped
`defects`	bug linked to a case	`severity`, `status`, `opened_on`, `closed_on`

test_suites · 5 rows

suite_id	suite_name	platform
1	Login & Auth	Web
2	Checkout	Web
3	Search	Web
4	Payments API	API
5	Mobile Smoke	Mobile

test_runs · 6 rows

run_id	build	started_at	environment
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

test_cases · 22 rows

case_id	suite_id	title	priority	automated
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

test_results · first 10 of 126 rows

run_id	case_id	status	duration_ms
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

defects · 6 rows

defect_id	case_id	severity	status	opened_on	closed_on
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

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);

— 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_id	build	environment	executed	passed	failed	pass_rate
1	2.4.0	staging	21	18	3	85.7
2	2.4.1	staging	15	14	1	93.3
3	2.5.0	uat	21	20	1	95.2
4	2.5.1	staging	15	14	1	93.3
5	2.6.0	staging	21	18	3	85.7
6	2.6.1	uat	21	18	3	85.7

6 rows in set

How this query works, step by step

Skipped tests are excluded: pass rate is usually measured over tests that actually ran.
SUM(status = 'PASSED') counts passes because a true comparison is 1.
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_name	platform	executed	pass_rate
Payments API	API	4	75.0
Search	Web	4	75.0
Checkout	Web	5	80.0
Login & Auth	Web	5	100.0
Mobile Smoke	Mobile	3	100.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_id	suite_name	title	priority	duration_ms
13	Search	Sort by rating	P2	2514
7	Checkout	Apply coupon	P3	7986
18	Payments API	Idempotency key	P3	3983

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_id	build	title	previous_status	status
3	2.5.0	Guest checkout	PASSED	FAILED
4	2.5.1	Sort by rating	PASSED	FAILED
5	2.6.0	Apply coupon	PASSED	FAILED
5	2.6.0	Idempotency key	PASSED	FAILED
5	2.6.0	Refund payment	PASSED	FAILED
6	2.6.1	Sort by rating	PASSED	FAILED

6 rows in set

How this query works, step by step

PARTITION BY case_id ORDER BY run_id makes a timeline per test.
LAG(status) reads the test’s status in the run before.
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_id	title	flips	failures	runs
13	Sort by rating	4	3	6
7	Apply coupon	2	4	6
9	Guest checkout	2	1	6
16	Refund payment	2	1	6

4 rows in set

How this query works, step by step

LAG compares each result with the previous executed result of the same test.
SUM(status <> prev) counts changes. The first row’s prev is NULL, so the comparison is NULL and adds nothing.
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

title	platform	timed_runs	avg_seconds	max_seconds
Push notification	Mobile	6	31.3	34.0
Biometric login	Mobile	6	31.3	33.7
App launch	Mobile	4	28.6	30.1
Refund payment	API	6	7.3	8.8
Remove item	Web	4	7.0	8.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_id	build	total_minutes	change_minutes
1	2.4.0	3.1	NULL
2	2.4.1	1.8	-1.2
3	2.5.0	2.9	1.0
4	2.5.1	2.3	-0.6
5	2.6.0	3.3	1.0
6	2.6.1	3.3	0.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_name	cases	automated	automation_pct
Checkout	5	3	60
Login & Auth	5	3	60
Search	5	3	60
Mobile Smoke	3	2	67
Payments API	4	4	100

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_id	title	automated
22	Voice search	0

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

title	priority	last_three
Sort by rating	P2	run 4: FAILED, run 5: PASSED, run 6: FAILED
Refund payment	P2	run 4: PASSED, run 5: FAILED, run 6: PASSED

2 rows in set

How this query works, step by step

run_id > MAX(run_id) - 3 keeps the three most recent runs.
GROUP_CONCAT lists each test’s recent history in one cell.
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

severity	total	still_open	avg_days_to_fix
Critical	2	1	1.0
Major	2	1	2.0
Minor	2	1	6.0

3 rows in set

How this query works, step by step

AVG(DATEDIFF(closed_on, opened_on)) uses only closed defects, because DATEDIFF with a NULL closed_on is NULL and AVG skips it.
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_id	severity	title	opened_on	days_open
1	Critical	Idempotency key	2025-09-29	7
2	Major	Apply coupon	2025-09-10	26

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_id	title	run_4	run_5	run_6
6	Add to cart	S	P	P
7	Apply coupon	P	F	F
8	Remove item	S	P	P
9	Guest checkout	P	P	P
10	Order confirmation email	P	P	P
15	Create payment 201	P	P	P
16	Refund payment	P	F	P
17	Invalid card 402	P	P	P
18	Idempotency key	P	F	F

9 rows in set

How this query works, step by step

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).
LEFT(status, 1) shortens PASSED / FAILED / SKIPPED to P / F / S.
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

platform	failures
Web	9
API	3

2 rows in set

Hint 1

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

SQL

SELECT s.platform, COUNT(*) AS failuresFROM test_results trJOIN test_cases c  ON c.case_id = tr.case_idJOIN test_suites s ON s.suite_id = c.suite_idWHERE tr.status = 'FAILED'GROUP BY s.platformORDER BY failures DESC;

ResultMySQL 8.4

platform	failures
Web	9
API	3

2 rows in set

Exercise: Tests that never failed

List automated tests that passed every time they ran.

Expected resultMySQL 8.4

case_id	title
1	Valid login
2	Invalid password
4	Password reset email
10	Order confirmation email
11	Search by keyword
15	Create payment 201
17	Invalid card 402
20	Biometric login
21	Push notification

9 rows in set

Hint 1

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

SQL

SELECT c.case_id, c.titleFROM test_cases cJOIN test_results tr ON tr.case_id = c.case_idWHERE c.automated = 1GROUP BY c.case_id, c.titleHAVING SUM(tr.status = 'FAILED') = 0ORDER BY c.case_id;

ResultMySQL 8.4

case_id	title
1	Valid login
2	Invalid password
4	Password reset email
10	Order confirmation email
11	Search by keyword
15	Create payment 201
17	Invalid card 402
20	Biometric login
21	Push notification

9 rows in set

What you practised

Task	SQL skills
Pass rates, coverage, defects	conditional `SUM`, `ROUND`, `AVG(DATEDIFF)`, `FIELD`
Latest run reports	scalar subquery `(SELECT MAX(run_id) ...)`
Regressions, flaky tests, run trend	`LAG() OVER (PARTITION BY ... ORDER BY ...)`
Never executed	`LEFT JOIN ... IS NULL`
Results matrix	pivot 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.