Real-World Scenario: Sales Analytics

WITH monthly AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(
         100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
         / LAG(revenue) OVER (ORDER BY month), 2
       ) AS mom_growth_pct
FROM monthly;

MoM growth % = (current - previous) / previous * 100. The LAG window function gets the previous month. This is a standard KPI in every sales dashboard.

WITH ranked AS (
  SELECT region, salesperson, SUM(amount) AS total_sales,
         RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rnk
  FROM sales
  GROUP BY region, salesperson
)
SELECT region, salesperson, total_sales
FROM ranked
WHERE rnk = 1;

Top performer per region. Note: GROUP BY happens before window function, so RANK() is applied to the grouped result. This pattern is used in sales leaderboard reports.

SELECT salesperson,
       SUM(amount) AS sales,
       ROUND(
         100.0 * SUM(amount)
         / SUM(SUM(amount)) OVER (), 2
       ) AS pct_of_total
FROM sales
GROUP BY salesperson
ORDER BY sales DESC;

SUM(SUM(amount)) OVER () — nested window function. The inner SUM groups by salesperson, the outer SUM() OVER () gives the grand total. Dividing gives % contribution. Used in market share analysis.

SELECT c.customer_id, c.name,
       MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING MAX(o.order_date) < DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    OR MAX(o.order_date) IS NULL;

Churn detection. LEFT JOIN includes customers with no orders (MAX will be NULL). HAVING filters to those with no recent orders. Used in CRM systems and retention campaigns.

SELECT s.salesperson,
       SUM(s.actual_sales) AS achieved,
       t.target,
       ROUND(100.0 * SUM(s.actual_sales) / t.target, 1) AS achievement_pct
FROM sales s
JOIN targets t ON s.salesperson = t.salesperson
GROUP BY s.salesperson, t.target
ORDER BY achievement_pct DESC;

Achievement % = actual / target * 100. This is a standard scorecard metric in every sales team. The JOIN brings in the target from a separate targets table.