How to summate weekly totals from a postgres date range type per user I have the following table:

Schedules
+----+---------+------------+-------+--------------------------+
| id | user_id | project_id | hours | date_range               |
+----+---------+------------+-------+--------------------------+
|  1 |       1 |          1 |     4 | [2019-05-01, 2019-05-08) |
+----+---------+------------+-------+--------------------------+
|  2 |       2 |          1 |     8 | [2019-04-20, 2019-05-20) |
+----+---------+------------+-------+--------------------------+
|  3 |       1 |          2 |     2 | [2019-05-05, 2019-05-12) |
+----+---------+------------+-------+--------------------------+
| ...etc       |            |       |                          |
+--------------+------------+-------+--------------------------+


What I was hoping to accomplish with SQL was to get a summation of total hours per week given a specific date range input, for example date_start: 2019-05-05, date_end: 2019-05-11.

For the week of 2019-05-05 to 2019-05-11, any Schedule that has a date_range that has overlapping dates would get summated by project_id and by user_id.

Example: Row 1 from the above table has a date_range of [2019-05-01, 2019-05-08) so it would have 2019-05-05, 2019-05-06, 2019-05-07 (2019-05-08 omitted because exclusive )) dates that match the date_start/date_end criterion.

Since the hours are 4 for that row, the given total would be 4 * n days = 12 hours total 

The date_start/date_end can be arbitrarily large, so say it was over a year range, every date_range would be added by week over the given range. 

Potential expected output (open to suggestions):

| week_start |  week_end  | project_id | user_id | total_hours |
| 2019-05-05 | 2019-05-11 |          1 |       1 |          12 |
| 2019-05-05 | 2019-05-11 |          1 |       2 |          56 |
| etc ...    |            |            |         |             |


I'm not quite sure how to structure a query to generate matching date_rage days and multiply them out per user.

Can someone point me in the right direction?
Build week borders table (week_start, week_end) in the range from MIN(date_start) to MAX(date_end) in CTE. Then cross-join it with your table, select only overlapped pairs and calculate values you need. - akina