Fiber Optic Test Results SQL Schema and Pass/Fail Query Generator

CREATE TABLE fiber_test_result (
  test_id        INTEGER PRIMARY KEY,
  serial_no      VARCHAR(40) NOT NULL,
  work_order     VARCHAR(30),
  part_no        VARCHAR(40),
  fiber_no       INTEGER,
  wavelength_nm  INTEGER NOT NULL,
  insertion_loss_db DECIMAL(6,3),
  return_loss_db    DECIMAL(6,2),
  length_m       DECIMAL(10,2),
  operator_id    VARCHAR(20),
  meter_serial   VARCHAR(40),
  tested_at      TIMESTAMP NOT NULL
);

CREATE INDEX ix_fiber_test_result_serial ON fiber_test_result (serial_no);
CREATE INDEX ix_fiber_test_result_date ON fiber_test_result (tested_at);

INSERT INTO fiber_test_result (test_id, serial_no, work_order, part_no, fiber_no, wavelength_nm, insertion_loss_db, return_loss_db, length_m, operator_id, meter_serial, tested_at)
VALUES (1, 'SN-000101', 'WO-5521', 'JMP-LC-SM-2M', 1, 1310, 0.12, 54.5, 2.0, 'OP17', 'PM-3302', '2026-10-01 09:15:00');

SELECT serial_no, fiber_no, wavelength_nm, insertion_loss_db, return_loss_db, tested_at
FROM fiber_test_result
WHERE wavelength_nm = 1310
  AND tested_at >= '2026-10-01' AND tested_at < '2026-10-31'
  AND (insertion_loss_db > 0.30 OR return_loss_db < 50)
ORDER BY tested_at;

SELECT CAST(tested_at AS DATE) AS test_day,
       COUNT(*) AS tests,
       SUM(CASE WHEN insertion_loss_db <= 0.30 AND return_loss_db >= 50 THEN 1 ELSE 0 END) AS passed,
       100.0 * SUM(CASE WHEN insertion_loss_db <= 0.30 AND return_loss_db >= 50 THEN 1 ELSE 0 END) / COUNT(*) AS pass_pct
FROM fiber_test_result
WHERE wavelength_nm = 1310
  AND tested_at >= '2026-10-01' AND tested_at < '2026-10-31'
GROUP BY CAST(tested_at AS DATE)
ORDER BY test_day;

SELECT operator_id,
       COUNT(*) AS tests,
       AVG(insertion_loss_db) AS avg_il_db,
       MAX(insertion_loss_db) AS max_il_db,
       MIN(return_loss_db) AS min_rl_db
FROM fiber_test_result
WHERE wavelength_nm = 1310
  AND tested_at >= '2026-10-01' AND tested_at < '2026-10-31'
GROUP BY operator_id
ORDER BY avg_il_db DESC;