Напредни извештаи од базата (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", e2."date", e2.city, e2.price, 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

OR

select q4.godina, q4.city, q4.max_broj_nastani
from (
(
        select extract (year from e."date") as godina,
                count(distinct e.id_event) broj_nastani, e.city
        from events e
        group by godina, e.city 
) q1

join 

(
        select max(q2.broj_nastani) as max_broj_nastani
        from
                (
                select extract (year from e."date") as godina,
                        count(distinct e.id_event) broj_nastani, e.city
                from events e
                group by godina, e.city 
) 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 q5.id_event, q5."name", q5.max_prodadeni_karti
from (
(
        select q1.id_event, q1."name",
                case
                        when q1.prodadena_karta >= 1 then q1.prodadena_karta
                        else 0
                end as prodadeni_karti
        from (
                select e.id_event, e."name", count(t2.id_invoice) as prodadena_karta
                from ticket t
                join events e on t.id_event = e.id_event
                join transactions t2 on t.id_cart = t2.id_cart
                group by e.id_event, e."name"
) q1 ) q3

join

(
        select max(q2.prodadeni_karti) as max_prodadeni_karti
        from (
                select q1.id_event, q1."name",
                        case
                                when q1.prodadena_karta >= 1 then q1.prodadena_karta
                                else 0
                        end as prodadeni_karti
                from (
                        select e.id_event, e."name", count(t2.id_invoice) as prodadena_karta
                        from ticket t
                        join events e on t.id_event = e.id_event
                        join transactions t2 on t.id_cart = t2.id_cart
                        group by e.id_event, e."name"
) q1 ) q2

) q4
on
        q3.prodadeni_karti = q4.max_prodadeni_karti )
as q5

Вкупно продадени карти за секој настан

select q1.id_event, q1."name",
        case
                when q1.prodadena_karta >= 1 then q1.prodadena_karta
                else 0
        end as prodadeni_karti
from(
        select e.id_event, e."name", count(t2.id_invoice) as prodadena_karta
        from ticket t
        join events e on t.id_event = e.id_event
        join transactions t2 on t.id_cart = t2.id_cart
        group by e.id_event, e."name"
) as q1
order by q1.id_event asc

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

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