| From: | Michael Nolan <htfoot(at)gmail(dot)com> |
|---|---|
| To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
| Cc: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Average New Users Per DOW |
| Date: | 2015-07-07 01:30:00 |
| Message-ID: | CAOzAqu+6i6aD62zL8oJaTwn-S+=K7=CXtBkW6ToLJLT=L52WJA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | c.buhtz | 2015-07-07 04:55:16 | Re: [pg_hba.conf] publish own Python application using PostgreSQL |
| Previous Message | Edson F. Lidorio | 2015-07-07 01:23:39 | Re: Download PostgreSQL 9.5 Alpha |