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

==  Да се најде направениот промет за секој артист во изминатата година.

{{{#!sql
select 
	u."name" ,
	SUM(t.stripe_price)
from artists a 
join users u on 
	u.id = a.user_id 
join offers o on 
    o.artist_id = u.id 
join transactions t on 
	t.offer_id = o.id 
where 
    extract (year from t.created_at) = extract (year from now() - interval '1 year')
group by u.id 
}}}

== Да се најдат каков тип на Артисти ангажирале Организаторите во последните 6 месеци.

{{{#!sql
select 
        distinct at2.name
from organizers o 
left join events e on e.organizer_id = o.user_id 
left join offers o2 on o2.event_id = e.id 
left join artists a on a.user_id = o2.artist_id 
join artist_types at2 on at2.id = a.artist_type_id 
where 
        o2.status = 'COMPLETED'and
        o2.completed_at between now() - interval '6 months' and now()
group by o.user_id, at2.name
}}}

== Да се најде просечно време (во денови) кое било потрошено од страна на Организаторите и Артистите за да склучат договор (да платат).

{{{#!sql
select avg(extract(day from o.completed_at-o.created_at)) from offers o 
where completed_at notnull 
}}}

== Во кој дел од денот се испраќаат најмногу барања за понуди.

{{{#!sql
select concat(
			avg(extract (hour from o.created_at))::int, 
			':', 
			avg(extract (minute from o.created_at)):: int) as avgTime
from offers o
}}}

== Процент на успешно прифатени/реализирани понуди (во проценти).

{{{#!sql
select to_char((select 
	(	(select 
		count(*) 
	 	from offers o 
	 	where 
	 		completed_at notnull)
	/
		(select 
			count(*) 
	 	from offers o2)::float)
*100), 'fm00D00%') 
}}}

== Табеларен извештај за трансакции по артисти, по години и тримесечја.
{{{#!sql
with year_transactions as (
	select
		distinct extract(year from (t.created_at)) as year
	from transactions t 
)

select 
	us."name",
	yt.year,
	(
		select 
			count(t.id)
		from transactions t 
		join offers o on 	
			o.artist_id = us.id  and 
			t.offer_id = o.id 
	) as total_transactions,
	(
		select 
			count(t.id)
		from transactions t
		join offers o on
			o.artist_id = us.id  and 
			t.offer_id = o.id 
		where 
			yt.year = extract(year from (t.created_at)) and 
			extract(month from (t.created_at)) in (1,2,3)
	) as total_num_trans_q1,
	(
		select 
			count(t.id)
		from transactions t
		join offers o on
			o.artist_id = us.id  and 
			t.offer_id = o.id 
		where 
			yt.year = extract(year from (t.created_at)) and 
			extract(month from (t.created_at)) in (4,5,6)
	) as total_num_trans_q2,
	(
		select 
			count(t.id)
		from transactions t
		join offers o on
			o.artist_id = us.id  and 
			t.offer_id = o.id 
		where 
			yt.year = extract(year from (t.created_at)) and 
			extract(month from (t.created_at)) in (7,8,9)
	) as total_num_trans_q3,
	(
		select 
			count(t.id)
		from transactions t
		join offers o on
			o.artist_id = us.id  and 
			t.offer_id = o.id 
		where 
			yt.year = extract(year from (t.created_at)) and 
			extract(month from (t.created_at)) in (10,11,12)
	) as total_num_trans_q4,
	(
		select 
			avg(o.price)
		from transactions t
		join offers o on
			o.artist_id = us.id  and 
			t.offer_id = o.id 
		where 
			yt.year = extract(year from (t.created_at)) and 
			extract(month from (t.created_at)) in (1,2,3)
	) as avg_trans_value_q1,
	(
		select 
			avg(o.price)
		from transactions t
		join offers o on
			o.artist_id = us.id  and 
			t.offer_id = o.id 
		where 
			yt.year = extract(year from (t.created_at)) and 
			extract(month from (t.created_at)) in (4,5,6)
	) as avg_trans_value_q2,
	(
		select 
			avg(o.price)
		from transactions t
		join offers o on
			o.artist_id = us.id  and 
			t.offer_id = o.id 
		where 
			yt.year = extract(year from (t.created_at)) and 
			extract(month from (t.created_at)) in (7,8,9)
	) as avg_trans_value_q3,
	(
		select 
			avg(o.price)
		from transactions t
		join offers o on
			o.artist_id = us.id  and 
			t.offer_id = o.id 
		where 
			yt.year = extract(year from (t.created_at)) and 
			extract(month from (t.created_at)) in (10,11,12)
	) as avg_trans_value_q4
from year_transactions yt, artists ar
left join users us on
	us.id = ar.user_id;
}}}

== Популарни артисти - рангирано според број на рецензии, тотална заработка и број на добиени понуди.

{{{#!sql
select 
	u.full_name,
	u.email,
	a.city,
	a.country,
	a.price_per_hour,
	at2.name,
	coalesce(sum(t.stripe_price),0) as earnings,
	(
		select 
			count(o2.id)
		from offers o2 
		where o2.artist_id = a.user_id 
	) as num_offers,
	(
		(5 * (select 
			count(re.artist_id)
			from reviews re
			where 
				re.rating = 5 and
				re.artist_id = a.user_id) +
		4 * (select 
			count(re.artist_id)
			from reviews re
			where 
				re.rating = 4 and
				re.artist_id = a.user_id) +
		3 * (select 
			count(re.artist_id)
			from reviews re
			where 
				re.rating = 3 and
				re.artist_id = a.user_id) +
		2 * (select 
			count(re.artist_id)
			from reviews re
			where 
				re.rating = 2 and
				re.artist_id = a.user_id) +
		1 * (select 
			count(re.artist_id)
			from reviews re
			where 
				re.rating = 1 and
				re.artist_id = a.user_id)) /
		((select 
			count(re.artist_id)
			from reviews re
			where 
				re.artist_id = a.user_id))
	)
	as average_rating
from users u
join artists a on
	a.user_id = u.id
join artist_types at2 on
	at2.id = a.artist_type_id 
left join reviews r on
	r.artist_id = a.user_id 
left join offers o on
	o.artist_id = a.user_id 
left join transactions t on
	t.offer_id = o.id 
group by a.user_id, u.id, at2.id 
order by 
	average_rating desc,
	earnings desc,
	num_offers asc;
}}}

== Број на барања испратени кон артисти.

{{{#!sql
select 
	a.user_id,
	count(o.id) as num_of_offers_received 
from artists a 
join users u on u.id = a.user_id
join offers o on o.artist_id = a.user_id 
join events e on e.id = o.event_id
group by a.user_id 
}}}

== Просечен број на коментари/пораки кои се потребни за да се постигне договор.

{{{#!sql
-- Status 1 - vo dogovor
-- Status 2 - dogovoreni
-- Status 3 - otkazano

with tempTable as (select 
	oc.offer_id,
	count(oc.offer_id) as num_of_comments
from offer_comments oc 
join offers as o on o.id = oc.offer_id and 
					o.status = 2
group by oc.offer_id)
select 
	 avg(t.num_of_comments) 
from tempTable as t
}}}

== Најпопуларни жанрови според број на артисти кои ги пеат (во опаѓачки редослед).

{{{#!sql
select 
	asg.genre_id,
	count(asg.genre_id) as popularity
from artist_sings_genres asg
group by asg.genre_id
order by popularity desc 
}}}

== Најпопуларни локации за артисти (во опаѓачки редослед).

{{{#!sql
select 
	e.country,
	count(e.country) as num_of_events 
from events e 
group by e.country 
order by num_of_events desc 

}}}

== Просечен број на артисти со кои менаџерите менаџираат.

{{{#!sql
with tempTable as (select 
	m.user_id, 
	count(a.user_id)
from managers m 
left join artists a on a.manager_id = m.user_id 
group by m.user_id) 
select 
	avg(count)
from tempTable
}}}

== Најпопуларни типови на настани (во опаѓачки редослед).

{{{#!sql
select et.name, count(*) as occurence from events e 
join event_types et on 
	et.id = e.event_type_id 
group by et.name
order by occurence desc;
}}}


== Типови артисти според популарност (во опаѓачки редослед).

{{{#!sql
select
	a_t.id,
	a_t.name, 
	count (distinct a.user_id)
from artist_types a_t 
left join artists a on a.artist_type_id = a_t.id
group by a_t.id, a_t.name
order by count desc 
}}}