Group

SELECT model, count FROM aircrafts,
(SELECT COUNT(*) FROM seats GROUP BY aircraft_code ORDER BY COUNT(*) DESC LIMIT 1)
AS bar
WHERE aircraft_code = (SELECT aircraft_code FROM seats GROUP BY aircraft_code ORDER BY COUNT(*) DESC LIMIT 1)

Having

SELECT book_ref
FROM tickets
GROUP BY book_ref
HAVING COUNT (*) >= 5
ORDER BY book_ref
LIMIT 3


DISTINCT

SELECT COUNT (*)
FROM (SELECT DISTINCT flight_no FROM flights_v WHERE arrival_airport_name = 'Домодедово'
      AND date_part('day', (bookings.now() - actual_arrival)) < 7
      GROUP BY flight_no) AS flight_no


EXCEPT

SELECT arrival_city
FROM   routes
EXCEPT SELECT arrival_city
FROM   routes
WHERE  departure_city in ('Москва', 'Санкт-Петербург')


Подзапросы

SELECT fl.flight_no,
        fl.aircraft_code,
        fl.capacity,
        CASE WHEN tf.tickets_count is NULL THEN 0
            ELSE tf.tickets_count
        END

FROM (SELECT f.flight_id, f.flight_no, f.aircraft_code, s.count AS capacity
     FROM flights AS f
     JOIN (SELECT aircraft_code, count(*)
            FROM seats
            GROUP BY aircraft_code) AS s
     ON    f.aircraft_code=s.aircraft_code
     WHERE f.status='Departed') AS fl

LEFT JOIN (SELECT flight_id, count(*) AS tickets_count
        FROM ticket_flights GROUP BY flight_id) AS tf
ON fl.flight_id = tf.flight_id

WHERE ((tf.tickets_count * 1.0 / fl.capacity) * 100 <= 25
        OR tf.tickets_count is NULL)
    AND fl.capacity >= 30

ORDER BY fl.flight_no;





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

SELECT res.aircraft_code,
       res.actual_arrival,
       sum(res.tickets_count)
       OVER (PARTITION BY res.aircraft_code
            ORDER BY res.actual_arrival)

FROM (SELECT f.flight_id,
             f.status,
             f.aircraft_code,
             f.actual_arrival,
             (CASE WHEN tf.count is NULL THEN 0
                ELSE tf.count
              END) AS tickets_count

      FROM flights AS f

      LEFT JOIN (SELECT flight_id, count(*)
            FROM ticket_flights
            GROUP BY flight_id) AS tf
      ON f.flight_id = tf.flight_id

      WHERE f.status='Arrived'
        AND (f.aircraft_code='319'
            OR f.aircraft_code='773')
        AND f.actual_arrival >= (bookings.now() - interval '15 hours')
        AND f.actual_arrival < (bookings.now() + interval '9 hours')

      ORDER BY f.aircraft_code, f.actual_arrival) AS res

ORDER BY res.aircraft_code, res.actual_arrival DESC;


Общие табличные выражения (чит решение, но раз нет ревью :) )

with RECURSIVE route (arr) as
(
    values ('{IJK, SVO, VOG, CEK, SWT}'::text[])
    union all
    select arr from route where arr <@ '{IJK, SVO, CEK, SWT}'::text[]
)

select * from route