Review a network revenue query that NULLs quietly bend

An analyst (simulated) asks you to review a query before its numbers go into the 2023 network review deck. It runs on flight_day_raw in the course database (synthetic; grain intended to be one row per flight_no x date). Recall from Day 6 that the raw copy has about 0.8% duplicate rows (half of them with a late revenue update), missing avg_fare/revenue that is more common on intl-long, and a few impossible values.

SELECT market,
       COUNT(*)                                               AS flight_days,
       SUM(revenue)                                           AS revenue,
       ROUND(SUM(revenue) / SUM(capacity), 2)                 AS revenue_per_seat,
       ROUND(AVG(avg_fare), 2)                                AS avg_fare,
       SUM(CASE WHEN load_factor >= 0.95 THEN 1 ELSE 0 END) / COUNT(*) AS share_near_full
FROM flight_day_raw
WHERE date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY market;

Its output (computed on the course database):

market	flight_days	revenue	revenue_per_seat	avg_fare	share_near_full
domestic	4005	48,512,369	69.37	90.20	0
intl-long	730	91,047,541	445.44	602.94	0
intl-short	2197	60,050,178	157.71	198.42	0