sql - Extracting several math operations outputs from single select query

Question

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

Answer 1

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]