Напредни извештаи од базата (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))

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

   select distinct mp.projection_id,m.movie_name,vkupna_brojka.vkupno,prosek.prosek_zarabotka
from movieprojection as mp
join movie as m on mp.movie_id=m.movie_id

join(   select distinct r.reservation_id, count(r.projection_id) as vkupno
         from reservation as r
          join movieprojection as mp2 on r.projection_id=mp2.projection_id 
          join movie as m2 on mp2.movie_id=m2.movie_id
          where r.reservation_date between now() - interval '14 days' and now()
          group by 1
 )as vkupna_brojka on vkupna_brojka.vkupno is not null
 
 join(   select distinct r2.reservation_id,mp3.projection_id, avg(suma.sum1) as prosek_zarabotka
         from reservation as r2
          join movieprojection as mp3 on r2.projection_id=mp3.projection_id 
          
          join (select mp4.projection_id , sum(mp4.projection_price) as sum1
                 from movieprojection as mp4
                 group by 1
          )as suma on suma.sum1 is not null
          join movie as m3 on mp3.movie_id=m3.movie_id
          group by 1,2
          order by prosek_zarabotka desc
 )as prosek on prosek.prosek_zarabotka is not null

Прикажување на трите најрезервирани филмски жанрови според бројот на резервации на филмските проекции кои резервации се направени во последните 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 distinct u.user_name,u.user_surname,
    case when tabela.quarter=1 then tabela.broj_kupeni else 0 end as prv_kvartal,
        case when tabela.quarter=2 then tabela.broj_kupeni  else 0 end as vtor_kvartal,
    case when tabela.quarter=3 then tabela.broj_kupeni  else 0 end as tret_kvartal,
    case when tabela.quarter=4 then tabela.broj_kupeni else 0 end as cetvrt_kvartal,
    -------------------------------------------------------------------------------
    case when tabela.quarter=1 then tabela.suma else 0 end as prv_kvartal_suma,
    case when tabela.quarter=2 then tabela.suma  else 0 end as vtor_kvartal_suma,
    case when tabela.quarter=3 then tabela.suma  else 0 end as tret_kvartal_suma,
    case when tabela.quarter=4 then tabela.suma else 0 end as cetvrt_kvartal_suma
    
    
        from reservation as r
        join client as c on c.user_id = r.user_id
        join movieprojection as mp on mp.projection_id = r.projection_id 
        join users as u on u.user_id = c.user_id
        join(
        select distinct c2.user_id,extract(quarter from r2.reservation_date) as quarter,sum(mp.projection_price) as suma,
        count(r2.reservation_id) as broj_kupeni
        from reservation as r2
        join movieprojection as mp on mp.projection_id  = r2.projection_id
        join client as c2 on c2.user_id = r2.user_id
        group by 1,2
        ) as tabela on c.user_id = tabela.user_id