How to get lead value of a specific case in Postgresql?

Question

I have a table like below

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  group_id INT,
  code VARCHAR,
  created_date timestamptz
);

Sample values

INSERT INTO events (group_id, code, created_date) VALUES 
(1, '0001', clock_timestamp()),
(1, '000A', clock_timestamp()),
(1, '0002', clock_timestamp()),
(2, '000A', clock_timestamp()),
(2, '0003', clock_timestamp()),
(2, '0004', clock_timestamp()),
(3, '0001', clock_timestamp()),
(3, '000A', clock_timestamp()),
(4, '0001', clock_timestamp()),
(5, '000A', clock_timestamp()),
(5, '0002', clock_timestamp()),
(5, '000A', clock_timestamp()),
(5, '0006', clock_timestamp());

I have a query

SELECT 
 DISTINCT ON (group_id) group_id, 
 last_value(code) over w_last AS last_code, 
 last_value(created_date) over w_last AS last_created_date
FROM events
WINDOW
 w_last AS (PARTITION BY group_id ORDER BY created_date ROWS BETWEEN unbounded preceding AND unbounded following);

Query Result

| group_id | last_code | last_created_date        |
| -------- | --------- | ------------------------ |
| 1        | 0002      | 2020-11-18T09:25:53.443Z |
| 2        | 0004      | 2020-11-18T09:25:53.443Z |
| 3        | 000A      | 2020-11-18T09:25:53.443Z |
| 4        | 0001      | 2020-11-18T09:25:53.443Z |
| 5        | 0006      | 2020-11-18T09:25:53.443Z |

I'm trying to get one additional field to show event after last '000A' event

 Eg. for group_id 1 event code after 000A is 0002
     for group_id 2 event code after 000A is 0003
     for group_id 3 event code after 000A is NULL
     for group_id 4 event code after 000A is NULL
     for group_id 5 event code after 000A is 0006 (from last)

Expected Output

| group_id | last_code | after_last_000A | last_created_date        |
| -------- | --------- | --------------- | ------------------------ |
| 1        | 0002      |     0002        | 2020-11-18T09:25:53.443Z |
| 2        | 0004      |     0003        | 2020-11-18T09:25:53.443Z |
| 3        | 000A      |     NULL        | 2020-11-18T09:25:53.443Z |
| 4        | 0001      |     NULL        | 2020-11-18T09:25:53.443Z |
| 5        | 0006      |     0006        | 2020-11-18T09:25:53.443Z |

Same in DB Fiddle

S-Man · Accepted Answer · 2020-11-18T10:02:22.430

step-by-step demo: db<>fiddle

SELECT DISTINCT ON (group_id)                                              -- 4
    group_id,
    code as last_code,
    first_value(after_000A)                                                -- 3
        OVER (PARTITION BY group_id ORDER BY after_000A DESC NULLS LAST) as after_last,
    created_date as last_created_date
FROM (
    SELECT 
        *,
        CASE WHEN code = '000A' THEN                                       -- 2
            lead(code) OVER (PARTITION BY group_id ORDER BY created_date)  -- 1
        END as after_000A
    FROM 
        events
) s
ORDER BY group_id, created_date DESC                                       -- 4

lead() return the next row's value if available, NULL else
CASE clause eleminates all unrelated values. Therefore, only the lead values for codes '000A' are displayed. This is the new after_000A column
first_value() gives the first value of an ordered partition. Here the group_id partitions are ordered by the values of the previously created after_000A column (descending to fetch the last one).
DISTINCT ON (group_id) returns only one record per group_id group, in that case the last one (ordered by created_date desc).

score 0 · Answer 2 · answered Nov 18 '20 at 10:00

I think you can solve this by using LEAD() and ROW_NUMBER():

with data_set as
(
    select *
    ,ROW_NUMBER()OVER(PARTITION BY group_id,code ORDER BY created_date desc) counter_events
    from events
)
,calculate_next as
(
  select *
  ,case when counter_events = 1 AND code = '000A' then LEAD(code,1,NULL)OVER(PARTITION BY group_id ORDER BY created_date) else null end next_code
  ,last_value(code)OVER(PARTITION BY group_id ORDER BY created_date desc) AS last_code
,last_value(created_date)OVER(PARTITION BY group_id ORDER BY created_date desc) AS last_created_date
  from data_set
  order by group_id,created_date
)
select group_id
,max(last_code) last_code
,max(last_created_date) last_created_date
,max(next_code) after_last_000A
from calculate_next
group by group_id
order by group_id;

How to get lead value of a specific case in Postgresql?

2 Answers2