Changes between Version 23 and Version 24 of AdvancedReports

Timestamp:

03/10/22 19:09:39 (3 years ago)

Author:

191151

Comment:

--

AdvancedReports

@@ -315 +315 @@
 {{{#!sql
 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
+        u."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 
+                        case when count(re.artist_id) > 0
+                                then count(re.artist_id)::float
+                                else 1
+                                end
+                        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
+        a.user_id = u.id
 join artist_types at2 on
-        at2.id = a.artist_type_id 
+        at2.id = a.artist_type_id 
 left join reviews r on
-        r.artist_id = a.user_id 
+        r.artist_id = a.user_id 
 left join offers o on
-        o.artist_id = a.user_id 
+        o.artist_id = a.user_id 
 left join transactions t on
-        t.offer_id = o.id 
+        t.offer_id = o.id 
 group by a.user_id, u.id, at2.id 
 order by 
-        average_rating desc,
-        earnings desc,
-        num_offers asc;
+        average_rating desc,
+        earnings desc,
+        num_offers asc;
 }}}