1/
SELECT
	aircrafts.model, 
	sum(flights.actual_arrival-flights.actual_departure)
FROM
	flights
	INNER JOIN
	aircrafts
	ON 
		flights.aircraft_code = aircrafts.aircraft_code
WHERE
	flights.status = 'Arrived'
GROUP BY
	aircrafts.aircraft_code



2/
SELECT
	bookings.book_ref, 
	COUNT(boarding_passes.ticket_no)
FROM
	bookings
	LEFT JOIN
	tickets
	ON 
		bookings.book_ref = tickets.book_ref
	NATURAL LEFT JOIN
	boarding_passes
	LEFT JOIN
	ticket_flights
	ON 
		boarding_passes.flight_id = ticket_flights.flight_id AND
		tickets.ticket_no = ticket_flights.ticket_no
GROUP BY
	bookings.book_ref



3/
SELECT
	ticket_flights.fare_conditions, 
	SUM(ticket_flights.amount)
FROM
	ticket_flights
GROUP BY
	ticket_flights.fare_conditions


4/
SELECT
	routes.flight_no, 
	sum(ticket_flights.amount) AS oborot
FROM
	routes
	LEFT JOIN
	flights
	ON 
		routes.flight_no = flights.flight_no
	LEFT JOIN
	ticket_flights
	ON 
		flights.flight_id = ticket_flights.flight_id
GROUP BY
	routes.flight_no
HAVING
	sum(ticket_flights.amount) IS NOT NULL
ORDER BY
	oborot DESC
LIMIT 1



5/
SELECT
	EXTRACT(month FROM bookings.book_date), 
	sum(bookings.total_amount) as summa, 
	count (EXTRACT(month FROM bookings.book_date))
FROM
	bookings
GROUP BY
	EXTRACT(month FROM bookings.book_date)
ORDER BY
	summa DESC
	limit 1;
	SELECT
	EXTRACT(month FROM bookings.book_date), 
	sum(bookings.total_amount) as summa, 
	count (EXTRACT(month FROM bookings.book_date))
FROM
	bookings
GROUP BY
	EXTRACT(month FROM bookings.book_date)
ORDER BY
	summa ASC
	limit 1;
	


6/
SELECT DISTINCT
	vilet.city AS gorod_vileta,
	prilet.city AS gorod_prileta 
FROM
	(SELECT T
		.ticket_no,
		f.departure_airport,
		f.arrival_airport,
		f.scheduled_departure,
		f.scheduled_arrival 
	FROM
		bookings.tickets
		T JOIN bookings.ticket_flights tf ON tf.ticket_no = T.ticket_no
		JOIN bookings.flights f ON f.flight_id = tf.flight_id ) AS t1
	JOIN (SELECT T
		.ticket_no,
		f.departure_airport,
		f.arrival_airport,
		f.scheduled_departure,
		f.scheduled_arrival 
	FROM
		bookings.tickets
		T JOIN bookings.ticket_flights tf ON tf.ticket_no = T.ticket_no
		JOIN bookings.flights f ON f.flight_id = tf.flight_id ) AS t2 ON t2.ticket_no = t1.ticket_no 
	AND t1.arrival_airport = t2.departure_airport 
	AND t2.scheduled_departure - t1.scheduled_arrival < INTERVAL '24 hour'
	JOIN bookings.airports AS vilet ON t1.departure_airport = vilet.airport_code
	JOIN bookings.airports AS prilet ON t2.arrival_airport = prilet.airport_code;