CODEWITHSUNDEEP
sqlpostgresql
Sam
Sam

Reputation: 1

Extracting several math operations outputs from single select query

I have three tables that I need to merge to analyse: active, students and bills.

'Active' contains records on active students and the subjects they have been active on with columns: id (student id) int, time (time they have been active) timestamp, and subject (subject in which were active) - text

id   time                  subject
1    2020-04-23 06:53:30   Math
2    2020-05-13 09:51:22   Physics
2    2020-02-26 17:34:56   History

'Students' is the mass database containing: id (student id) int, group (the group to which student was assigned for a/b test) - text

id   group     
1    A       
2    B 
3    A
4    A

'Bills' keeps record of all transactions for courses that student purchased: id (student id) int, sale_time (time when student purchased course) timestamp, subject (subject in which course purchased) text, money (amount paid).

id     sale_time              subject     money
1      2020-03-04 08:54:55    Math        4300
1      2020-04-08 20:43:56    Math        3200
2      2020-05-09 13:43:12    Law         8900

Basically, we have a student database (Students) some of which purchased courses (Bills). While some of those who purchased remain active (Active).

I need to write ONE SINGLE query where I can extract the following grouped by whether they belong to A or B group:

average revenue per user: sum (money) / count (distinct Students.id)
average revenue per active user: sum (money) / count (distinct Active.id)
conversion rate (%): count (distinct Bills.id) / count (distinct Students.id)
conversion rate (active) (%): count (distinct Bills.id) / count (distinct Active.id)
conversion rate (Math) (%) (count (distinct Bills.id) where Bills.subject = Math) / (count (distinct Active.id) where Active.subject = Math)

All these in single query!

I used

select sum (money)/count (distinct Students.id)
  from Students
     left join Bills using (id)
        left join Active using (id)
            group by group, Students.id

but I don't know how to do these math calculations all in one right after select with filters.

Please help!

SQL fiddle: https://www.db-fiddle.com/f/NPQR6aBf8H36XvrefJY2J/0

Upvotes: 0

Views: 77

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

I would recommend removing duplicates before joining and then using window functions:

select s.group, avg(b.money)as AvgPerUser,
       sum(b.money) /  nullif(count(a.id), 0) as AvgActUser,
       count(b.id) / nullif(count(s.id), 0) as CovRate,
       count(b.id) / nullif(count(a.id),0) as ConActRate,
       count(b.id) filter (where s.subject = 'Math') * 1.0 / count(*) filter (where s.subject = 'Math') as ConRateMath
from Students s left join
     (select b.id, sum(money) as money
      from bills b
      group by b.id
     ) b
     on s.id = b.id left join
     (select distinct a.id from active a
     ) a
     on s.id = a.id
group by s.group;

Note: I don't think you want s.id in the GROUP BY. That really would not be aggregating anything.

Upvotes: 1

Sahar Rezazadeh
Sahar Rezazadeh

Reputation: 333

All You need is this:

select s.[group], sum (money)/  NULLIF( count (distinct s.id),0) as 
   AvgPerUser,
   sum (money) /  NULLIF(count (distinct a.id),0) as AvgActUser,
   count (distinct b.id) /  NULLIF(count (distinct a.id),0) as CovRate,
   count (distinct b.id) /  NULLIF(count (distinct a.id),0) as ConActRate,
   (select count(distinct b2.id) from Bills as b2 where b2.subject = 'Math') /  
   NULLIF((select count ( distinct a2.id) from Active as a2  where a2.subject 
   ='Math'),0) as ConRateMath
   from Students as s
   left join Bills as b on s.id = b.id
    left join Active as a on s.id = a.id
        group by s.[group]

Upvotes: 1

Related Questions