Window queries

function(arguments) OVER (
  [PARTITION BY expression, ...]
  [ORDER BY expression [ASC | DESC], ...]
  [ROWS | RANGE frame]
)

SELECT i_item_sk, i_category, i_current_price,
       ROW_NUMBER() OVER (
         PARTITION BY i_category ORDER BY i_current_price DESC NULLS LAST, i_item_sk
       ) AS row_position
FROM demo.tpcds.item
ORDER BY i_category NULLS LAST, row_position
LIMIT 20;

SELECT i_item_sk, i_current_price,
       SUM(CAST(i_current_price AS DOUBLE)) OVER (
         ORDER BY i_item_sk ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_price_total
FROM demo.tpcds.item
WHERE i_item_sk <= 10
ORDER BY i_item_sk;

SELECT i_item_sk, i_current_price,
       LAG(i_current_price) OVER (ORDER BY i_item_sk) AS previous_price,
       LEAD(i_current_price) OVER (ORDER BY i_item_sk) AS next_price
FROM demo.tpcds.item
WHERE i_item_sk <= 8
ORDER BY i_item_sk;