The 20 examples below showcase practical SQL Server techniques and solutions drawn from the kinds of database challenges I have encountered throughout my career. Each example begins with a real-world scenario and the problem I was solving, followed by the SQL Server code and a technical explanation of how the solution works. Together, these examples demonstrate my ability to design queries, manipulate and analyze data, and develop efficient database solutions for real-world business applications.
Year-Over-Year Sales Growth with Window Functions
When an executive asks, ‘How are we doing compared to last year?’, they don’t want a static list. I built this query to look back at the same month from the previous year, calculate the difference, and show the growth percentage side-by-side.
SELECT
SalesYear, SalesMonth, MonthlySales,
LAG(MonthlySales, 12) OVER (ORDER BY SalesYear, SalesMonth) AS PriorYearSales,
(MonthlySales – LAG(MonthlySales, 12) OVER (ORDER BY SalesYear, SalesMonth))
/ LAG(MonthlySales, 12) OVER (ORDER BY SalesYear, SalesMonth) * 100 AS YoYGrowthPct
FROM MonthlySalesSummary;
This utilizes the LAG window function to access data from a previous row (offset by 12 months) without a self-join. It demonstrates proficiency in time-series analysis and set-based calculations.
Recursive CTE for Organizational Hierarchies
Managing ‘who reports to whom’ in a company is tricky because the chain can be 2 levels or 20 levels deep. I used a recursive query that starts at the CEO and drills down through every manager to map out the entire staff structure automatically.
WITH OrgChart AS (
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employees WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.Name, e.ManagerID, oc.Level + 1
FROM Employees e
INNER JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT * FROM OrgChart ORDER BY Level;
Implements a Recursive Common Table Expression (CTE). It handles hierarchical data by joining an anchor member with a recursive member, essential for parent-child relationship modeling.
Dynamic Pivot for Monthly Financial Reporting
Static reports break when a new month is added. I wrote a script that ‘sniffs’ the data, finds all available months, and automatically rotates the rows into columns so the report expands itself without manual coding.
DECLARE @Cols AS NVARCHAR(MAX), @Query AS NVARCHAR(MAX);
SELECT @Cols = STUFF((SELECT ‘,’ + QUOTENAME(MonthName)
FROM SalesPeriods FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 1, ”);
SET @Query = ‘SELECT Region, ‘ + @Cols + ‘ FROM (SELECT Region, MonthName, Revenue FROM SalesData) x
PIVOT (SUM(Revenue) FOR MonthName IN (‘ + @Cols + ‘)) p ‘;
EXEC sp_executesql @Query;
Uses Dynamic SQL and the PIVOT operator. It leverages FOR XML PATH for string aggregation of column headers, allowing for flexible schema output based on underlying data.
Gaps and Islands: Detecting System Downtime
Sometimes you need to know when a system wasn’t working. This query finds ‘islands’ of uptime and ‘gaps’ of downtime, grouping consecutive events together to tell us exactly how long a failure lasted.
SELECT Status, MIN(EventTime) AS StartTime, MAX(EventTime) AS EndTime
FROM (
SELECT Status, EventTime,
ROW_NUMBER() OVER(ORDER BY EventTime) –
ROW_NUMBER() OVER(PARTITION BY Status ORDER BY EventTime) AS GroupID
FROM SystemLogs
) t
GROUP BY Status, GroupID;
A classic Gaps and Islands solution using dual ROW_NUMBER() functions. By subtracting a partitioned row number from a global one, we create a constant value for consecutive rows, allowing for grouping of sequences.
Slowly Changing Dimensions (SCD Type 2) with MERGE
In historical reporting, you can’t just overwrite a customer’s address; you need to keep a record of where they used to live. I used a MERGE statement to automatically end-date old records and insert new ones in a single atomic step.
MERGE TargetTable AS T
USING SourceTable AS S ON T.ID = S.ID
WHEN MATCHED AND T.IsCurrent = 1 AND T.HashKey <> S.HashKey THEN
UPDATE SET T.EndDate = GETDATE(), T.IsCurrent = 0
WHEN NOT MATCHED THEN
INSERT (ID, Val, StartDate, IsCurrent) VALUES (S.ID, S.Val, GETDATE(), 1);
Demonstrates SCD Type 2 logic via the MERGE statement. It manages data versioning by identifying attribute changes and performing conditional updates/inserts simultaneously.
Processing Complex JSON Data Packages
Modern apps often send data in JSON format. Instead of asking developers to flatten it, I wrote SQL that parses nested JSON arrays directly into relational tables, making it immediately ready for Power BI.
SELECT HeaderID, CustomerName, ItemID, Quantity
FROM SalesJSON
CROSS APPLY OPENJSON(JsonData, ‘$.items’)
WITH (ItemID INT ‘$.id’, Quantity INT ‘$.qty’);
Uses OPENJSON with a defined schema (WITH clause) and CROSS APPLY. This allows for the “shredding” of nested JSON arrays into a standard row-and-column format.
Performance Tuning with Indexed Views
When a dashboard was taking 30 seconds to load a million rows, I created a ‘materialized’ view. This tells SQL Server to physically store the calculation results on the disk, making the report load almost instantly.
CREATE VIEW dbo.vw_ExecutiveSummary WITH SCHEMABINDING AS
SELECT RegionID, SUM(TotalAmount) AS TotalRevenue, COUNT_BIG(*) AS TransactionCount
FROM dbo.SalesTransactions
GROUP BY RegionID;
GO
CREATE UNIQUE CLUSTERED INDEX IDX_VwSummary ON vw_ExecutiveSummary(RegionID);
Implements an Indexed View. By applying a clustered index to a view with SCHEMABINDING, the engine persists the result set, drastically reducing CPU cost for expensive aggregations.
The OUTPUT Clause for Real-Time Auditing
If data is deleted or changed, I want to know exactly what the values were before the change. I used an ‘OUTPUT’ clause to grab deleted rows and move them into an archive table as it happens.
DELETE FROM Transactions
OUTPUT DELETED.*, GETDATE(), USER_NAME() INTO AuditLogTable
WHERE TransactionDate < ‘2020-01-01’;
Utilizes the OUTPUT clause to access the DELETED and INSERTED virtual tables during DML operations. This ensures transactional consistency for auditing purposes.
Handling Deadlocks with Snapshot Isolation
Reports often ‘lock’ tables, stopping other people from doing their work. I enabled a feature that lets people read data without blocking others, ensuring the report shows the ‘last known good’ version of the data.
ALTER DATABASE ReportingDB SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT SUM(Amount) FROM HeavyTransactionTable;
COMMIT;
Implements Snapshot Isolation. It uses row versioning in tempdb to provide statement-level or transaction-level consistency without using shared locks, eliminating read/write blocking.
Ranking Data with TIES (Top N Analysis)
If you ask for the top 3 salespeople but the 3rd and 4th place are a tie, a standard report hides the tie. My query ensures that if multiple people have the same score, they all get recognized in the top rank.
SELECT TOP 3 WITH TIES SalesPerson, TotalSales
FROM SalesPerformance
ORDER BY TotalSales DESC;
Uses the TOP N WITH TIES clause. This is vital for fair ranking reports where equal values must be treated as a single rank position.
Stored Procedure with Error Handling and Transactions
Financial transfers must be ‘all or nothing.’ I built this procedure so that if Part A succeeds but Part B fails, the system automatically ‘undos’ Part A, ensuring the books always balance.
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Accounts SET Balance -= 100 WHERE ID = 1;
UPDATE Accounts SET Balance += 100 WHERE ID = 2;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
Demonstrates Structured Error Handling (TRY…CATCH) and Explicit Transactions. It ensures ACID compliance by rolling back the transaction if any part of the batch fails.
Using CROSS APPLY for Row-Level Logic
I needed to show each customer’s last three purchases inside a single report. Standard joins don’t work for that, so I used a ‘Cross Apply’ which acts like a smart loop to find the specific history for every individual row.
SELECT c.Name, lastSales.OrderDate, lastSales.Amount
FROM Customers c
CROSS APPLY (
SELECT TOP 3 OrderDate, Amount
FROM Orders o WHERE o.CustomerID = c.CustomerID
ORDER BY OrderDate DESC
) AS lastSales;
Leverages CROSS APPLY, which functions similarly to a correlated subquery but allows for multiple columns and rows to be returned. It is significantly more efficient than using cursors for row-by-row logic.
Temporal Tables for “Point in Time” Analysis
I was asked, ‘What did our inventory look like six months ago today?’ Because I enabled Temporal Tables, SQL Server maintains a hidden history for me, allowing me to travel back in time to any specific second.
SELECT * FROM Inventory
FOR SYSTEM_TIME AS OF ‘2024-02-01 12:00:00’;
Utilizes System-Versioned Temporal Tables. This feature automatically manages a history table, allowing for AS OF queries to reconstruct data as it existed at any point in time.
String Aggregation for Clean Lists
Database data is usually vertical (one row per item). I wrote a query to ‘smush’ those rows together so a manager can see all products in an order separated by commas in a single cell.
SELECT OrderID,
STRING_AGG(ProductName, ‘, ‘) WITHIN GROUP (ORDER BY ProductName) AS ProductList
FROM OrderDetails
GROUP BY OrderID;
Employs the STRING_AGG function (introduced in SQL 2017). It provides a high-performance, native way to concatenate string values across rows, replacing older, more complex FOR XML PATH methods.
CTE-Based Duplicate Removal
Data isn’t always perfect. I built a cleanup script that identifies duplicate records (where the same info was entered twice) and safely deletes the extras while keeping the original ‘clean’ version.
WITH DeDupe AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Email ORDER BY CreatedDate DESC) as RN
FROM Users
)
DELETE FROM DeDupe WHERE RN > 1;
Uses a CTE with ROW_NUMBER(). By partitioning by a unique identifier and ordering by date, we can target specific duplicate rows for deletion in a single set-based operation.
Quantile Analysis (NTILE) for Customer Segmentation
To help marketing, I divided our entire customer list into four equal ‘buckets’ based on their spending. This lets us target the ‘Top 25%’ (the big spenders) with a different strategy than the bottom 25%.
SELECT CustomerID, TotalSpent,
NTILE(4) OVER(ORDER BY TotalSpent DESC) AS SpendingQuartile
FROM CustomerSalesSummary;
Implements the NTILE window function. This distributes rows into a specified number of ranked groups, essential for statistical distribution and bucket-based analysis.
Conditional Aggregation for Side-by-Side KPIs
Managers want to see ‘Actuals vs. Targets’ in the same line. I used conditional logic inside my math formulas to calculate both numbers at the same time, giving a clean comparison in one report.
SELECT Region,
SUM(CASE WHEN Type = ‘Actual’ THEN Amount ELSE 0 END) AS TotalActual,
SUM(CASE WHEN Type = ‘Target’ THEN Amount ELSE 0 END) AS TotalTarget
FROM PerformanceData
GROUP BY Region;
Uses Filtered Aggregation (SUM + CASE). This is a high-performance alternative to multiple joins, allowing the engine to calculate different metrics in a single pass over the data.
Efficient Data Paging (OFFSET/FETCH)
When a report has 100,000 rows, you can’t show them all at once. I wrote a paging script that only pulls 50 rows at a time, making the application feel lightning-fast for the user.
SELECT ProductID, Name FROM Products
ORDER BY Name
OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY;
Demonstrates the OFFSET FETCH clause. This is the standard, modern way to handle server-side pagination, ensuring only a subset of data is transmitted over the network.
Capturing Percent of Total (Window Aggregates)
It’s one thing to know a store made $10,000. It’s another to know that $10,000 represents 40% of the entire company’s revenue. I calculated these percentages dynamically without needing a separate summary table.
SELECT StoreName, Revenue,
Revenue / SUM(Revenue) OVER() * 100 AS PctOfTotalRevenue
FROM StoreSales;
Uses Window Aggregates (SUM over an empty OVER() clause). This allows for the combination of detail-level data and grand-total-level data in a single row without a GROUP BY clause.
Validating and Cleaning Strings with PATINDEX
People often mistype email addresses or phone numbers. I built a ‘scrubber’ that uses pattern matching to find any records that contain illegal characters, ensuring our data is clean for the next marketing blast.
SELECT UserEmail FROM Users
WHERE PATINDEX(‘%[^a-z0-9@._-]%’, UserEmail) > 0;
Leverages PATINDEX with RegEx-like patterns. This allows for sophisticated string validation using wildcard characters to identify rows that do not conform to specific data quality standards.