NTH_VALUE

1
SELECT 
2
  category,
3
  product_name,
4
  sales,
5
  NTH_VALUE(product_name, 2) OVER (
6
    PARTITION BY category 
7
    ORDER BY sales DESC
8
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
9
  ) AS second_best
10
FROM products;

1
SELECT DISTINCT
2
  category,
3
  NTH_VALUE(product_name, 1) OVER w AS top_1,
4
  NTH_VALUE(product_name, 2) OVER w AS top_2,
5
  NTH_VALUE(product_name, 3) OVER w AS top_3
6
FROM products
7
WINDOW w AS (
8
  PARTITION BY category 
9
  ORDER BY sales DESC
10
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
11
);

1
SELECT 
2
  NTH_VALUE(score, (COUNT(*) OVER () + 1) / 2) OVER (
3
    ORDER BY score
4
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
5
  ) AS median_ish
6
FROM test_scores;