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

За секоја година, установа со најголем број одржани настани

select q4.god as godina, p.ime as predmet, q4.broj_casovi as broj_casovi
from
(

    select * from 
     (
         select q1.godina as god, max(q1.broj_casovi) as maksimum from
             (
              select extract (year from c.vreme_pocetok) as godina, p.id_predmet as predmet, count(c.id_cas) broj_casovi
                from predmeti p
                join casovi c on c.id_predmet = p.id_predmet 
                group by godina,predmet
                ) q1
                group by godina
        ) q2
        join 
        (
        select extract (year from c.vreme_pocetok) as godina, p.id_predmet as predmet, count(c.id_cas) broj_casovi
                from predmeti p
                join casovi c on c.id_predmet = p.id_predmet 
                group by godina,predmet
        ) q3
        on q2.god=q3.godina and q2.maksimum=q3.broj_casovi

) q4
join predmeti p on p.id_predmet = q4.predmet
order by godina desc;

Настан со најмногу заинтересирани купувачи во последните 100 дена

select p.ime as predmet, count(zz.id_ucenik) as broj_na_zainteresirani_ucenici
from predmeti p 
join zainteresiran_za zz 
on zz.id_predmet = p.id_predmet 
join ucenici u 
on u.id_ucenik = zz.id_ucenik 
where zz.datum between current_date - interval '100 days' and current_date
group by p.ime
order by count(zz.id_ucenik) desc 
limit 1;

Листа на артисти со број на одржани настани

select p.ime as predmet, count(pp.id_nastavnik) broj_na_nastavnici
from predmeti p
join predava_predmet pp on pp.id_predmet = p.id_predmet 
group by p.id_predmet 
order by p.ime;

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

select title from(
         select  m.title, sum(mv.stars_rated) as total
         from replies r 
         join discussions d on r.discussion_id = d.discussion_id
         join replies r2 on r2.discussion_id  = d.discussion_id  and r2.user_id  != 1
         join users u  on u.user_id  = r2.user_id and 
         
                        (
                                select count(r3.reply_id)
                                from replies r3 
                                group by u.user_id 
                        ) >= 
                        (
                                
                                select count(*)::float / (select count(*)::float from users)
                                from replies 
                        )
                        
     join movie_rates mv on mv.user_id = u.user_id 
     join movies m on m.movie_id  = mv.movie_id
     where  r.user_id = 1 and 
     r.date between current_date - interval '2 months' and current_date
     group by m.title
     order by sum(mv.stars_rated) desc 
     limit 15
 ) as tabela;

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

select distinct u.user_name,u.user_surname,
    case when tabela.quarter=1 then tabela.broj_kupeni else 0 end as prv_kvartal_kupeni,
        case when tabela.quarter=2 then tabela.broj_kupeni  else 0 end as vtor_kvartal_kupeni,
    case when tabela.quarter=3 then tabela.broj_kupeni  else 0 end as tret_kvartal_kupeni,
    case when tabela.quarter=4 then tabela.broj_kupeni else 0 end as cetvrt_kvartal_kupeni,
    -------------------------------------------------------------------------------
    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

Број на трансакции реализирани од страна на купувачи кои оставиле позитивни рецензии (оценка над 3) кон артисти

select count (*) from (
with tempTable as (select 
        o.* 
from organizers o 
join reviews as r on 
       r.organizer_id = o.user_id and 
       r.rating > 3
group by o.user_id)
select 
        t.*
from events as e
join tempTable as tt on 
       tt.user_id = e.organizer_id 
join offers as o2 on 
       o2.event_id = e.id 
join transactions as t on 
     t.offer_id = o2.id 
) as temp