Food Lot Traceability SQL Generator: One Step Forward, One Step Back

WITH RECURSIVE back_trace AS (
  SELECT input_lot AS lot_no, output_lot AS used_in, qty_consumed AS qty, 1 AS depth
  FROM lot_genealogy
  WHERE output_lot = 'FG-240918-03'
  UNION ALL
  SELECT g.input_lot, g.output_lot, g.qty_consumed, b.depth + 1
  FROM lot_genealogy g
  JOIN back_trace b ON g.output_lot = b.lot_no
  WHERE b.depth < 10
)
SELECT lot_no, used_in, qty, depth
FROM back_trace
ORDER BY depth, lot_no;

WITH RECURSIVE fwd_trace AS (
  SELECT output_lot AS lot_no, 1 AS depth
  FROM lot_genealogy
  WHERE input_lot = 'FG-240918-03'
  UNION ALL
  SELECT g.output_lot, f.depth + 1
  FROM lot_genealogy g
  JOIN fwd_trace f ON g.input_lot = f.lot_no
  WHERE f.depth < 10
)
SELECT DISTINCT s.customer_id, s.lot_no, s.ship_date
FROM shipment_lines s
WHERE s.lot_no = 'FG-240918-03'
   OR s.lot_no IN (SELECT lot_no FROM fwd_trace)
ORDER BY s.ship_date, s.customer_id;

-- Start the clock when you run the queries, stop when the customer list is verified.
-- Record: lot traced = FG-240918-03, query run time, rows returned, reconciliation of produced vs shipped vs on hand.