Generate first and last status in separate columns SQL

Question

I have the following data:

And using the query below I get the follwing:

What I would like to get as a result:

How can I modify my query to get the last table?
with minmax as (select t1.*,
                       case when rank=(min(rank) over (partition by id)) then status end   as first_status,
                       case when rank=(min(rank) over (partition by id)) then status_date end   as first_status_date,
                       case when rank=(max(rank) over (partition by id)) then status end   as last_status,
                       case when rank=(max(rank) over (partition by id)) then status_date end   as last_status_date,
                       max(rank) over (partition by id) as max
                from (select id,
                             status_date,
                             status,
                             rank()
                             over (partition by id order by id, status_date) as rank
                      from history_table as hist
                     ) as t1
                group by 1, 2, 3, 4
                order by id, status_date)
select distinct 
                id,
                first_status,
                first_status_date,
                last_status,
                last_status_date
from id_table as idt
left join minmax as mm on idt.id=mm.id

George Joseph · Answer

You should be able to use this using first_value and last_value analytical functions as follows
select distinct 
       id
      ,first_value(status) over(partition by id order by status_date) as first_status
      ,first_value(status_date) over(partition by id order by status_date) as first_status_date
      ,last_value(status) over(partition by id order by status_date      range between unbounded preceding and unbounded following) as last_status
      ,last_value(status_date) over(partition by id order by status_date range between unbounded preceding and unbounded following) as last_status_date
  from t   
order by id

+-----+--------------+---------------------+-------------+---------------------+
| id  | first_status |  first_status_date  | last_status |  last_status_date   |
+-----+--------------+---------------------+-------------+---------------------+
| 123 | appointment  | 2018-01-09 13:18:09 | left        | 2018-09-20 14:51:13 |
| 547 | appointment  | 2018-02-14 09:43:15 | sold        | 2018-06-06 07:36:56 |
+-----+--------------+---------------------+-------------+---------------------+

Here is a db fiddle link
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=564f022fcf2d84afb054b0a48fa438ca

GMB · Answer

I think this is best solved with window functions and distinct:
select distinct
    id,
    first_value(status) over(partition by id order by status_date) first_status,
    min(status_date)         over(partition by id) first_status_date,
    first_value(status) over(partition by id order by status_date desc) last_status,
    max(status_date)         over(partition by id) last_status_date
from history_table
order by id

A more classical approach is row_number() and conditional aggregation - but I suspect that's a bit less efficient here (and it's lengthier too):
select
    id,
    min(status) filter(where rn_asc = 1) first_status,
    min(status_date) first_status_date,
    min(status) filter(where rn_desc = 1) last_status,
    min(status_date) last_status_date,
from (
    select 
        h.*,
        row_number() over(partition by id order by status_date) rn_asc,
        row_number() over(partition by id order by status_date desc) rn_desc
    from history_table h
) h
where 1 in (rn_asc, rn_desc)
group by id

Generate first and last status in separate columns SQL

2 Answers

Add your own answers!

Ask a Question