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

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

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

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

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

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

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