Напредни извештаи од базата (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 месеци.
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 или повеќе артисти
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
Да се најде просечно време (во денови) кое било потрошено од страна на Организаторите и Артистите за да склучат договор (да платат).
select avg(extract(day from o.completed_at-o.created_at)) from offers o where completed_at notnull
Број на трансакции реализирани од страна на организатори кои оставиле позитивни рецензии (оценка над 3) кон артисти
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
Процентуален извештај за артисти и понуди сортирани по тип на артист
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
Во кој дел од денот се испраќаат најмногу барања за понуди.
select concat(
avg(extract (hour from o.created_at))::int,
':',
avg(extract (minute from o.created_at)):: int) as avgTime
from offers o
Табеларен приказ на организатори со години во кои имало настани (приказ на детали за понуди, трансакции и сл.)
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
Процент на успешно прифатени/реализирани понуди (во проценти).
select to_char((select
( (select
count(*)
from offers o
where
completed_at notnull)
/
(select
count(*)
from offers o2)::float)
*100), 'fm00D00%')
Табеларен извештај за трансакции по артисти, по години и тримесечја.
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;
Детален приказ на статистики за артисти кои имаат менаџери
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
Популарни артисти - рангирано според број на рецензии, тотална заработка и број на добиени понуди.
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;
Број на добиени барања за понуди по артист.
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"
Просечен број на коментари/пораки кои се потребни за да се постигне договор.
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
Најпопуларни жанрови според број на артисти кои ги пеат (во опаѓачки редослед).
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
Најпопуларни локации за артисти (во опаѓачки редослед).
select
e.country,
count(e.country) as num_of_events
from events e
group by e.country
order by num_of_events desc
Просечен број на артисти со кои менаџерите менаџираат.
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
Најпопуларни типови на настани (во опаѓачки редослед).
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;
Типови артисти според популарност (во опаѓачки редослед).
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
Last modified
3 years ago
Last modified on 03/10/22 21:33:42
Note:
See TracWiki
for help on using the wiki.
