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

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

select
        distinct u."name", u.surname,
        case
                when q1.quarter = 1 then q1.broj_kupeni
                else 0
        end as prv_kvartal_kupeni,
        case
                when q1.quarter = 2 then q1.broj_kupeni
                else 0
        end as vtor_kvartal_kupeni,
        case
                when q1.quarter = 3 then q1.broj_kupeni
                else 0
        end as tret_kvartal_kupeni,
        case
                when q1.quarter = 4 then q1.broj_kupeni
                else 0
        end as cetvrt_kvartal_kupeni,
        -------------------------------------------------------------------------------
 case
                when q1.quarter = 1 then q1.suma
                else 0
        end as prv_kvartal_suma,
        case
                when q1.quarter = 2 then q1.suma
                else 0
        end as vtor_kvartal_suma,
        case
                when q1.quarter = 3 then q1.suma
                else 0
        end as tret_kvartal_suma,
        case
                when q1.quarter = 4 then q1.suma
                else 0
        end as cetvrt_kvartal_suma
from
        cart as c
join users as u on c.id_user_customer = u.id_user
join transactions as t on t.id_user_customer = c.id_user_customer
join
  (
        select
                distinct c2.id_user_customer,
                extract(QUARTER
        from
                t2.created_at) as QUARTER,
                sum(c2.total) as suma,
                count(t2.id_invoice) as broj_kupeni
        from
                cart as c2
        join transactions as t2 on t2.id_cart = c2.id_cart
        join users as u2 on u2.id_user = t2.id_user_customer
        group by 1, 2
        ) as q1 
        on u.id_user = q1.id_user_customer

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

as q4

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

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 u."name", u.surname, 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 
join users u on c.id_user=u.id_user 
where r.rating >7
group by u."name", u.surname, r.rating