find max date for each category and sub category in sql

Question

Here is how my data looks,

id  level   date
a   1   2019-05-09
a   1   2018-09-24
a   2   2019-12-06
a   2   2020-04-28
b   1   2019-08-23
b   1   2019-05-03
b   1   2020-04-30
b   2   2019-09-11
b   2   2020-07-03

I need my output to be like this,

id  level   date    max_date    level1_max_date  level2_max_date
a   1   2019-05-09  2020-04-28  2019-05-09       2020-04-28
a   1   2018-09-24  2020-04-28  2019-05-09       2020-04-28
a   2   2019-12-06  2020-04-28  2019-05-09       2020-04-28
a   2   2020-04-28  2020-04-28  2019-05-09       2020-04-28
b   1   2019-08-23  2020-07-03  2020-04-30       2020-07-03
b   1   2019-05-03  2020-07-03  2020-04-30       2020-07-03
b   1   2020-04-30  2020-07-03  2020-04-30       2020-07-03
b   2   2019-09-11  2020-07-03  2020-04-30       2020-07-03
b   2   2020-07-03  2020-07-03  2020-04-30       2020-07-03

I tried this query to get max_date , but am not sure of the logic for getting level1_max_date and level2_max_date .

select id
     , level
     , date
     , max(date) over(partition by id) as max_date
from table;

Can some one please help me .

Answer 1

Use conditional window aggregation:

select t.*,
    max(date) over(partition by id) max_date,
    max(case when level = 1 then date end) over(partition by id) level1_max_date,
    max(case when level = 2 then date end) over(partition by id) level2_max_date
from mytable t