Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (
    id int, 
    value text,
    usagedate date
);

INSERT INTO t VALUES
(1, 'value1', '2020-09-10'),
(1, 'value1', '2020-09-15'),
(1, 'value1', '2020-09-20'),
(1, 'value1', '2020-09-23'),
(1, 'value1', '2020-09-25'),
(1, 'value1', '2020-09-30'),
(1, 'value2', '2020-09-15'),
(1, 'value2', '2020-09-20'),
(1, 'value2', '2020-09-23'),
(1, 'value2', '2020-09-25');

SELECT * FROM t;
10 rows affected



id
value
usagedate


1
value1
2020-09-10

1
value1
2020-09-15

1
value1
2020-09-20

1
value1
2020-09-23

1
value1
2020-09-25

1
value1
2020-09-30

1
value2
2020-09-15

1
value2
2020-09-20

1
value2
2020-09-23

1
value2
2020-09-25


SELECT 
    *, 
    row_number() OVER (PARTITION BY id, value)
FROM
    t


id
value
usagedate
row_number


1
value1
2020-09-10
1

1
value1
2020-09-15
2

1
value1
2020-09-20
3

1
value1
2020-09-23
4

1
value1
2020-09-25
5

1
value1
2020-09-30
6

1
value2
2020-09-15
1

1
value2
2020-09-20
2

1
value2
2020-09-23
3

1
value2
2020-09-25
4


SELECT 
    *, 
    row_number() OVER (PARTITION BY id, value ORDER BY usagedate),
    first_value(usagedate) OVER (PARTITION BY id ORDER BY usagedate DESC)
FROM
    t


id
value
usagedate
row_number
first_value


1
value1
2020-09-30
6
2020-09-30

1
value2
2020-09-25
4
2020-09-30

1
value1
2020-09-25
5
2020-09-30

1
value2
2020-09-23
3
2020-09-30

1
value1
2020-09-23
4
2020-09-30

1
value2
2020-09-20
2
2020-09-30

1
value1
2020-09-20
3
2020-09-30

1
value2
2020-09-15
1
2020-09-30

1
value1
2020-09-15
2
2020-09-30

1
value1
2020-09-10
1
2020-09-30


SELECT
    *
FROM (
    SELECT 
        *, 
        row_number() OVER (PARTITION BY id, value ORDER BY usagedate),
        first_value(usagedate) OVER (PARTITION BY id ORDER BY usagedate DESC)
    FROM
        t
)s
WHERE row_number % 2 = 1


id
value
usagedate
row_number
first_value


1
value1
2020-09-25
5
2020-09-30

1
value2
2020-09-23
3
2020-09-30

1
value1
2020-09-20
3
2020-09-30

1
value2
2020-09-15
1
2020-09-30

1
value1
2020-09-10
1
2020-09-30


SELECT
    id,
    value,
    usagedate,
    lead(usagedate) OVER (PARTITION BY id ORDER BY usagedate)
FROM (
    SELECT 
        *, 
        row_number() OVER (PARTITION BY id, value ORDER BY usagedate),
        first_value(usagedate) OVER (PARTITION BY id ORDER BY usagedate DESC)
    FROM
        t
)s
WHERE row_number % 2 = 1


id
value
usagedate
lead


1
value1
2020-09-10
2020-09-15

1
value2
2020-09-15
2020-09-20

1
value1
2020-09-20
2020-09-23

1
value2
2020-09-23
2020-09-25

1
value1
2020-09-25
null


SELECT
    id,
    value,
    usagedate,
    COALESCE(
        lead(usagedate) OVER (PARTITION BY id ORDER BY usagedate),
        first_value
    )
FROM (
    SELECT 
        *, 
        row_number() OVER (PARTITION BY id, value ORDER BY usagedate),
        first_value(usagedate) OVER (PARTITION BY id ORDER BY usagedate DESC)
    FROM
        t
)s
WHERE row_number % 2 = 1


id
value
usagedate
coalesce


1
value1
2020-09-10
2020-09-15

1
value2
2020-09-15
2020-09-20

1
value1
2020-09-20
2020-09-23

1
value2
2020-09-23
2020-09-25

1
value1
2020-09-25
2020-09-30
id	value	usagedate
1	value1	2020-09-10
1	value1	2020-09-15
1	value1	2020-09-20
1	value1	2020-09-23
1	value1	2020-09-25
1	value1	2020-09-30
1	value2	2020-09-15
1	value2	2020-09-20
1	value2	2020-09-23
1	value2	2020-09-25