Postgres 13

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

CREATE TABLE foo (
    grp char(1),
    mydate date,
    value integer);

INSERT INTO foo VALUES
('A', '2021-01-27', 5),
('A', '2021-01-23', 10),
('A', '2021-01-15', 15),
('B', '2021-01-26', 7),
('B', '2021-01-24', 12),
('B', '2021-01-15', 17);

SELECT * FROM foo

CREATE TABLE



INSERT 0 6




grp
mydate
value


A
2021-01-27
5

A
2021-01-23
10

A
2021-01-15
15

B
2021-01-26
7

B
2021-01-24
12

B
2021-01-15
17



SELECT 6


SELECT
    *,
    lead(mydate) OVER (PARTITION BY grp ORDER BY mydate) - 1
FROM foo


grp
mydate
value
?column?


A
2021-01-15
15
2021-01-22

A
2021-01-23
10
2021-01-26

A
2021-01-27
5
null

B
2021-01-15
17
2021-01-23

B
2021-01-24
12
2021-01-25

B
2021-01-26
7
null



SELECT 6


SELECT
    *,
    COALESCE(lead(mydate) OVER (PARTITION BY grp ORDER BY mydate) - 1, mydate)
FROM foo


grp
mydate
value
coalesce


A
2021-01-15
15
2021-01-22

A
2021-01-23
10
2021-01-26

A
2021-01-27
5
2021-01-27

B
2021-01-15
17
2021-01-23

B
2021-01-24
12
2021-01-25

B
2021-01-26
7
2021-01-26



SELECT 6


SELECT
    *
FROM (
    SELECT
        *,
        COALESCE(
             lead(mydate) OVER (PARTITION BY grp ORDER BY mydate) - 1, 
             mydate
        ) as prev_date
    FROM foo
) s,
    generate_series(mydate, prev_date, interval '1 day') as gs


grp
mydate
value
prev_date
gs


A
2021-01-15
15
2021-01-22
2021-01-15 00:00:00+00

A
2021-01-15
15
2021-01-22
2021-01-16 00:00:00+00

A
2021-01-15
15
2021-01-22
2021-01-17 00:00:00+00

A
2021-01-15
15
2021-01-22
2021-01-18 00:00:00+00

A
2021-01-15
15
2021-01-22
2021-01-19 00:00:00+00

A
2021-01-15
15
2021-01-22
2021-01-20 00:00:00+00

A
2021-01-15
15
2021-01-22
2021-01-21 00:00:00+00

A
2021-01-15
15
2021-01-22
2021-01-22 00:00:00+00

A
2021-01-23
10
2021-01-26
2021-01-23 00:00:00+00

A
2021-01-23
10
2021-01-26
2021-01-24 00:00:00+00

A
2021-01-23
10
2021-01-26
2021-01-25 00:00:00+00

A
2021-01-23
10
2021-01-26
2021-01-26 00:00:00+00

A
2021-01-27
5
2021-01-27
2021-01-27 00:00:00+00

B
2021-01-15
17
2021-01-23
2021-01-15 00:00:00+00

B
2021-01-15
17
2021-01-23
2021-01-16 00:00:00+00

B
2021-01-15
17
2021-01-23
2021-01-17 00:00:00+00

B
2021-01-15
17
2021-01-23
2021-01-18 00:00:00+00

B
2021-01-15
17
2021-01-23
2021-01-19 00:00:00+00

B
2021-01-15
17
2021-01-23
2021-01-20 00:00:00+00

B
2021-01-15
17
2021-01-23
2021-01-21 00:00:00+00

B
2021-01-15
17
2021-01-23
2021-01-22 00:00:00+00

B
2021-01-15
17
2021-01-23
2021-01-23 00:00:00+00

B
2021-01-24
12
2021-01-25
2021-01-24 00:00:00+00

B
2021-01-24
12
2021-01-25
2021-01-25 00:00:00+00

B
2021-01-26
7
2021-01-26
2021-01-26 00:00:00+00



SELECT 25


SELECT
    grp,
    gs::date as mydate,
    value
FROM (
    SELECT
        *,
        COALESCE(
             lead(mydate) OVER (PARTITION BY grp ORDER BY mydate) - 1, 
             mydate
        ) as prev_date
    FROM foo
) s,
    generate_series(mydate, prev_date, interval '1 day') as gs
ORDER BY grp, mydate DESC


grp
mydate
value


A
2021-01-27
5

A
2021-01-26
10

A
2021-01-25
10

A
2021-01-24
10

A
2021-01-23
10

A
2021-01-22
15

A
2021-01-21
15

A
2021-01-20
15

A
2021-01-19
15

A
2021-01-18
15

A
2021-01-17
15

A
2021-01-16
15

A
2021-01-15
15

B
2021-01-26
7

B
2021-01-25
12

B
2021-01-24
12

B
2021-01-23
17

B
2021-01-22
17

B
2021-01-21
17

B
2021-01-20
17

B
2021-01-19
17

B
2021-01-18
17

B
2021-01-17
17

B
2021-01-16
17

B
2021-01-15
17



SELECT 25

grp	mydate	value
A	2021-01-27	5
A	2021-01-23	10
A	2021-01-15	15
B	2021-01-26	7
B	2021-01-24	12
B	2021-01-15	17

grp	mydate	value	?column?
A	2021-01-15	15	2021-01-22
A	2021-01-23	10	2021-01-26
A	2021-01-27	5	null
B	2021-01-15	17	2021-01-23
B	2021-01-24	12	2021-01-25
B	2021-01-26	7	null

grp	mydate	value	prev_date	gs
A	2021-01-15	15	2021-01-22	2021-01-15 00:00:00+00
A	2021-01-15	15	2021-01-22	2021-01-16 00:00:00+00
A	2021-01-15	15	2021-01-22	2021-01-17 00:00:00+00
A	2021-01-15	15	2021-01-22	2021-01-18 00:00:00+00
A	2021-01-15	15	2021-01-22	2021-01-19 00:00:00+00
A	2021-01-15	15	2021-01-22	2021-01-20 00:00:00+00
A	2021-01-15	15	2021-01-22	2021-01-21 00:00:00+00
A	2021-01-15	15	2021-01-22	2021-01-22 00:00:00+00
A	2021-01-23	10	2021-01-26	2021-01-23 00:00:00+00
A	2021-01-23	10	2021-01-26	2021-01-24 00:00:00+00
A	2021-01-23	10	2021-01-26	2021-01-25 00:00:00+00
A	2021-01-23	10	2021-01-26	2021-01-26 00:00:00+00
A	2021-01-27	5	2021-01-27	2021-01-27 00:00:00+00
B	2021-01-15	17	2021-01-23	2021-01-15 00:00:00+00
B	2021-01-15	17	2021-01-23	2021-01-16 00:00:00+00
B	2021-01-15	17	2021-01-23	2021-01-17 00:00:00+00
B	2021-01-15	17	2021-01-23	2021-01-18 00:00:00+00
B	2021-01-15	17	2021-01-23	2021-01-19 00:00:00+00
B	2021-01-15	17	2021-01-23	2021-01-20 00:00:00+00
B	2021-01-15	17	2021-01-23	2021-01-21 00:00:00+00
B	2021-01-15	17	2021-01-23	2021-01-22 00:00:00+00
B	2021-01-15	17	2021-01-23	2021-01-23 00:00:00+00
B	2021-01-24	12	2021-01-25	2021-01-24 00:00:00+00
B	2021-01-24	12	2021-01-25	2021-01-25 00:00:00+00
B	2021-01-26	7	2021-01-26	2021-01-26 00:00:00+00