Оконные функции
SQL: полный разбор
с примерами

WITH ranked AS (
  SELECT
    user_id,
    order_id,
    amount,
    order_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY amount DESC
    ) AS rn
  FROM orders
)
SELECT *
FROM ranked
WHERE rn <= 3;

SELECT
  seller_id,
  revenue,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num
FROM sellers;

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month), 1)
    AS mom_growth_pct
FROM monthly;

WITH with_prev AS (
  SELECT
    user_id,
    event_time,
    LAG(event_time) OVER (
      PARTITION BY user_id
      ORDER BY event_time
    ) AS prev_time
  FROM events
),
with_flag AS (
  SELECT
    *,
    CASE WHEN prev_time IS NULL OR event_time - prev_time > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS new_session
  FROM with_prev
)
SELECT
  user_id,
  event_time,
  SUM(new_session) OVER (
    PARTITION BY user_id
    ORDER BY event_time
  ) AS session_id
FROM with_flag;

SELECT
  order_date,
  SUM(amount) AS daily_revenue,
  SUM(SUM(amount)) OVER (
    ORDER BY order_date
  ) AS running_total
FROM orders
GROUP BY order_date
ORDER BY order_date;

SELECT
  category,
  SUM(amount) AS revenue,
  ROUND(100.0 * SUM(amount)
    / SUM(SUM(amount)) OVER (), 1)
    AS pct_of_total
FROM orders
GROUP BY category
ORDER BY revenue DESC;

SELECT
  day,
  daily_users,
  ROUND(AVG(daily_users) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 0) AS ma_7d
FROM daily_stats;

WITH user_revenue AS (
  SELECT
    user_id,
    SUM(amount) AS total_revenue
  FROM orders
  GROUP BY user_id
)
SELECT NTILE(10) OVER (ORDER BY total_revenue DESC) AS decile,
  COUNT(*) AS users,
  SUM(total_revenue) AS revenue,
  ROUND(100.0 * SUM(total_revenue)
    / SUM(SUM(total_revenue)) OVER (), 1) AS pct_revenue
FROM (
  SELECT
    user_id,
    total_revenue,
    NTILE(10) OVER (ORDER BY total_revenue DESC) AS decile
  FROM user_revenue
) t
GROUP BY decile
ORDER BY decile;

SELECT
  user_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY user_id ORDER BY order_date
  ) AS prev_amount
FROM orders
HAVING amount < LAG(amount) OVER (
  PARTITION BY user_id ORDER BY order_date
);
-- Так не работает! Оконные функции нельзя в WHERE/HAVING -- Правильный способ: WITH with_prev AS (
  SELECT
    user_id, order_date, amount,
    LAG(amount) OVER (
      PARTITION BY user_id ORDER BY order_date
    ) AS prev_amount
  FROM orders
)
SELECT *
FROM with_prev
WHERE amount < prev_amount;

WITH cohort_activity AS (
  SELECT DATE_TRUNC('month', u.registration_date) AS cohort,
    DATE_PART('month',
      AGE(DATE_TRUNC('month', a.activity_date),
          DATE_TRUNC('month', u.registration_date))
    )::INT AS period,
    COUNT(DISTINCT u.user_id) AS active_users
  FROM users u
  JOIN user_activity a ON u.user_id = a.user_id
  GROUP BY 1, 2
)
SELECT
  cohort,
  period,
  active_users,
  ROUND(100.0 * active_users
    / FIRST_VALUE(active_users) OVER (
        PARTITION BY cohort ORDER BY period
      ), 1
  ) AS retention_pct
FROM cohort_activity
ORDER BY cohort, period;