Напредни извештаи од базата (SQL и складирани процедури)

Извештај за број на откжани, успешни резервации по ресторан

SELECT 
    au.first_name, 
    au.last_name, 
    au.membership_level, 
    au."role", 
    rh.restaurant_id,
    COUNT(CASE WHEN rh.cancellation_reason = 'Canceled by user' THEN 1 END) AS canceled_by_user_per_restaurant,
    COUNT(CASE WHEN rh.cancellation_reason = '/' THEN 1 END) AS succesfull_by_user_per_restaurant,
    COUNT(rh.cancellation_reason) AS total_cancellations_per_restaurant
FROM reservation_history rh  
INNER JOIN app_user au ON rh.user_id = au.id 
WHERE rh.cancellation_reason IS NOT NULL  
GROUP BY au.first_name, 
         au.last_name, 
         au.membership_level, 
         au."role", 
         rh.restaurant_id 

ORDER BY canceled_by_user_per_restaurant DESC;

Извештај за најмногу резервации по ресторан

SELECT 
	r.restaurantId, 
	r."name", 
	COUNT(res.reservationId) AS totalReservations
FROM restaurants r
JOIN reservations res ON r.restaurantId = res.restaurantId
GROUP BY r.restaurantId, 
         r."name" 
ORDER BY totalReservations desc

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

SELECT 
    r."name" AS restaurant_name,
    ROUND(AVG(rh.party_size), 1) AS average_party_size,
    MAX(rh.party_size) AS max_party_size,
    COUNT(rh.id) AS number_of_reservations
FROM reservation_history rh 
JOIN "tables" t ON t.tableid = rh.table_id  
JOIN restaurants r ON r.restaurantid = rh.restaurant_id 
WHERE rh.reservation_datetime  >= (CURRENT_DATE - INTERVAL '1 month') 
GROUP BY r."name"
ORDER BY average_party_size DESC;

Извештај за најрезервиран временски период во ресторан

SELECT 
    r."name",
    CONCAT(
        LPAD(DATE_PART('hour', rh.check_in_date)::TEXT, 2, '0'), 
        ':00 - ', 
        LPAD((DATE_PART('hour', rh.check_in_date) + 2)::TEXT, 2, '0'),
        ':00'
    ) AS hour_slot, 
    COUNT(rh.id) AS totalReservations
FROM reservation_history rh 
JOIN restaurants r ON rh.restaurant_id = r.restaurantid
GROUP BY hour_slot, r."name" 
ORDER BY totalReservations DESC, r."name"  
LIMIT 5;