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


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

{{{#!div style="font-size: 90%"
  {{{#!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 месеци и корисници кои имаат оставено број на реплики поголем или еднаков од вкупниот просечен на број на реплики по сите корисници 

{{{#!div style="font-size: 90%"
  {{{#!sql 
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 дена 

{{{#!div style="font-size: 90%"
  {{{#!sql 
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
  }}}
}}}
== За секоја година, град со најголем број одржани настани 

{{{#!div style="font-size: 90%"
  {{{#!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) 
as q4
  }}}
}}}

OR

{{{#!div style="font-size: 90%"
  {{{#!sql 
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
  }}}
}}}

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

{{{#!div style="font-size: 90%"
  {{{#!sql 
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
  }}}
}}}

== За даден настан, листа на сите артисти на настанот со број на активни настани во кои учествува артисот
{{{#!div style="font-size: 90%"
  {{{#!sql 
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) кон артисти 

{{{#!div style="font-size: 90%"
  {{{#!sql 
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
  }}}
}}}