Напредни извештаи од базата (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 месеци.

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;

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

select avg(extract(day from o.completed_at-o.created_at)) from offers o 
where completed_at notnull 

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

select concat(
                        avg(extract (hour from o.created_at))::int, 
                        ':', 
                        avg(extract (minute from o.created_at)):: int) as avgTime
from offers o

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

select ((select count(*) from offers o where completed_at notnull)/(select count(*) from offers o2)::float)*100;

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

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;

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

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 

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

-- 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

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

select 
        asg.genre_id,
        count(asg.genre_id) as popularity
from artist_sings_genres asg
group by asg.genre_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