t100: routes_by_month

Custom SQL query (hide)

SELECT
  dest,
  month,
  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 AND year = :year
  AND dest IN (
    SELECT dest FROM t100
    WHERE origin = :origin AND year = :year
    GROUP BY dest
    HAVING SUM(departures_performed) >= CAST(COALESCE(NULLIF(:min_deps, ''), 12) AS integer)
  )
GROUP BY dest, month
HAVING SUM(departures_performed) > 0 OR SUM(passengers) > 0 OR SUM(freight) > 0
ORDER BY month, dest