Changes between Version 13 and Version 14 of AdvancedReports

Timestamp:

03/10/22 18:24:45 (4 years ago)

Author:

191151

Comment:

--

AdvancedReports

@@ -63 +63 @@
                 from offers o2)::float)
 *100), 'fm00D00%') 
+}}}
+
+== Табеларен извештај за трансакции по артисти, по години и тримесечја
+{{{#!sql
+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;
 }}}