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

== Листа на организатори кои организирале настани на кои биле поканети 2 или повеќе артисти

{{{#!sql
select 
	u.id,
	u.username,
	u.email,
	u.name,
	o.city,
	o.country
from events as e
join (
	select 
		e.id,
		e.title,
		count(distinct a.user_id) as num_artists
	from events as e 
	join offers as o2 on
						o2.event_id = e.id
	join artists as a on
						a.user_id = o2.artist_id
	group by e.id 
) as tt on
						tt.id = e.id and 
						tt.num_artists > 1
join organizers as o on 
						o.user_id = e.organizer_id
join users as u on 
					u.id = o.user_id 
group by o.user_id, u.id 
}}}

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

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

== Број на трансакции реализирани од страна на организатори кои оставиле позитивни рецензии (оценка над 3) кон артисти

{{{#!sql
select count (*) from (
with tempTable as (select 
	o.* 
from organizers o 
join reviews as r on 
					r.organizer_id = o.user_id and 
					r.rating > 3
group by o.user_id)
select 
	t.*
from events as e
join tempTable as tt on 
						tt.user_id = e.organizer_id 
join offers as o2 on 
					o2.event_id = e.id 
join transactions as t on 
					t.offer_id = o2.id 
) as temp
}}}

== Процентуален извештај за артисти и понуди сортирани по тип на артист

{{{#!sql
select 
	*,
	(
	select to_char( 
		(
			select 
			        count(*) 
			from artists a
			where
				a.admin_verified_at is not null and
				a.artist_type_id = at1.id
			)
			/
			(
			select 
                                count(*) 
                        from artists a 
			where 
				a.admin_verified_at is not null
			)::numeric ,
			'fm00D00%'
		)
	) as percent_artists,
	(
		select to_char( 
			(
			select 
			        count(*) 
			from artists a
			where
				a.admin_verified_at is not null and
				a.artist_type_id = at1.id and
				a.manager_id is not null 
			)
			/
			(
			select 
                                count(*)
                        from artists a 
			where 
				a.admin_verified_at is not null
			)::numeric * 100,
			'fm00D00%'
		) 
	)as percent_artists_with_managers,
	(
		select 
			count(*)
		from artists a
		join offers o on o.artist_id = a.user_id 
		where 
			a.admin_verified_at is not null and
			a.artist_type_id = at1.id
	) as num_offers_received,
	(
		to_char
		( 
			(
			case when (
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id
		                  ) != 0 then 
				(
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id and
						o.status = 'COMPLETED'
				)
				/
				(
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id
				)::numeric 
			else 
				0::numeric 
			end
			) * 100,
			'fm00D00%'
		)
	) as percent_completed_offers, -- procent na 'COMPLETED' offers
	(
		to_char
		( 
			(
			case when (
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id
				) != 0 then 
				(
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id and
						o.status = 'IN PROGRESS'
				)
				/
				(
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id
				)::numeric 
			else 
				0::numeric 
			end
			) * 100,
			'fm00D00%'
		)
	) as percent_inProgress_offers, -- procent na 'IN PROGRESS' offers
	(
		to_char
		( 
			(
			case when (
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id
				  ) != 0 then 
				(
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id and
						o.status = 'WAITING FOR PAYMENT'
				)
				/
				(
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id
				)::numeric 
			else 
				0::numeric 
			end
			) * 100,
			'fm00D00%'
		)
	) as percent_waitingForPayment_offers, -- procent na 'WAITING FOR PAYMENT' offers
	(
		to_char
		( 
			(
			case when (
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id
				) != 0 then 
				(
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id and
						o.status = 'DECLINED'
				)
				/
				(
					select 
						count(*)
					from artists a
					join offers o on o.artist_id = a.user_id 
					where 
						a.admin_verified_at is not null and
						a.artist_type_id = at1.id
				)::numeric 
			else 
				0::numeric 
			end
			) * 100,
			'fm00D00%'
		)
	) as percent_declined_offers -- procent na 'DECLINED' offers
from artist_types at1
order by at1.id
}}}


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

{{{#!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
with year_events as (
        select
                distinct extract(year from (e.event_date)) as year
        from events e
)
select 
	u.id,
	u.name,
	ye.year,
	(
		select 
			count(*) 
		from events as e
		where 	
			e.organizer_id = o.user_id and 
			extract(year from (e.event_date)) = ye.year	
	) as total_events,
	(
		select 
			count(*)
		from events as e
		join offers o2 on o2.event_id = e.id 
		where 	
			e.organizer_id = o.user_id and 
			extract(year from (e.event_date)) = ye.year
	) as total_offers_sent,
	(
			select 
			count(*)
		from events as e
		left join offers o2 on o2.event_id = e.id 
		join transactions as t on t.offer_id = o2.id 
		where 	
			e.organizer_id = o.user_id and 
			extract(year from (e.event_date)) = ye.year
	)as total_transactions,
	(
		select 
			count(distinct o2.artist_id)
		from events as e
		join offers o2 on o2.event_id = e.id 
		where 	
			e.organizer_id = o.user_id and 
			extract(year from (e.event_date)) = ye.year
	) as num_artists, --br na artisti so koi sorabotuval
	(
		select 
			count(distinct o2.artist_id)
		from events as e
		join offers o2 on o2.event_id = e.id 
		join artists a on a.user_id = o2.artist_id and a.artist_type_id = 1
		where 	
			e.organizer_id = o.user_id and 
			extract(year from (e.event_date)) = ye.year
	) as num_solo_artists, --br na solo artisti so koi sorabotuval
		(
		select 
			count(distinct o2.artist_id)
		from events as e
		join offers o2 on o2.event_id = e.id 
		join artists a on a.user_id = o2.artist_id and a.artist_type_id = 2
		where 	
			e.organizer_id = o.user_id and 
			extract(year from (e.event_date)) = ye.year
	) as num_duo_artists, --br na duo artisti so koi sorabotuval
		(
		select 
			count(distinct o2.artist_id)
		from events as e
		join offers o2 on o2.event_id = e.id 
		join artists a on a.user_id = o2.artist_id and a.artist_type_id = 3
		where 	
			e.organizer_id = o.user_id and 
			extract(year from (e.event_date)) = ye.year
	) as num_band_artists --br na band artisti so koi sorabotuval
	
from organizers as o
cross join year_events as ye
join users u on u.id = o.user_id
}}}

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

{{{#!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 
	m.user_id as manager_id,
	a.user_id as artists_id,
	u.name,
	a.city,
	a.country,
	at2.name as artist_type,
	(
		select 
			count(distinct asg.genre_id)
		from artist_sings_genres asg 
		where asg.artist_id = a.user_id
	) as num_genres,
	(
		select 
			count(*) 
		from offers o
		where o.artist_id = a.user_id 
	) as num_offers,
	(
		select 
			count(*) 
		from offers o
		join transactions t on t.offer_id = o.id
		where o.artist_id = a.user_id 
	) as num_completed_offers,
	(
		select 
			count(distinct e.id) 
		from offers o
		join events e on e.id = o.event_id and 
						e.event_date < now()
		where o.artist_id = a.user_id 
	) as num_completed_events,
	(
		select 
			count(distinct e.id) 
		from offers o
		join events e on e.id = o.event_id and 
						e.event_date > now()
		where o.artist_id = a.user_id 
	) as num_upcoming_events,
	(
		select 
			count(*) filter(where r.rating > 3) 
		from reviews r
		where r.artist_id = a.user_id 
	) as num_positive_reviews,
	(
		select 
			count(*) filter(where r.rating <= 3) 
		from reviews r
		where r.artist_id = a.user_id 
	) as num_negative_reviews,
	(
		select 
			count(distinct e.organizer_id) 
		from offers o
		join events e on e.id = o.event_id 
		where o.artist_id = a.user_id
	) as num_organizers,
	(
		select 
			count(t.id)
		from offers o 
		left join transactions t on t.offer_id = o.id 
		where o.artist_id = a.user_id 
	) as num_transactions
from managers m 
join artists as a on 
					a.manager_id = m.user_id and 
					a.admin_verified_at is not null 
join users as u on u.id = a.user_id 					
join artist_types at2 on at2.id = a.artist_type_id
}}}

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

{{{#!sql
select 
        u."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 
                        case when count(re.artist_id) > 0
                        	then count(re.artist_id)::float
                        	else 1
                        	end
                        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 
        u."name" ,
        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 u."name" 
}}}

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

{{{#!sql
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 = 'COMPLETED'
group by oc.offer_id)
select 
         avg(t.num_of_comments) 
from tempTable as t
}}}

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

{{{#!sql
select 
        g."name",
        count(asg.genre_id) as popularity
from artist_sings_genres asg
join genres g on 
	g.id = asg.genre_id 
group by asg.genre_id, g.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 
}}}