Re: Average New Users Per DOW

[Michael Nolan <htfoot@xxxxxxxxx>]

Here's a minor refinement that doesn't require knowing the range of dates in the users table:

(select created, created as created2, count(*) as total from users
group by 1, 2
union
(select generate_series(
(select min(created)::timestamp from users),
(select max(created)::timestamp from users),
'1 day')::date, null, 0) ) as x) as y
group by 1, 2) as z
order by dow

Day       New Users       
--- ----------------------
Sun 1.00000000000000000000
Mon     4.5000000000000000
Tue     2.0000000000000000
Wed     4.5000000000000000
Thu 1.00000000000000000000
Fri     3.0000000000000000
Sat     3.0000000000000000

--

Mike Nolan