Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

with a (user_id, call_date, city)
as (
  select *
  from ( values
    ('1', date '2021-01-01', 'A'),
    ('1', date '2021-01-02', 'B'),
    ('1', date '2021-01-03', 'B'),
    ('1', date '2021-01-05', 'B'),
    ('1', date '2021-01-10', 'A'),
    ('1', date '2021-01-12', 'B'),
    ('1', date '2021-01-16', 'A'),
    ('2', date '2021-01-17', 'A'),
    ('2', date '2021-01-20', 'B'),
    ('2', date '2021-01-22', 'B'),
    ('2', date '2021-01-23', 'A'),
    ('2', date '2021-01-24', 'B'),
    ('2', date '2021-01-26', 'B'),
    ('2', date '2021-01-30', 'A')
  ) as t
)
, grp as (
  /*Identify groups*/
  select a.*,
    /*This is a grouping of consecutive rows:
      they will have the same difference between
      two row_numbers while the more detailed
      row_number changes, which means the attribute had changed.

*/
    dense_rank() over(
      partition by user_id
      order by call_date asc
    ) - 
    dense_rank() over(
      partition by user_id, city
      order by call_date asc
    ) as grp,
    /*Get next call date*/
    lead(call_date, 1, call_date)
      over(
        partition by user_id
        order by call_date asc
      ) as next_dt
  from a
)
select
  user_id,
  city,
  min(call_date) as dt_from,
  max(next_dt) as dt_to,
  max(next_dt) - min(call_date) as diff
from grp
where city = 'B'
group by user_id, grp, city
order by 1, 3

user_id	city	dt_from	dt_to	diff
1	B	2021-01-02	2021-01-10	8
1	B	2021-01-12	2021-01-16	4
2	B	2021-01-20	2021-01-23	3
2	B	2021-01-24	2021-01-30	6