Напредни извештаи од базата (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(CASE WHEN rh.cancellation_reason = 'Canceled by restaurant' THEN 1 END) AS canceled_by_restaurant,

    COUNT(rh.cancellation_reason) AS total_cancellations_per_restaurant

FROM reservation_history rh  
INNER JOIN app_user au ON rh.customer_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
}}}