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

Извештаи за заработка на секоја фирма и генерален интерес за секое тромесечие

select distinct f.firm_name,
    case when tabelaQuarter.quarter=1 then tabelaSuma.count else 0 end as prv_kvartal_suma,
    case when tabelaQuarter.quarter=2 then tabelaSuma.count  else 0 end as vtor_kvartal,
    case when tabelaQuarter.quarter=3 then tabelaSuma.count  else 0 end as tret_kvartal,
    case when tabelaQuarter.quarter=4 then tabelaSuma.count  else 0 end as cetvrt_kvartal,
       
    case when tabelaQuarter.quarter=1 then tabelaSuma.sum else 0 end as prv_kvartal_suma,
    case when tabelaQuarter.quarter=2 then tabelaSuma.sum  else 0 end as vtor_kvartal_suma,
    case when tabelaQuarter.quarter=3 then tabelaSuma.sum  else 0 end as tret_kvartal_suma,
    case when tabelaQuarter.quarter=4 then tabelaSuma.sum  else 0 end as cetvrt_kvartal_suma
       
       
       
    from firm as f
    join auditorium as a on a.firm_id = f.firm_id
    join gives as g on g.auditorium_id = a.auditorium_id
    join movieprojection as mp on mp.projection_id = g.projection_id
    join reservation as r on r.projection_id = mp.projection_id
    
    join(
    select distinct f.firm_name, extract(quarter from r.reservation_date) as quarter
    from firm as f
    join auditorium as a on a.firm_id = f.firm_id
    join gives as g on g.auditorium_id = a.auditorium_id
    join movieprojection as mp on mp.projection_id = g.projection_id
    join reservation as r on r.projection_id = mp.projection_id
   
    ) as tabelaQuarter on tabelaQuarter.firm_name = f.firm_name
    
    join(
    select distinct f.firm_id,f.firm_name , a.auditorium_name as sala,sum(mp.projection_price) ,count(distinct r.reservation_id ),mp.projection_price 
    from reservation as r
    join movieprojection as mp on mp.projection_id = r.projection_id
    join gives as g on g.projection_id = mp.projection_id
    join auditorium as a on a.auditorium_id = g.auditorium_id 
    join firm as f on f.firm_id = a.firm_id 
    group by 1,2,3,6
    order by sum desc
    ) as tabelaSuma on tabelaSuma.firm_id = f.firm_id
    where ((extract(year from now())::integer-1)=(extract(year from r.reservation_date)::integer))

Прикажување на трите најрезервирани филмски жанрови според бројот на резервации на филмските проекции кои резервации се направени во последните 2 месеци гледано од моменталниот датум

select g.genre_id ,g.genre_name, count(r) as rezervacii from genre as g
     join is_ as i on i.genre_id = g.genre_id
     join movie as m on m.movie_id  = i.movie_id
     join movieprojection as mp on mp.movie_id = m.movie_id
     join reservation as r on r.projection_id = mp.projection_id
     and r.reservation_date between now() - interval '2 month' and now()
     group by g.genre_id
     order by rezervacii desc
     limit 3;

Го селектираме и genre_id за да може Query-то да го употребиме во нашата Spring Boot Application

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

  select c.user_id,sum(mp.projection_price) as vkupna_potroshuvacka,u.user_name,u.user_surname from users as u
     join client as c on c.user_id = u.user_id
     join reservation as r on r.user_id = c.user_id
     and r.reservation_date between now() - interval '1 year' and now()
     join movieprojection as mp on mp.projection_id = r.projection_id
     where mp.projection_id = 12732  --?--
     group by c.user_id,u.user_name,u.user_surname
     order by vkupna_potroshuvacka desc
     limit 1;

Коментарот --?-- значи дека во Spring Boot ќе се замени вредноста со projection_id кој се задава како параметар по кликање на копчето View Projections кој се наоѓа до секој од филмовите