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

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

select q4."location", q4.max_broj_nastani
from (
              (select extract (year from t.date) as godina,  count(distinct e.id_event) broj_nastani, t."location" 
                from ticket t 
                join events e  on t.id_event =e.id_event 
                group by godina,  t."location" 
                
               ) q1
                
                join 
                (
                select max(q2.broj_nastani) as max_broj_nastani
                from (
                select extract (year from t.date) as godina,  count(distinct e.id_event) broj_nastani, t."location" 
                from ticket t 
                join events e  on t.id_event =e.id_event 
                group by godina,  t."location"
                ) q2
                ) q3
       on q1.broj_nastani=q3.max_broj_nastani) q4

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

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

Предложување на 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;

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

select e2."name", q4.max_broj_zainteresirani
from
((select e.id_event, count(u.id_user) as broj_na_zainteresirani_kupuvaci
from ticket t  
join events e 
on t.id_event  = e.id_event 
join users u
on t.id_user_customer =u.id_user 
join cart c on c.id_user_customer =t.id_user_customer 
where e."date" between current_date - interval '100 days' and current_date
group by e.id_event ) q1

join 

(
select max(q2.broj_na_zainteresirani_kupuvaci) as max_broj_zainteresirani
from (
select e.id_event, count(u.id_user) as broj_na_zainteresirani_kupuvaci
from ticket t  
join events e 
on t.id_event  = e.id_event 
join users u
on t.id_user_customer =u.id_user 
join cart c on c.id_user_customer =t.id_user_customer 
where e."date" between current_date - interval '100 days' and current_date
group by e.id_event 
) q2 
) q3 on q1.broj_na_zainteresirani_kupuvaci=q3.max_broj_zainteresirani)
as q4
join 
events e2 
on q4.id_event=e2.id_event 

Артист со максимален број на одржани настани

select q4."name", q4.surname, q4.max_broj_nastani
from
(

(select u."name", u.surname, count(e.id_event) as broj_nastani 
from artist a join users u on a.id_user =u.id_user
join events e on a.id_user =e.id_user_artist 
group by  u."name", u.surname) q1

join

(select max(q2.broj_nastani) as max_broj_nastani
from (select u."name", u.surname, count(e.id_event) as broj_nastani 
from artist a join users u on a.id_user =u.id_user
join events e on a.id_user =e.id_user_artist 
group by  u."name", u.surname
) q2 ) q3
on q1.broj_nastani=q3.max_broj_nastani) 

as q4

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

select u."name", u.surname, count(e.id_event) as broj_nastani 
from artist a join users u on a.id_user =u.id_user
join events e on a.id_user =e.id_user_artist
where u.id_user in (
select u.id_user 
from artist a join users u on a.id_user =u.id_user
join events e on a.id_user =e.id_user_artist
where e.id_event =1
group by  u.id_user )
group by  u."name", u.surname

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

select c.id_user, r.rating, count(t.id_transaction) as broj_transakcii
from customer c join review r on c.id_user =r.id_user_customer 
join cart c2 on c.id_user =c2.id_user_customer 
join transactions t on c2.id_user_customer =t.id_user_customer 
where r.rating >7
group by c.id_user, r.rating