Bits of .NET

📊 Key Takeaways: Master ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and running totals for complex analytics without self-joins or subqueries.

Complex reports used to require messy self-joins and subqueries. Window functions make them elegant and fast.

✅ ROW_NUMBER – Ranking Without Gaps

-- Find top 3 products per category
SELECT
    Category,
    ProductName,
    Price,
    ROW_NUMBER() OVER (
        PARTITION BY Category
        ORDER BY Price DESC
    ) AS RankInCategory
FROM Products
WHERE RankInCategory <= 3;

-- ✅ No subquery! ✅ No self-join!

📊 LAG & LEAD – Previous/Next Values

-- Calculate month-over-month growth
SELECT
    Month,
    Revenue,
    LAG(Revenue, 1, 0) OVER (ORDER BY Month) AS PreviousMonth,
    Revenue - LAG(Revenue, 1, 0) OVER (ORDER BY Month) AS Growth
FROM MonthlyRevenue;

-- 💡 No self-join needed!

📈 Running Totals

-- Running total of sales
SELECT
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

-- Moving average (7-day)
SELECT
    OrderDate,
    Amount,
    AVG(Amount) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS MovingAvg7
FROM Orders;

🔥 Real-World: Employee Ranking

-- Full ranking with RANK, DENSE_RANK, ROW_NUMBER
SELECT
    Department,
    EmployeeName,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum,
    RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank,
    DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DenseRank,
    NTILE(4) OVER (PARTITION BY Department ORDER BY Salary DESC) AS Quartile
FROM Employees;

-- 💡 Perfect for performance reviews!

🚀 Advanced: Percentiles

-- PERCENTILE_CONT for median salary
SELECT
    Department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary)
        OVER (PARTITION BY Department) AS MedianSalary,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Salary)
        OVER (PARTITION BY Department) AS Q1Salary,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Salary)
        OVER (PARTITION BY Department) AS Q3Salary
FROM Employees
GROUP BY Department, Salary;

-- Statistical analysis in pure SQL!