-- RANK: rank orders by value per user
SELECT
  user_id,
  id AS order_id,
  total_agorot,
  RANK() OVER (
    PARTITION BY user_id          -- reset rank for each user
    ORDER BY total_agorot DESC    -- highest first
  ) AS rank_by_value,
  DENSE_RANK() OVER (
    PARTITION BY user_id
    ORDER BY total_agorot DESC
  ) AS dense_rank  -- no gaps in ranking (1,2,3 not 1,2,4)
FROM orders
WHERE status = 'delivered';

-- Running total and moving average
SELECT
  ordered_at::DATE AS date,
  SUM(total_agorot) AS daily_revenue,
  SUM(SUM(total_agorot)) OVER (
    ORDER BY ordered_at::DATE
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue,
  AVG(SUM(total_agorot)) OVER (
    ORDER BY ordered_at::DATE
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 7-day moving average
  ) AS moving_avg_7d
FROM orders
WHERE status = 'delivered'
GROUP BY ordered_at::DATE
ORDER BY date;

-- LAG/LEAD: compare to previous/next row
SELECT
  ordered_at::DATE AS date,
  SUM(total_agorot) AS revenue,
  LAG(SUM(total_agorot)) OVER (ORDER BY ordered_at::DATE) AS prev_day,
  SUM(total_agorot) - LAG(SUM(total_agorot)) OVER (ORDER BY ordered_at::DATE) AS daily_change,
  ROUND(
    100.0 * (SUM(total_agorot) - LAG(SUM(total_agorot)) OVER (ORDER BY ordered_at::DATE)) /
    NULLIF(LAG(SUM(total_agorot)) OVER (ORDER BY ordered_at::DATE), 0), 2
  ) AS pct_change
FROM orders
WHERE status = 'delivered'
GROUP BY ordered_at::DATE
ORDER BY date;
PostgreSQL Advanced — CTEs, Window Functions, JSONB

PostgreSQL Advanced — CTEs, Window Functions, JSONB

PostgreSQL Advanced — CTEs, Window Functions, JSONB
