AI for SQL Server

SELECT TOP 5 r.name, COUNT(d.id) AS closed_deals, SUM(d.value) AS total_value
FROM sales_reps r
JOIN deals d ON d.rep_id = r.id
WHERE d.status = 'closed'
  AND d.closed_date >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0)
GROUP BY r.name
ORDER BY total_value DESC;

WITH monthly AS (
  SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0) AS month,
         SUM(total) AS revenue
  FROM orders
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0)
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       CASE WHEN LAG(revenue) OVER (ORDER BY month) > 0
            THEN (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / LAG(revenue) OVER (ORDER BY month)
            ELSE NULL END AS growth_pct
FROM monthly
ORDER BY month DESC;

SELECT d.name AS department, AVG(e.salary) AS avg_salary, COUNT(e.id) AS headcount
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.name
ORDER BY avg_salary DESC;