SELECT
    SUBQ1.week_number AS week_number,
    SUBQ1.ticket_amount AS ticket_amount,
    SUBQ2.festival_week AS festival_week,
    SUBQ2.festival_name AS festival_name
FROM

(SELECT
    EXTRACT(WEEK FROM CAST(flights.arrival_time AS DATE)) AS week_number,
    COUNT(ticket_flights.ticket_no) as ticket_amount
FROM
    flights
INNER JOIN ticket_flights ON ticket_flights.flight_id = flights.flight_id
WHERE
    (EXTRACT(WEEK FROM CAST(flights.departure_time AS DATE)) BETWEEN '30' AND '36') AND (flights.arrival_airport = 'SVO' OR flights.arrival_airport = 'VKO' OR flights.arrival_airport = 'DME' OR flights.arrival_airport = 'ZIA')
GROUP BY
    week_number) AS SUBQ1

LEFT JOIN

(SELECT
    festival_name AS festival_name,
    EXTRACT(WEEK FROM festival_date) AS festival_week
FROM
    festivals
WHERE
    (CAST(festival_date AS DATE) BETWEEN '2018-07-23' AND '2018-09-30') AND festival_city = 'Москва') AS SUBQ2

ON SUBQ2.festival_week = SUBQ1.week_number
ORDER BY
    week_number;