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