Count days between dates based on multiple conditions

I have a table which has dates, IDs and states. Inside the postgres function I need to enter start_date and end_date. Now based on these entered dates, the count of the usage and available days should be returned as IDs -> count.

Example table:

(ID)    (Date)        (State)
1     01-01-2020     Available
1     02-01-2020      In Use
1     05-01-2020     Available
1     06-01-2020      In Use
2     01-01-2020     Available
2     21-01-2020      In Use

Lets say I enter : start_date = ’01-01-2020′ and end_date = ’31-01-2020′

Output should be:

(ID)         (Usage Days)
1              29
2              11

How its calculated :

• For device 1: (2nd to 5th) = 3 days ; 6th to 31st = 26 day ; SO 26+3=29

• For device 2: (21st to 31st) = 11 days

Answer

This works for me. dbfiddle

with parms as (
  select '2020-01-01'::date as start_date,
         '2020-01-31'::date as end_date
), runs as (
  select u.*, 
         extract(days from coalesce(lead(ddate) over w, p.end_date + interval '1 day') - ddate) as runlength
  from parms p
  join usagelog u
    on u.ddate between p.start_date and p.end_date
window w as (partition by id order by ddate)
)
select id, 
       sum(runlength) filter (where state = 'Available') as available_days,
       sum(runlength) filter (where state = 'In Use') as usage_days
  from runs
 group by id
 order by id;