Given a table sales(emp_id, sale_date, amount), rank employees by total sales, compute a running total per employee, and find the top performer per month.

You can solve all three parts in a single query using window functions. First, compute total sales per employee:

SELECT
  emp_id,
  SUM(amount) OVER (PARTITION BY emp_id) AS total_sales
FROM sales;

For the running total per employee, order the rows by sale_date and use a cumulative sum:

SELECT
  emp_id,
  sale_date,
  amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM sales;

To find the top performer each month, first aggregate monthly sales per employee, then rank them within each month:

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', sale_date) AS month,
    emp_id,
    SUM(amount) AS month_sales
  FROM sales
  GROUP BY 1, 2
), ranked AS (
  SELECT
    month,
    emp_id,
    month_sales,
    RANK() OVER (PARTITION BY month ORDER BY month_sales DESC) AS rnk
  FROM monthly
)
SELECT month, emp_id, month_sales
FROM ranked
WHERE rnk = 1;

Complexity: Each window function scans the data once, so the overall time is O(n log n) due to the internal sorting required for the ORDER BY clauses. The space complexity is O(n) for the intermediate result sets. This approach is efficient and works in most modern RDBMS that support window functions (PostgreSQL, SQL Server, Oracle, MySQL 8+, etc.).