Group By (Numerator, Denominator, or both?)


#1

In the following code, am I correct in thinking that SQL will group both the numerator AND the denominator by “date”, then perform the individual calculations?

select
date(created_at),
round(sum(price) / count(distinct user_id), 2) as arppu
from purchases
where refunded_at is null
group by 1
order by 1;

If so, when I want to group by ONLY the numerator, does the denominator have to be within a sub query, as in the following code?:

Select name, round(sum(amount_paid) /
(select sum(amount_paid) from order_items)
100.0, 2) as pct
from order_items
group by 1
order by 2 desc;

Thanks!


#2

This query will group all of the records with the same date then sum their price divided by the distinct user ids.

So if you have 3 records with the created_at date of 2018/10/18, their price at 100, 150,95 respectively and 100 and 95 have the same user_id the query will sum 100 + 150 + 95 then divide it by 2 (because two records have the same id and one record has a different id the distinct ids count is 2) then the result will be rounded.