Need to get an id associated with the most recent column value

Question

SQL QUERY

select id, month_id, id_type,
      max(case when immediate_prev <> id_type then immediate_prev
              end) over (partition by id, id_type, (seqnum - seqnum_2)
              ) as id_type_prev
from (select *,
            row_number() over (partition by id order by month_id) as seqnum,
            row_number() over (partition by id, id_type order by month_id) as seqnum_2,
            lag(id_type) over (partition by id order by month_id) as immediate_prev
      from `my_table`
      WHERE id = 123
        )
ORDER BY month_id asc

my_table data

id|month_id|id_type
123|202001|aaa
123|202002|aaa
123|202003|aaa
123|202004|bbb
123|202005|bbb
123|202006|bbb

Query return data

id|month_id|id_type|id_type_prev
123|202001|aaa|null
123|202002|aaa|null
123|202003|aaa|null
123|202004|bbb|aaa
123|202005|bbb|aaa
123|202006|bbb|aaa

I have a SQL query that returns the previous id_type value for a given id. I would also like to know the month_id of the previous id_type but I am not sure how to get this information. Above is the table data and what my current query returns.

Below is the additional data I am after, I woud like help getting the month_id_prev added to my above query. This would be the previous id_type's most recent month_id.

id|month_id|id_type|id_type_prev|month_id_prev
123|202001|aaa|null|null
123|202002|aaa|null|null
123|202003|aaa|null|null
123|202004|bbb|aaa|202003
123|202005|bbb|aaa|202003
123|202006|bbb|aaa|202003

Ricco D · Answer

Consider the approach below using your sample data:

with sample_data as (
  select 123 as id, 202001 as month_id, 'aaa' as id_type,
  union all select 123 as id, 202002 as month_id, 'aaa' as id_type,
  union all select 123 as id, 202003 as month_id, 'aaa' as id_type,
  union all select 123 as id, 202004 as month_id, 'bbb' as id_type,
  union all select 123 as id, 202005 as month_id, 'bbb' as id_type,
  union all select 123 as id, 202006 as month_id, 'bbb' as id_type,
),
cte1 as (
select id, month_id, id_type,
      max(case when immediate_prev <> id_type then immediate_prev
              end) over (partition by id, id_type, (seqnum - seqnum_2)
              ) as id_type_prev,
      latest_ym,
      lag(latest_ym) over (partition by id order by month_id) as prev_ym
from
(select *,
        row_number() over (partition by id order by month_id) as seqnum,
        row_number() over (partition by id, id_type order by month_id) as seqnum_2,
        lag(id_type) over (partition by id order by month_id) as immediate_prev,
        last_value(month_id) over (partition by id,id_type order by id) as latest_ym
from sample_data)

)

select 
  id,
  month_id,
  id_type,
  id_type_prev,
  max(if(month_id > prev_ym, prev_ym, null)) over (partition by id,id_type) as month_id_prev
from cte1
order by month_id asc

Output:

Need to get an id associated with the most recent column value

1 Answers