Intel's semiconductor manufacturing team wants to identify fabrication lots with unusually low die-test yields. For lots tested during the first quarter of 2025, calculate the total number of tested dies, the number of passing dies, the yield rate, and the average leakage current. A die with `test_result = 'PASS'` is passing. A `NULL` test result is inconclusive: it still counts as a tested die but does not count as passing. `NULL` leakage readings must be ignored when calculating the average. Return only lots with at least 5 tested dies and a yield rate below 90%. Return the columns in this exact order: `lot_id`, `process_node`, `tested_dies`, `passing_dies`, `yield_rate`, and `average_leakage_ua`. Round `yield_rate` to four decimal places and `average_leakage_ua` to two decimal places. Sort by `yield_rate` descending, then `lot_id` descending. ### Table Schema | Table | Column | Type | |---|---|---| | `fab_lots` | `lot_id` | integer | | `fab_lots` | `process_node` | varchar(20) | | `fab_lots` | `fab_site` | varchar(30) | | `die_tests` | `test_id` | integer | | `die_tests` | `lot_id` | integer | | `die_tests` | `tested_at` | timestamp | | `die_tests` | `test_result` | varchar(10) | | `die_tests` | `leakage_ua` | decimal(10,2) | ### Example Input: `fab_lots` | lot_id | process_node | fab_site | |---:|---|---| | 1 | Intel 4 | Arizona | | 2 | Intel 3 | Oregon | | 3 | Intel 7 | Ireland | ### Example Input: `die_tests` | test_id | lot_id | tested_at | test_result | leakage_ua | |---:|---:|---|---|---:| | 101 | 1 | 2025-01-04 08:00:00 | PASS | 2.10 | | 102 | 1 | 2025-01-04 08:05:00 | PASS | 2.40 | | 103 | 1 | 2025-01-04 08:10:00 | FAIL | 8.00 | | 104 | 1 | 2025-01-04 08:15:00 | PASS | NULL | | 105 | 1 | 2025-01-04 08:20:00 | FAIL | 9.20 | | 106 | 2 | 2025-02-10 09:00:00 | PASS | 1.10 | | 107 | 2 | 2025-02-10 09:05:00 | FAIL | 7.50 | | 108 | 2 | 2025-02-10 09:10:00 | FAIL | 8.20 | | 109 | 2 | 2025-02-10 09:15:00 | FAIL | 8.80 | | 110 | 2 | 2025-02-10 09:20:00 | FAIL | 9.00 | | 111 | 3 | 2025-03-02 10:00:00 | FAIL | 5.00 | | 112 | 3 | 2025-03-02 10:05:00 | PASS | 2.00 | ### Example Output Explanation Lot 1 has five tested dies and three passing dies, producing a yield rate of 0.6000. Lot 2 has five tested dies and one passing die, producing a yield rate of 0.2000. Lot 3 has fewer than five tested dies and is excluded.
DROP TABLE IF EXISTS die_tests; DROP TABLE IF EXISTS fab_lots; CREATE TABLE fab_lots ( lot_id INT, process_node VARCHAR(20), fab_site VARCHAR(30) ); CREATE TABLE die_tests ( test_id INT, lot_id INT, tested_at TIMESTAMP, test_result VARCHAR(10), leakage_ua DECIMAL(10,2) ); INSERT INTO fab_lots VALUES (1,'Intel 4','Arizona'), (2,'Intel 3','Oregon'), (3,'Intel 7','Ireland'); INSERT INTO die_tests VALUES (101,1,'2025-01-04 08:00:00','PASS',2.10), (102,1,'2025-01-04 08:05:00','PASS',2.40), (103,1,'2025-01-04 08:10:00','FAIL',8.00), (104,1,'2025-01-04 08:15:00','PASS',NULL), (105,1,'2025-01-04 08:20:00','FAIL',9.20), (106,2,'2025-02-10 09:00:00','PASS',1.10), (107,2,'2025-02-10 09:05:00','FAIL',7.50), (108,2,'2025-02-10 09:10:00','FAIL',8.20), (109,2,'2025-02-10 09:15:00','FAIL',8.80), (110,2,'2025-02-10 09:20:00','FAIL',9.00), (111,3,'2025-03-02 10:00:00','FAIL',5.00), (112,3,'2025-03-02 10:05:00','PASS',2.00);