NTH_VALUE

1
SELECT
2
  department,
3
  employee_name,
4
  salary,
5
  NTH_VALUE(salary, 2) OVER (
6
    PARTITION BY department
7
    ORDER BY salary DESC
8
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
9
  ) as second_highest_salary
10
FROM employees;

1
SELECT
2
  category,
3
  product_name,
4
  sales,
5
  NTH_VALUE(product_name, 1) OVER w as top1,
6
  NTH_VALUE(product_name, 2) OVER w as top2,
7
  NTH_VALUE(product_name, 3) OVER w as top3
8
FROM product_sales
9
WINDOW w AS (
10
  PARTITION BY category
11
  ORDER BY sales DESC
12
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
13
);

1
SELECT
2
  department,
3
  NTH_VALUE(salary, (COUNT(*) OVER w + 1) / 2) OVER w as median_salary
4
FROM employees
5
WINDOW w AS (
6
  PARTITION BY department
7
  ORDER BY salary
8
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
9
);