SQLize Online / PHPize Online  /  SQLtest Online

WITH tmp AS ( SELECT COUNT(EXTRACT(EPOCH FROM (actual_departure - scheduled_departure)) / 60 > 30 OR NULL) AS count_delayed, COUNT(*) AS count_departured, departure_airport FROM flights GROUP BY departure_airport HAVING COUNT(*) > 100 ) SELECT DENSE_RANK() OVER (ORDER BY count_delayed) airport_rank, airport_name->>'en' AS airport_name, ROUND(count_delayed::NUMERIC * 100 / count_departured::NUMERIC, 2) AS delayed_flights_rate FROM tmp JOIN airports_data ad ON tmp.departure_airport = ad.airport_code