= Напредни извештаи од базата (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
  }}}
}}}

== Настан со максимален број на продадени карти

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

== Настан со најмногу заинтересирани купувачи во последните 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.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 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
  }}}
}}}

== Прикажување на сите настани во кој учествува одреден тип на артист

{{{#!div style="font-size: 90%"
  {{{#!sql 
select e."name", e."date", e.city, e.price, u."name", u.surname 
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 u2.id_user
        from artist a2
        join users u2 on a2.id_user = u2.id_user
        join events e2 on a2.id_user = e2.id_user_artist
        where a2.type_of_artist = 'actress'
        group by u2.id_user)
order by e."date" desc
  }}}
}}}

== Број на трансакции реализирани од страна на купувачи кои оставиле позитивни рецензии (оценка над 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
  }}}
}}}