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