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

Вкупен број на резервации за храна во текот на претходните 2 месеци

select count(distinct rfpm.reservation_id) as broj_rezervacii_na_hrana
from reservation_for_prepared_meal rfpm 
join prepared_meal pm  on
rfpm.prepared_meal_id = pm.prepared_meal_id 
join reservation r on
rfpm.reservation_id  = r.reservation_id 
and 
start_date between now() - interval '2 months' and now()

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

select   extract(month from start_date) as months,
                 count(e.event_id) as num_events,  
         sum(e.number_of_interested_guests) as total_guests, 
         avg(e.price) as avg_price,
         sum(e.price * e.number_of_interested_guests) as sales
from reservation_for_events rfe 
join reservation r on
rfe.reservation_id = r.reservation_id
join events e on
rfe.event_id  = e.event_id 
group by months

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

select   extract(year from start_date) as years,
                 count(a.activity_id) as num_activities,  
         sum(a.number_of_interested_guests) as total_guests, 
         avg(a.price) as avg_price,
         sum(a.price * a.number_of_interested_guests) as sales
from reservation_for_activity rfa 
join reservation r on
rfa.reservation_id = r.reservation_id
join activities a  on
rfa.activity_id  = a.activity_id
group by years

Просечна плата на секој тип на вработени во Вила Дихово

select 
        (
          select count(w.user_id)
          from waiter w
        )as waiters_in_villa_dihovo,
        (
          select avg(w.salary)
          from waiter w
        )as average_salary_waiter,
        (
          select count(r.user_id) 
          from receptionist r
        )as receptionists_in_villa_dihovo,
        (
          select avg(r.salary)
          from receptionist r 
        )as average_salary_receptionist,
        (
          select count(c.user_id)
          from chef c
        ) as chefs_in_villa_dihovo,
        (
          select avg(c.salary)
          from chef c
        )as average_salary_chef

from user_table ut
group by 1,2,3;

Нарачки за храна кој ги има направено еден корисник и вкупна сума на плаќање

select pm.user_id,
       ut.full_name,
       count(ut.full_name) as broj_naracki_hrana, 
       sum(rfpm.quantity * m.price) as total_sum
from prepared_meal pm 
join user_table ut on
pm.user_id  = ut.user_id 
join meal m on
pm.meal_id  = m.meal_id 
join reservation_for_prepared_meal rfpm on
rfpm.prepared_meal_id  = pm.prepared_meal_id 
join reservation r on
rfpm.reservation_id  = r.reservation_id 
group by 1,2

Одржана активност за која што има најголема заинтересираност во последната година

select a.activity_type as aktivnost,
       sum(a.number_of_interested_guests) as broj_na_zainteresirani_gosti
from activities a  
join reservation_for_activity rfa 
on a.activity_id  = rfa.activity_id 
join reservation r  
on r.reservation_id = rfa.reservation_id 
where r.start_date  between  now() - interval '1 year' and now()
group by a.activity_type 
order by count(a.number_of_interested_guests) desc
limit 1;

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

select distinct r.room_type,
 (
        select count(r2.reservation_id)
        where r.room_type = 'Single'
 ) as single_room_reservation,
 (
        select count(r2.reservation_id)
        where r.room_type = 'Double'
 )as double_room_reservation
from rooms r
join reservation r2 
on r.room_id  = r2.room_id 
group by 1;

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

   select  ut.full_name,
           (
                select  count(rfa.reservation_id)
                from reservation_for_activity rfa
           )as number_reservations_for_activity,
           (
                select count(rfe.reservation_id)
                from reservation_for_events rfe
           )as number_reservations_for_events,
           (
                select count(rfpm.reservation_id)
                from reservation_for_prepared_meal rfpm 
           )as number_reservations_for_meal,
           (
                select count(rfs.reservation_id)
                from reservation_for_service rfs 
           )as number_reservations_for_service
from user_table ut
join guests g 
on ut.user_id  = g.user_id 
join guests_make_reservation gmr 
on g.user_id  = gmr.user_id 
join reservation r
on gmr.reservation_id = r.reservation_id
group by 1;

Број на соби во секоја вила

select v.name as villa_name ,
       count(r.room_id) as number_of_rooms
    from villa v
    join rooms r on
    r.villa_id  = v.villa_id 
    group by villa_name 
    order by villa_name