Reference queries for common warehouse and analytics engineering tasks.
Build Running Totals With Window Functions: A clean cumulative-sum pattern for trend lines, balances, and progressive totals.
Check For Null And Duplicate Keys: A fast validation query for checking whether a model has null keys or duplicate identifiers.
Choose Views for Flexibility and Tables for Stability: A guide to how views support flexibility, how tables support stable outputs, and how both can be used in a pipeline.
Choose the Right Ranking Function for Ties: A side-by-side ranking pattern for deciding between ROW_NUMBER, RANK, and DENSE_RANK.
Compare Each Row to Its Group Average: A practical pattern for flagging records that outperform or underperform their peer group.
Deduplicate Rows With Row Number: A warehouse-friendly SQL pattern for keeping the latest record per business key.
Detect Missing Values in a Sequence: A sequence-gap check for invoice numbers, tickets, or any ordered identifier stream.
Find Customers Active in Consecutive Months: A retention-style query for identifying customers who returned in back-to-back months.
Find the Nth Highest Value With Dense Rank: A durable pattern for returning the nth highest value while handling ties correctly.
Keep the Top Products Within Each Category: A top-n-per-group pattern for ranking products without mixing categories together.
Operate SQL Server Triggers and Stored Procedures Safely: A practical SQL Server pattern for using triggers carefully and writing transaction-safe stored procedures.
Pivot Rows Into Reporting Columns: A conditional-aggregation pattern for turning row values into side-by-side reporting columns.
Schedule SQL Server Agent Jobs by Time and Trigger: A SQL Server Agent guide for time-based schedules, trigger-driven job starts, and separating scheduling from job logic.
Standardize Text Values With SQL String Functions: A lightweight cleanup pattern for normalizing free-text fields before analysis.
Use a CTE to Stage a Complex Query: A readable SQL pattern for splitting one large transformation into named steps.