df-window-func-leadlag-sql.py

spark.sql("""
WITH CALCULATED (
    SELECT 
        brand, 
        sum(price) as price_sum
        
    FROM PURCHASE
    
    WHERE 
        brand IS NOT NULL 
        
    GROUP BY brand
), 

RANKED (
    SELECT 
        brand, 
        lag(price_sum, 1) OVER (PARTITION BY 1 ORDER BY price_sum DESC) as price_sum_prev,
        price_sum as price_sum_current, 
        lead(price_sum, 1) OVER (PARTITION BY 1 ORDER BY price_sum DESC) as price_sum_next,
        dense_rank() OVER (PARTITION BY 1 ORDER BY price_sum DESC) as rank        
        
    FROM CALCULATED
)

SELECT *
FROM RANKED
ORDER BY rank ASC
""").show(truncate=False)