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?.
create table t as
SELECT 1 as user_id, 'someone@gmail.com' as email , DATE '2020-08-28' as used_date union all
SELECT 1,'someone@gmail.com' , DATE '2020-08-29' union all
SELECT 1,'someone@gmail.com' , DATE '2020-08-30' union all
SELECT 1,'someone@gmail.com' , DATE '2020-08-31' union all
SELECT 1,'someone1@gmail.com', DATE '2020-09-03' union all
SELECT 1,'someone1@gmail.com', DATE '2020-09-05' union all
SELECT 1,'someone1@gmail.com', DATE '2020-09-07' union all
SELECT 1,'someone@gmail.com', DATE '2020-09-09' union all
SELECT 2,'bob@gmail.com' , DATE '2019-07-12' union all
SELECT 3,'alice@newmail.com' , DATE '2020-08-08'
10 rows affected
select user_id, email, min(used_date) as email_start_date,
lead(min(used_date)) over (partition by user_id order by min(used_date)) - interval '1 day' as email_end_date
from (select t.*,
row_number() over (partition by user_id order by used_date) as seqnum,
row_number() over (partition by user_id, email order by used_date) as seqnum_2
from t
) t
group by user_id, email, (seqnum - seqnum_2);
| user_id | email_start_date | email_end_date | |
|---|---|---|---|
| 1 | someone@gmail.com | 2020-08-28 | 2020-09-02 00:00:00 |
| 1 | someone1@gmail.com | 2020-09-03 | 2020-09-08 00:00:00 |
| 1 | someone@gmail.com | 2020-09-09 | null |
| 2 | bob@gmail.com | 2019-07-12 | null |
| 3 | alice@newmail.com | 2020-08-08 | null |
select user_id, email, used_date as email_start_date,
lead(used_date) over (partition by user_id order by used_date) - interval '1 day' as email_end_date
from (select t.*,
lag(email) over (partition by user_id order by used_date) as prev_email
from t
) t
where prev_email is null or prev_email <> email;
| user_id | email_start_date | email_end_date | |
|---|---|---|---|
| 1 | someone@gmail.com | 2020-08-28 | 2020-09-02 00:00:00 |
| 1 | someone1@gmail.com | 2020-09-03 | 2020-09-08 00:00:00 |
| 1 | someone@gmail.com | 2020-09-09 | null |
| 2 | bob@gmail.com | 2019-07-12 | null |
| 3 | alice@newmail.com | 2020-08-08 | null |