Extract number of days per week from multiple date ranges

Question

I have a table trips in PostgreSQL 10.5:

id  start_date    end_date
----------------------------
1   02/01/2019    02/03/2019
2   02/02/2019    02/03/2019
3   02/06/2019    02/07/2019
4   02/06/2019    02/14/2019
5   02/06/2019    02/06/2019

I want to count the number of days in trips that overlap with given weeks. Trips in the table have inclusive bounds. Weeks start on Monday and end on Sunday. The expected result would be:

week_of    days_utilized
------------------------
01/28/19    5
02/04/19    8
02/11/19    4

For a calendar reference:

Monday 01/28/19 - Sunday 02/03/19
Monday 02/04/19 - Sunday 02/10/19
Monday 02/11/19 - Sunday 02/17/19

I know how to write this in the programming language I use, but I'd prefer to do this in Postgres and I'm unclear where to start ...

Answer 1

You seem to want generate_series() and a join and group by. To count the week covered:

select gs.wk, count(t.id) as num_trips
from generate_series('2019-01-28'::date, '2019-02-11'::date, interval '1 week') gs(wk) left join
     trips t
     on gs.wk <= t.end_date and
        gs.wk + interval '6 day' >= t.start_date
group by gs.wk
order by gs.wk;

EDIT:

I see you want the days covered. This is slightly more work in the aggregation:

select gs.wk, count(t.id) as num_trips,
       sum( 1 +
            extract(day from (least(gs.wk + interval '6 day', t.end_date) - greatest(gs.wk, t.start_date)))
          ) as days_utilized
from generate_series('2019-01-28'::date, '2019-02-11'::date, interval '1 week') gs(wk) left join
     trips t
     on gs.wk <= t.end_date and
        gs.wk + interval '6 day' >= t.start_date
group by gs.wk
order by gs.wk;

Note: This doesn't return the exactly results you have. I think these are correct.