Напредни извештаи од базата (SQL)

Извештај за трендовите на резервации на клиенти по град и зграда за најпопуларниот тип на соба за секоја зграда

SELECT 
    hb.city AS City,
    hb.building_id AS Building,
    COUNT(res.reservation_id) AS Total_Reservations,
    r.room_type AS Most_Popular_Room_Type,
    COUNT(CASE WHEN r.room_type = (
        SELECT r2.room_type 
        FROM Room r2
        JOIN Reservation res2 ON r2.room_number = res2.room_number AND r2.building_id = res2.building_id
        WHERE res2.building_id = hb.building_id
        GROUP BY r2.room_type
        ORDER BY COUNT(*) DESC
        LIMIT 1
    ) THEN 1 END) AS Room_Type_Reservations
FROM 
    Hotel_Building hb
JOIN Room r ON hb.building_id = r.building_id
JOIN Reservation res ON r.room_number = res.room_number AND r.building_id = res.building_id
GROUP BY 
    hb.city, hb.building_id, r.room_type
ORDER BY 
    Total_Reservations DESC;

Извештај за стапките на искористеност на собите по зграда - ја пресметува стапката на искористеност на собите (процент на зафатени соби) за секоја зграда во одреден временски период.

SELECT 
    hb.building_id AS Building,
    hb.city AS City,
    COUNT(r.room_number) AS Total_Rooms,
    COUNT(CASE 
        WHEN res.start_date <= '2025-01-15' AND 
             (res.end_date >= '2025-01-01' OR res.end_date IS NULL) 
        THEN 1 END) AS Occupied_Rooms,
    ROUND(
        (COUNT(CASE 
            WHEN res.start_date <= '2025-01-15' AND 
                 (res.end_date >= '2025-01-01' OR res.end_date IS NULL) 
            THEN 1 END) / COUNT(r.room_number)) * 100, 
        2
    ) AS Utilization_Percentage
FROM 
    Hotel_Building hb
LEFT JOIN Room r ON hb.building_id = r.building_id
LEFT JOIN Reservation res ON r.room_number = res.room_number 
                          AND r.building_id = res.building_id
                          AND (res.start_date <= '2025-01-15' AND 
                               (res.end_date >= '2025-01-01' OR res.end_date IS NULL))
GROUP BY 
    hb.building_id, hb.city
ORDER BY 
    Utilization_Percentage DESC;