Оконные функции

SELECT first_name, last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_no,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM teachers
LIMIT 10;

WITH ranked AS (
  SELECT faculty_id, first_name, last_name, salary,
         RANK() OVER (PARTITION BY faculty_id ORDER BY salary DESC) AS rnk
  FROM teachers
)
SELECT faculty_id, first_name, last_name, salary FROM ranked WHERE rnk = 1;

SELECT c.name, COUNT(*) AS products,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share
FROM products p JOIN categories c ON c.id = p.category_id
GROUP BY c.name
ORDER BY products DESC;

WITH m AS (
  SELECT EXTRACT(MONTH FROM requested_at) AS mon, SUM(fare) AS revenue
  FROM trips
  WHERE status = 'completed' AND requested_at >= '2026-01-01'
  GROUP BY EXTRACT(MONTH FROM requested_at)
)
SELECT mon, revenue, SUM(revenue) OVER (ORDER BY mon) AS running_total
FROM m ORDER BY mon;

WITH m AS (
  SELECT EXTRACT(MONTH FROM requested_at) AS mon, COUNT(*) AS trips
  FROM trips WHERE requested_at >= '2026-01-01'
  GROUP BY EXTRACT(MONTH FROM requested_at)
)
SELECT mon, trips, trips - LAG(trips) OVER (ORDER BY mon) AS diff
FROM m ORDER BY mon;