CODEWITHSUNDEEP
sqldatabasepostgresql
Ninja
Ninja

Reputation: 301

Get daily count of rows for a range of days

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;

Upvotes: 0

Views: 3715

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658947

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:

Upvotes: 1

Josh Pospisil
Josh Pospisil

Reputation: 215

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'

Upvotes: 1

Related Questions