Given a Users table like so:
Users: id, created_at
How can I get the # of users created grouped by day? My goal is to see the number of users created this Monday versus previous Monday's.
4 Answers
If created_at is of type timestamp, the simplest and fastest way is a plain cast to date:
SELECT created_at::date AS day, count(*) AS ct
FROM users
GROUP BY 1;
Since I am assuming that id cannot be NULL, count(*) is a tiny bit shorter and faster than count(id), while doing the same.
If you just want to see days since "last Monday":
SELECT created_at::date, count(*) AS ct
FROM users
WHERE created_at >= (now()::date - (EXTRACT(ISODOW FROM now())::int + 6))
GROUP BY 1
ORDER BY 1;
This is carefully drafted to use a sargable condition, so it can use a simple index on created_at if present.
Consider the manual for EXTRACT.
answered Jul 22, 2013 at 23:46
Erwin Brandstetter
669k160 gold badges1.2k silver badges1.3k bronze badges
Comments
SELECT COUNT(id) AS cnt, EXTRACT(DOW FROM created_at) AS dow
FROM Users
GROUP BY EXTRACT(DAY FROM created_at)
4 Comments
AnApprentice
ERROR: column "day" does not exist LINE 3: GROUP BY DATEPART(DAY, created_at)
AnApprentice
Thanks but the output doesn't say what day it is next to the count. Anyway to get the day? Ideally just Mondays?
Mike Perrenoud
@AnApprentice, clearly you can expand on the statement. I just showed you how to grab the day of the week so you can use the results to filter to just Mondays. Further, if you want the date itself simply look up the extract function, there are a ton of options.
AnApprentice
Thanks but it's erroring: "ERROR: column "users.created_at" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT COUNT(id) AS cnt, EXTRACT(DOW FROM created_at) AS dow ^ "
If you want to see the days, use to_char(<date>, 'Day').
So, one way to do what you want:
select date_trunc('day', created_at), count(*)
from users u
where to_char(created_at, 'Dy') = 'Mon'
group by date_trunc('day', created_at)
order by 1 desc;
Perhaps a more general way to look at it would be to summarize the results by day of the week, for this week and last week. Something like:
select to_char(created_at, 'Day'),
sum(case when created_at >= current_date - 6 then 1 else 0 end) as ThisWeek,
sum(case when trunc(created_at) between current_date - 13 and current_date - 7 then 1 else 0 end) as LastWeek
from users u
group by to_char(created_at, 'Day')
2 Comments
AnApprentice
ERROR: function trunc(timestamp without time zone) does not exist LINE 1: select trunc(created_at), count(*) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
AnApprentice
Errors: "ERROR: function trunc(timestamp without time zone) does not exist LINE 1: select trunc(created_at), count(*) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. "
I am from a T-SQL background and I would do something like this
CREATE TABLE #users
(id int,
created_at datetime
)
INSERT INTO #users
(id, created_at)
VALUES
(
1, getdate()
)
INSERT INTO #users
(id, created_at)
VALUES
(
1, getdate()
)
INSERT INTO #users
(id, created_at)
VALUES
(
1, dateadd(DAY, 1,getdate())
)
SELECT id, created_at, count(id) FROM #users
GROUP BY id, created_at
DROP TABLE #users
You will get better results if you only group by day part and not the entire datetime value.
Coming to second part - only comparing for Mondays; you can use something like
select datename(dw,getdate())
the above will give you the name of the weekday which you can compare against a string literal 'Monday'.
created_at? Version of Postgres?