Window Function Challenges — 6 Problems

SELECT c.first_name, c.last_name,
       SUM(o.total_amount) AS total_spent,
       RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS spending_rank
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY spending_rank;

SELECT category, product_name, price
FROM (
  SELECT category, product_name, price,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY price DESC, product_name ASC
    ) AS rn
  FROM products
) ranked
WHERE rn = 1
ORDER BY price DESC;

SELECT
  order_date,
  SUM(total_amount) AS daily_revenue,
  SUM(SUM(total_amount)) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders
WHERE status = 'completed'
GROUP BY order_date
ORDER BY order_date;

SELECT c.first_name, c.last_name,
       o.order_id, o.order_date,
       LAG(o.order_date) OVER (
         PARTITION BY o.customer_id
         ORDER BY o.order_date
       ) AS previous_order_date,
       DATEDIFF(
         o.order_date,
         LAG(o.order_date) OVER (
           PARTITION BY o.customer_id
           ORDER BY o.order_date
         )
       ) AS days_since_last_order
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_date;

SELECT p.category, p.product_name,
       SUM(oi.quantity * oi.unit_price) AS product_revenue,
       SUM(SUM(oi.quantity * oi.unit_price)) OVER (
         PARTITION BY p.category
       ) AS category_total,
       ROUND(
         SUM(oi.quantity * oi.unit_price) * 100.0 /
         SUM(SUM(oi.quantity * oi.unit_price)) OVER (
           PARTITION BY p.category
         ), 1
       ) AS pct_of_category
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category, p.product_name
ORDER BY p.category, product_revenue DESC;

SELECT category, product_name, revenue, rnk
FROM (
  SELECT p.category, p.product_name,
         SUM(oi.quantity * oi.unit_price) AS revenue,
         DENSE_RANK() OVER (
           PARTITION BY p.category
           ORDER BY SUM(oi.quantity * oi.unit_price) DESC
         ) AS rnk
  FROM products p
  JOIN order_items oi ON p.product_id = oi.product_id
  GROUP BY p.category, p.product_name
) ranked
WHERE rnk <= 2
ORDER BY category, rnk;