Working with Window Functions

SELECT id, customer_id,
       row_number() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS rn
  FROM orders;

SELECT name, score,
       rank() OVER (ORDER BY score DESC) AS rnk
  FROM players;
-- Ties share rank, next jumps (1,1,3)

SELECT name, score, dense_rank() OVER (ORDER BY score DESC) FROM players;
-- (1,1,2)

SELECT name, score, ntile(4) OVER (ORDER BY score DESC) AS quartile FROM players;

SELECT day, revenue,
       lag(revenue, 1) OVER (ORDER BY day) AS prev_day,
       revenue - lag(revenue, 1, 0) OVER (ORDER BY day) AS delta
  FROM daily_revenue;

SELECT event_at, lead(event_at) OVER (PARTITION BY session_id ORDER BY event_at) AS next_event
  FROM events;

SELECT customer_id, placed_at,
       first_value(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS first_order_at
  FROM orders;

SELECT customer_id, placed_at,
       last_value(placed_at) OVER (
         PARTITION BY customer_id ORDER BY placed_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_order_at
  FROM orders;

SELECT customer_id,
       nth_value(placed_at, 2) OVER (
         PARTITION BY customer_id ORDER BY placed_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_order_at
  FROM orders;

SELECT customer_id, total_cents,
       sum(total_cents) OVER (PARTITION BY customer_id) AS customer_total
  FROM orders;

SELECT day, revenue,
       avg(revenue) OVER (
         ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7day_avg
  FROM daily_revenue;

SELECT id, customer_id,
       row_number() OVER w AS rn,
       sum(total_cents) OVER w AS running_total
  FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at);

SELECT day, revenue,
       sum(revenue) OVER (ORDER BY day) AS cumulative,
       100.0*revenue/sum(revenue) OVER () AS pct_of_total
  FROM daily_revenue;