t100: routes_by_year

Custom SQL query (hide)

SELECT
  dest,
  MAX(dest_city_market_id) AS market_id,
  MAX(dest_city_name) AS city,
  year,
  SUM(departures_performed) AS deps,
  SUM(seats) AS seats,
  SUM(passengers) AS passengers,
  SUM(freight) AS freight,
  CASE WHEN SUM(CASE WHEN domestic = 1 THEN departures_performed ELSE 0 END) * 2
            > SUM(departures_performed)
       THEN 'domestic' ELSE 'international' END AS service
FROM t100
WHERE origin = :origin
GROUP BY dest, year
HAVING SUM(departures_performed) >= CAST(COALESCE(NULLIF(:min_deps, ''), 12) AS integer)
  AND (SUM(departures_performed) > 0 OR SUM(passengers) > 0 OR SUM(freight) > 0)
ORDER BY year, dest