Aggregate counts for each date between date range

Question

I want to find the counts for each date for each city where I have a date range specified by two columns start_date and end_date.

Suppose I have created a table with values like this.

create table abc (city varchar(30),start_date date , end_date date);

insert into abc values('a','2018-01-01','2018-01-03');
insert into abc values('b','2018-01-02','2018-01-05');
insert into abc values('a','2018-01-03','2018-01-06');
insert into abc values('b','2018-01-03','2018-01-03');
insert into abc values('a','2018-01-02','2018-01-02');
insert into abc values('b','2018-01-02','2018-01-05');

I wish to find what are the counts for city a and b on each date. Here it should show me this.

a, 2018-01-01,1
a, 2018-01-02,2
a, 2018-01-03,2
a, 2018-01-04,1
a, 2018-01-05,1
a, 2018-01-06,1
b, 2018-01-02,2
b, 2018-01-03,3
b, 2018-01-04,2
b, 2018-01-05,2

If it was a single date a group by would have done it.

Any help is appreciated.

Answer 1

Use the function generate_series(start, stop, step interval) to get all dates within ranges:

select city, date::date, count(*)
from abc
cross join generate_series(start_date, end_date, '1day'::interval) date
group by 1, 2
order by 1, 2

 city |    date    | count 
------+------------+-------
 a    | 2018-01-01 |     1
 a    | 2018-01-02 |     2
 a    | 2018-01-03 |     2
 a    | 2018-01-04 |     1
 a    | 2018-01-05 |     1
 a    | 2018-01-06 |     1
 b    | 2018-01-02 |     2
 b    | 2018-01-03 |     3
 b    | 2018-01-04 |     2
 b    | 2018-01-05 |     2
(10 rows)

Cross join in the above query is a lateral join, the function is executed once for each row. Because Postgres allows functions returning set in the select list, you can also phrase this as:

select city, generate_series(start_date, end_date, '1day'::interval)::date date, count(*)
from abc
group by 1, 2
order by 1, 2