Postgres Intro Exercises

pgex.md

Postgres Intro Exercises

Простые запросы

Задача #1

Кто летел позавчера рейсом Москва (SVO) — Новосибирск (OVB) на месте 1A, и когда он забронировал свой билет?

select t.passenger_name,
       b.book_date
from bookings b
         join tickets t on b.book_ref = t.book_ref
         join boarding_passes bp on t.ticket_no = bp.ticket_no
         join flights f on bp.flight_id = f.flight_id
where f.departure_airport = 'SVO'
  and f.arrival_airport = 'OVB'
  and f.scheduled_departure::date = bookings.now()::date - INTERVAL '2 day'
  and bp.seat_no = '1A';

Задача #2

Сколько мест осталось незанятыми вчера на рейсе PG0404?

select count(*)
from flights f
         join seats s on f.aircraft_code = s.aircraft_code
where f.flight_no = 'PG0404'
  and f.scheduled_departure::date = bookings.now()::date - interval '1 day'
  and not exists(
        select null
        from boarding_passes bp
        where bp.flight_id = f.flight_id
          and bp.seat_no = s.seat_no
    )

или используя операцию вычитания множеств:

select count(*)
from (
         select s.seat_no
         from seats s
         where s.aircraft_code = (
             select aircraft_code
             from flights
             where flight_no = 'PG0404'
               and scheduled_departure::date = bookings.now()::date - interval '1 day'
         )
             except
         select bp.seat_no
         from boarding_passes bp
         where bp.flight_id = (
             select flight_id
             from flights
             where flight_no = 'PG0404'
               and scheduled_departure::date = bookings.now()::date - interval '1 day'
         )
     ) t;

Задача #3

На каких маршрутах произошли самые длительные задержки рейсов? Выведите список из десяти «лидирующих» рейсов.

В запросе надо учитывать только рейсы, которые уже вылетели:

select f.flight_no,
       f.scheduled_departure,
       f.actual_departure,
       f.actual_departure - f.scheduled_departure as delay
from flights f
where f.actual_departure is not null
order by f.actual_departure - f.scheduled_departure desc
limit 10;

Агрегатные функции

Задача #1

Какова минимальная и максимальная продолжительность полета для каждого из возможных рейсов из Москвы в Санкт-Петербург, и сколько раз вылет рейса был задержан больше, чем на час?

Здесь удобно воспользоваться готовым представлением flights_v, чтобы не выписывать соединения необходимых таблиц. В запросе учитываем только уже выполненные рейсы.

select f.flight_no,
       f.scheduled_duration,
       min(f.actual_duration),
       max(f.actual_duration),
       sum(case
               when f.actual_departure >
                    f.scheduled_departure + interval '1 hour' then 1
               else 0 end) delays
from flights_v f
where f.departure_city = 'Москва'
  and f.arrival_city = 'Санкт-Петербург'
  and f.status = 'Arrived'
group by f.flight_no, f.scheduled_duration;

Задача #2

Найдите самых дисциплинированных пассажиров, которые зарегистрировались на все рейсы первыми. Учтите только тех пассажиров, которые совершали минимум два рейса.

Используем тот факт, что номера посадочных талонов выдаются в порядке регистрации.

select t.passenger_name,
       t.ticket_no
from tickets t
         join boarding_passes bp on t.ticket_no = bp.ticket_no
group by t.passenger_name, t.ticket_no
having max(bp.boarding_no) = 1
   and count(*) > 1;

Задача #3

Сколько человек бывает включено в одно бронирование?

Сначала посчитаем количество человек в каждом бронировании, а затем число бронирований для каждого количества человек.

select tt.cnt, count(*)
from (
         select t.book_ref,
                count(*) cnt
         from tickets t
         group by t.book_ref
     ) tt
group by tt.cnt
order by tt.cnt;

Оконные функции