How To Get Postgres Command 'nth_value' Equivalent In Pyspark Hive Sql?

I was solving this example : https://www.windowfunctions.com/questions/grouping/5 Here, they use Oracle or postgres command nth_value to get the answer, but this is not implemented

Solution 1:

An alternative option is row_number() and a conditional window function:

select
    name,
    weight,
    coalesce(
        max(case when rn = 4 then weight end) over(order by rn),
        99.9
    ) imagined_weight
from (select c.*, row_number() over(order by weight) rn from cats c) c