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

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

{{{#!sql
select 
	SUM(t.stripe_price) from transactions t
	inner join offers o on
		o.artist_id  = 6
		and 
		t.offer_id = o.id 
	where extract (year from t.created_at) = extract (year from now() - interval '1 year');
}}}

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

{{{#!sql
select distinct at2.name from 
	(
		select o2.artist_id from users u
		join organizers o on
			o.user_id = u.id 
		join events e on
			e.organizer_id = o.user_id
		join offers o2 on
			o2.event_id = e.id
		where e.organizer_id = 11
			and o2.completed_at notnull and o2.status = 2
	)
as artists
join artists a on
	artists.artist_id = a.user_id
join artist_types at2 on 
	at2.id = a.artist_type_id;
}}}

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

{{{#!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 ((select count(*) from offers o where completed_at notnull)/(select count(*) from offers o2)::float)*100;
}}}

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

{{{#!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.artist_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
where 
	e.title = 'Igor''s Birthday' and 
	u.username = 'test_artist_1'
group by a.user_id 
}}}

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

{{{#!sql
-- Status 1 - dogovoreni
-- Status 2 - vo dogovor
-- 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 = 1
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 
}}}