Get daily count of rows for a range of days

Question

My database table looks like this:

CREATE TABLE record (
    id                  int,
    status              int,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

And I want to create a generic query to get count of record created on every single day in given period period of time(30 days , 1 week , 3 days etc).

For EXAMPLE, I want to know in last 30 days on every single day how many records are created.

Expected Output if duration is 3 days:

Number of day  : issue count
day1 :           2    
day2 :           3
day3 :           3

What I have so far:

With a little help from stackoverflow I am able to create a query to calculate the count for a single day. To get this for whole week or month, I would need to run this query for every single day separately?

select COUNT(id)
from record
where extract(isodow from created_at) = 1
and created_at >= current_date - 7;

Josh Pospisil · Accepted Answer · 2021-04-03T00:14:59.167

1

Add this to the end group by date(created_at)

As pointed out in the comments, - 7 here will get you the last 7 days. That number should be adjusted to fit your specific needs.

select date(created_at) as day, COUNT(1)
from record
where created_at >= current_date - 7
group by date(created_at)

You could also modify the where clause to get a specific date range. That would look like so

where date(created_at) between '2021-03-31' and '2021-04-01'

edited Apr 03 '21 at 00:14

answered Apr 02 '21 at 23:47

Josh Pospisil

170
7

Sir I didn't get . seems answer is not formatted – Ninja Apr 02 '21 at 23:50
And spell it out that `-7` will get last 7 days. Calendar week or month will require more code. OK? – PM 77-1 Apr 02 '21 at 23:54
getting this pq: syntax error at or near "and" – Ninja Apr 02 '21 at 23:55
there is no "and" in the code provided, have you added something to that code? – Paul Maxwell Apr 03 '21 at 00:01
@Ninja Not sure how long this fiddle will be valid for so just adding in the comments, but here is a working example. http://sqlfiddle.com/#!17/6649a1/2 – Josh Pospisil Apr 03 '21 at 00:07

Erwin Brandstetter · Answer 2 · 2021-04-03T01:37:53.940

Create days of interest in your time range with generate_series(). Then LEFT JOIN and aggregate.
Example period: March 2021:

SELECT the_day::date, count(r.created_at) AS issue_count
FROM   generate_series(timestamp '2021-03-01' -- start of range (inclusive)
                     , timestamp '2021-04-01' -- end of range (exclusive)
                     , interval  '1 day') the_day
LEFT   JOIN record r ON created_at >= the_day
                    AND created_at <  the_day + interval '1 day'
GROUP  BY 1;

This way the results includes days with 0 rows.
The join condition allows the use of an index on (created_at), it's "sargable" - as opposed to something like WHERE date(created_at) BETWEEN _start AND _end'.

For bigger date-ranges / many rows it will be faster to select and aggregate the whole range from the table before the join. Like:

WITH input AS (
   SELECT timestamp '2021-03-01' AS _start  -- inlusive
        , timestamp '2021-04-01' AS _end    -- exclusive
   )
SELECT the_day, COALESCE(r.issue_count, 0) AS issue_count
FROM  (
   SELECT generate_series(_start, _end, interval '1 day')::date AS the_day
   FROM   input
   ) d
LEFT   JOIN (
   SELECT created_at::date AS the_day, count(*) AS issue_count
   FROM   input i
   JOIN   record r ON r.created_at >= i._start
                  AND r.created_at <  i._end
   GROUP  BY 1
   ) r USING (the_day);

Alternatively, drop the CTE input and provide _start and _end as query parameters directly.

See:

Hi. I think here should be something like: `SELECT the_day::date FROM generate_series(timestamp '2021-03-01 0:0:1' -- start of range (inclusive) , timestamp '2021-04-01' -- end of range (exclusive) , interval '1 day') the_day;` otherwise it will produce date '2021-04-01'. — jian, Dec 16 '22 at 10:49

Get daily count of rows for a range of days

What I have so far:

2 Answers2