Changes between Version 24 and Version 25 of AdvancedReports

Timestamp:

03/10/22 20:22:02 (4 years ago)

Author:

191215

Comment:

--

AdvancedReports

@@ -103 +103 @@
                         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_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_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
 }}}