Nakheel Hotels bookings

SELECT channel, status, count(*) AS bookings
FROM bookings
GROUP BY channel, status
ORDER BY channel, bookings DESC;

SELECT date_trunc('month', n.night)::date AS month,
       count(*) AS room_nights,
       sum(b.nightly_rate) AS room_revenue
FROM bookings b
CROSS JOIN LATERAL generate_series(b.check_in, b.check_out - 1, interval '1 day') AS n(night)
WHERE b.status IN ('checked_out', 'checked_in')
  AND n.night < DATE '2026-01-01'
GROUP BY 1
ORDER BY 1;

SELECT h.hotel_name, b.channel,
       round(sum(b.total_amount) / sum(b.check_out - b.check_in), 2) AS adr
FROM bookings b
JOIN hotels h ON h.hotel_id = b.hotel_id
WHERE b.status = 'checked_out'
GROUP BY 1, 2
ORDER BY 1, 2;

SELECT channel,
       count(*) AS bookings,
       round(100.0 * count(*) FILTER (WHERE status = 'cancelled') / count(*), 1) AS cancel_pct
FROM bookings
WHERE check_in <= DATE '2025-12-31'
GROUP BY channel
ORDER BY cancel_pct DESC;

SELECT h.hotel_name, count(*) AS reviews, round(avg(r.rating), 2) AS avg_rating
FROM reviews r
JOIN bookings b ON b.booking_id = r.booking_id
JOIN hotels h ON h.hotel_id = b.hotel_id
GROUP BY h.hotel_name
ORDER BY avg_rating DESC;