Ranking & Running Totals

SELECT first_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees
WHERE salary BETWEEN 60000 AND 90000;

WITH ranked AS (
  SELECT category, name, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS pos
  FROM products
)
SELECT category, name, price
FROM ranked
WHERE pos <= 2
ORDER BY category, pos;

WITH latest AS (
  SELECT customer_id, id, order_date, status,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS n
  FROM orders
)
SELECT c.name, l.id AS last_order, l.order_date, l.status
FROM latest AS l
JOIN customers AS c ON c.id = l.customer_id
WHERE l.n = 1
ORDER BY l.order_date DESC;

SELECT id, customer_id, order_date,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order,
       julianday(order_date) - julianday(LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS days_since
FROM orders
WHERE customer_id IN (1, 2)
ORDER BY customer_id, order_date;

SELECT order_id, product_id, quantity,
       SUM(quantity) OVER (ORDER BY order_id, product_id) AS running_units
FROM order_items
WHERE order_id <= 4;

SELECT month, orders,
       round(AVG(orders) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM (
  SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
  FROM orders
  GROUP BY month
);

SELECT first_name, salary,
       FIRST_VALUE(first_name) OVER (ORDER BY salary DESC) AS top_earner,
       NTILE(3) OVER (ORDER BY salary DESC) AS pay_band
FROM employees
WHERE department_id IS NOT NULL;