Changes between Version 21 and Version 22 of AdvancedReports

Timestamp:

03/10/22 19:01:18 (4 years ago)

Author:

191215

Comment:

--

AdvancedReports

@@ -178 +178 @@
 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
 }}}