Version 36 (modified by 2 years ago) ( diff ) | ,
---|
Напредни извештаи од базата (SQL и складирани процедури)
Извештај за купувач според тромесечие, купени карти и придонес
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."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
Note:
See TracWiki
for help on using the wiki.