У вас есть БД в которой находятся 8 таблиц с информацией по авиаперевозкам:
Задания:
1. Выведите название самолетов, которые имеют менее 100 посадочных мест?
2. Выведите процентное изменение ежемесячной суммы бронирования билетов, округленной до сотых.
3. Выведите названия самолетов не имеющих бизнес - класс. Подсказка применить функцию array_agg.
4. Вывести накопительный итог количества мест в самолетах по каждому аэропорту на каждый день, учитывая только те самолеты, которые летали пустыми и только те дни, где из одного аэропорта таких самолетов вылетало более одного.
5. Найдите процентное соотношение перелетов по маршрутам от общего количества перелетов.
6. Выведите количество пассажиров по каждому коду сотового оператора, если учесть, что код оператора - это три символа после +7
7. Классифицируйте финансовые обороты (сумма стоимости перелетов) по маршрутам:
- До 50 млн - low
- От 50 млн включительно до 150 млн - middle
- От 150 млн включительно - high
- Выведите в результат количество маршрутов в каждом полученном классе
Для выполнения заданий необходимо создать таблицы и наполнить их данными
Решения:
Задача_1
select a.model
from aircrafts a
join (select s.aircraft_code, count(s.seat_no)
from seats s
group by s.aircraft_code
having count(s.seat_no) < 100) r on a.aircraft_code = r.aircraft_code;
Задача_2
select date_trunc('month', b.book_date)::date as "Даты по месяцам",
sum(total_amount) as "Сумма",
lead(sum(total_amount), 1) over (order by date_trunc('month', b.book_date)::date),
round((lead(sum(total_amount), 1) over () / sum(total_amount)) * 100, 2) as "Округленное отношение"
from bookings b
group by date_trunc('month', b.book_date)::date
Задача_3
select a.model, "Массив"
from aircrafts a
join (select s.aircraft_code, array_agg(distinct fare_conditions) as "Массив"
from seats s
group by s.aircraft_code, fare_conditions) p on a.aircraft_code = p.aircraft_code
where 'Business' != all("Массив")
Задача 4
WITH EmptyFlights AS (
SELECT
f.Departure_Airport,
f.Scheduled_Departure AS FlightDate,
COUNT(DISTINCT sts.Seat_No) AS EmptySeats
FROM Flights AS f
JOIN Aircrafts AS s ON f.aircraft_code = s.aircraft_code
LEFT JOIN seats as sts on s.aircraft_code = sts.aircraft_code
LEFT JOIN Ticket_Flights AS tf ON f.Flight_ID = tf.Flight_ID
WHERE tf.Ticket_No IS NULL
GROUP BY
f.Departure_Airport,
f.Scheduled_Departure
HAVING COUNT(DISTINCT sts.Seat_No) > 1
)
SELECT
Departure_Airport,
FlightDate,
SUM(EmptySeats) OVER (PARTITION BY Departure_Airport ORDER BY FlightDate) AS CumulativeEmptySeats
FROM EmptyFlights
ORDER BY
Departure_Airport,
FlightDate;
Задача 5.
select concat (dep.airport_name,' - ',ar.airport_name) as "Аэропорт вылета и прилёта", round((count/sum)*100,2) as "Доля от общего кол-ва перелётов"
from (select f.departure_airport, f.arrival_airport, count(*),
sum(count(*)) over () from flights f group by f.departure_airport, f.arrival_airport order by f.departure_airport) t
left join airports dep on dep.airport_code = t.departure_airport
left join airports ar on ar.airport_code = t.arrival_airport
Задача 6.
select count(passenger_id) as Количество, substring(contact_data ->> 'phone' from 3 for 3) as "Код оператора"
from tickets
group by "Код оператора" order by "Код оператора"
Задача 7.
with cte as (select departure_airport, arrival_airport, sum(t.amount)
from flights f
left join ticket_flights t on t.flight_id = f.flight_id
group by departure_airport, arrival_airport order by departure_airport)
select t.class_amount as Класс, count(t.class_amount) as Количество
from (select case
when cte.sum <= 50000000 then 'low'
when cte.sum between 50000000 and 150000000 then 'middle'
when cte.sum >= 150000000 then 'high'
end class_amount
from cte
where cte.sum is not null) t
group by Класс order by Количество
✔️ Благодарю за прочтение
✔️Подпишись на этот канал, что бы не пропустить новую полезную публикацию.
✔️Поставь под этой публикацией палец вверх - это помогает продвигать контент.
✔️Подробнее про SQL на этом обучающем сайте