Master SQL Window Functions: Your Hands-On Dojo with 12 Challenges

1. Introduction: The Power Beyond GROUP BY

Have you ever found yourself wrestling with complex self-joins or cumbersome cursors just to answer seemingly simple questions? Questions like, "how much time passed between this user's click and their last one?" or "what was the previous status of this order before it was updated?" are common frustrations for developers. The traditional SQL toolkit can make these tasks feel convoluted and inefficient.

This is where SQL window functions come in. They are a powerful, elegant feature set designed to solve precisely these kinds of problems—performing calculations across a set of table rows that are related to the current row.

This article is a hands-on "Dojo": a self-contained environment for you to practice and master these functions. We will set up a sample database and then tackle 12 practical challenges, each demonstrating a common and powerful pattern. You will be able to copy, paste, and run everything directly in your SQL Server environment to build real, practical skills.

2. The Setup: Your Personal SQL Windowing Dojo

The first step is to create our sandbox. The following self-contained script will create the WindowingDojo database, all necessary tables, and populate them with carefully designed sample data. Run this entire script in SQL Server Management Studio (SSMS) or your preferred SQL tool.

/* ============================================================
WINDOWING DOJO - SETUP
Safe to re-run: drops and recreates the database.
============================================================ */
USE master;
GO
IF DB_ID('WindowingDojo') IS NOT NULL
BEGIN
ALTER DATABASE WindowingDojo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE WindowingDojo;
END
GO
CREATE DATABASE WindowingDojo;
GO
USE WindowingDojo;
GO
/* ============================================================
Tables
============================================================ */
-- 1) Event log: append-only stream (think telemetry / audit trail)
CREATE TABLE dbo.EventLog (
EventId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
TenantId int NOT NULL,
UserId int NOT NULL,
DeviceId int NOT NULL,
EventTime datetime2(3) NOT NULL,
EventType varchar(50) NOT NULL,
Value int NULL,
CorrelationId uniqueidentifier NULL
);
-- 2) Audit: entity history (status changes, amount changes)
CREATE TABLE dbo.OrderAudit (
AuditId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
OrderId int NOT NULL,
ChangedAt datetime2(3) NOT NULL,
ChangedBy varchar(50) NOT NULL,
Status varchar(20) NOT NULL,
Amount decimal(12,2) NULL,
Note varchar(200) NULL
);
-- 3) Staging customers: duplicates, conflicting attributes (migration cleanup)
CREATE TABLE dbo.StageCustomer (
StageId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
NaturalKey varchar(50) NOT NULL, -- e.g., email or external id
FullName varchar(100) NULL,
Email varchar(100) NULL,
Phone varchar(30) NULL,
IsVerified bit NOT NULL,
SourceRank int NOT NULL, -- lower = better
LastUpdated datetime2(3) NOT NULL
);
-- 4) Device events with expected sequence (integrity checks)
CREATE TABLE dbo.DeviceSeqEvent (
Id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
DeviceId int NOT NULL,
Seq int NOT NULL,
EventTime datetime2(3) NOT NULL,
Payload varchar(100) NULL
);
-- 5) Queue: batching / fairness per tenant
CREATE TABLE dbo.WorkQueue (
WorkId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
TenantId int NOT NULL,
CreatedAt datetime2(3) NOT NULL,
Priority int NOT NULL, -- lower = higher priority
Status varchar(20) NOT NULL, -- Pending/Done
Payload varchar(100) NULL
);
-- Helpful indexes for window patterns (partition+order)
CREATE INDEX IX_EventLog_UserTime
ON dbo.EventLog (UserId, EventTime, EventId)
INCLUDE (TenantId, DeviceId, EventType, Value, CorrelationId);
CREATE INDEX IX_OrderAudit_OrderTime
ON dbo.OrderAudit (OrderId, ChangedAt, AuditId)
INCLUDE (ChangedBy, Status, Amount);
CREATE INDEX IX_DeviceSeq_DeviceSeq
ON dbo.DeviceSeqEvent (DeviceId, Seq)
INCLUDE (EventTime, Payload);
CREATE INDEX IX_WorkQueue_TenantStatusPriority
ON dbo.WorkQueue (Status, TenantId, Priority, CreatedAt, WorkId);
GO
/* ============================================================
Seed data
============================================================ */
-- EventLog: 3 tenants, 6 users, 5 devices
DECLARE @t0 datetime2(3) = '2025-01-10T09:00:00.000';
INSERT dbo.EventLog (TenantId, UserId, DeviceId, EventTime, EventType, Value, CorrelationId)
VALUES
-- Tenant 1, User 101: sessions + retries + burst
(1,101,1001,DATEADD(minute, 0,@t0),'Login',NULL, NEWID()),
(1,101,1001,DATEADD(minute, 2,@t0),'View',10, NEWID()),
(1,101,1001,DATEADD(minute, 4,@t0),'Click',1, NEWID()),
(1,101,1001,DATEADD(minute, 5,@t0),'Retry',NULL,NEWID()),
(1,101,1001,DATEADD(minute, 6,@t0),'Retry',NULL,NEWID()),
(1,101,1001,DATEADD(minute, 7,@t0),'Purchase',99,NEWID()),
-- idle gap -> new session
(1,101,1001,DATEADD(minute, 60,@t0),'Login',NULL, NEWID()),
(1,101,1001,DATEADD(minute, 63,@t0),'View',20, NEWID()),
(1,101,1001,DATEADD(minute, 64,@t0),'Logout',NULL, NEWID()),
-- Tenant 1, User 102: ties (same time), needs deterministic ordering
(1,102,1002,DATEADD(minute, 10,@t0),'Login',NULL, NEWID()),
(1,102,1002,DATEADD(minute, 10,@t0),'View',5, NEWID()),
(1,102,1002,DATEADD(minute, 11,@t0),'Click',1, NEWID()),
-- Tenant 2, User 201: anomaly spike
(2,201,2001,DATEADD(minute, 0,@t0),'Login',NULL, NEWID()),
(2,201,2001,DATEADD(minute, 1,@t0),'Click',1, NEWID()),
(2,201,2001,DATEADD(minute, 2,@t0),'Click',1, NEWID()),
(2,201,2001,DATEADD(minute, 3,@t0),'Click',1, NEWID()),
(2,201,2001,DATEADD(minute, 4,@t0),'Click',1, NEWID()),
(2,201,2001,DATEADD(minute, 5,@t0),'Click',1, NEWID()),
-- Tenant 3, User 301: normal
(3,301,3001,DATEADD(minute, 0,@t0),'Login',NULL, NEWID()),
(3,301,3001,DATEADD(minute, 15,@t0),'View',30, NEWID()),
(3,301,3001,DATEADD(minute, 16,@t0),'Logout',NULL, NEWID());
-- OrderAudit: multiple orders with legal & illegal transitions
INSERT dbo.OrderAudit (OrderId, ChangedAt, ChangedBy, Status, Amount, Note)
VALUES
(5001,'2025-01-10T09:00:00.000','svc','Created', 99.00,'new order'),
(5001,'2025-01-10T09:02:00.000','user','Paid', 99.00,'paid ok'),
(5001,'2025-01-10T09:10:00.000','svc','Shipped', 99.00,'shipped'),
(5001,'2025-01-10T09:30:00.000','svc','Delivered', 99.00,'delivered'),
(5002,'2025-01-10T09:01:00.000','svc','Created', 15.00,'new order'),
(5002,'2025-01-10T09:05:00.000','user','Paid', 15.00,'paid ok'),
(5002,'2025-01-10T09:06:00.000','svc','Created', 15.00,'BUG: went backwards'),
(5002,'2025-01-10T09:20:00.000','svc','Cancelled', 15.00,'cancelled'),
(5003,'2025-01-10T09:03:00.000','svc','Created', 120.00,'new order'),
(5003,'2025-01-10T09:04:00.000','svc','Paid', 120.00,'paid ok'),
(5003,'2025-01-10T09:05:00.000','svc','Paid', 120.00,'duplicate event'),
(5003,'2025-01-10T09:07:00.000','svc','Shipped', 120.00,'shipped');
-- StageCustomer: duplicates, varying data quality
INSERT dbo.StageCustomer (NaturalKey, FullName, Email, Phone, IsVerified, SourceRank, LastUpdated)
VALUES
('alice@example.com','Alice A','alice@example.com',NULL, 0, 3,'2025-01-09T10:00:00.000'),
('alice@example.com','Alice A','alice@example.com','+91-999',1, 5,'2025-01-10T08:00:00.000'),
('alice@example.com','Alice Anand','alice@example.com','+91-999',1, 1,'2025-01-10T09:00:00.000'),
('bob@example.com','Bob B','bob@example.com',NULL,0,2,'2025-01-10T09:00:00.000'),
('bob@example.com','Bobby B','bob@example.com','+91-888',0,2,'2025-01-10T09:05:00.000'),
('carol@example.com',NULL,'carol@example.com',NULL,0,9,'2025-01-08T09:00:00.000');
-- DeviceSeqEvent: gaps & duplicates
INSERT dbo.DeviceSeqEvent (DeviceId, Seq, EventTime, Payload)
VALUES
(1001,1,'2025-01-10T09:00:00.000','ok'),
(1001,2,'2025-01-10T09:00:01.000','ok'),
(1001,4,'2025-01-10T09:00:03.000','gap missing 3'),
(1001,4,'2025-01-10T09:00:03.500','duplicate 4'),
(1002,10,'2025-01-10T09:10:00.000','ok'),
(1002,11,'2025-01-10T09:10:01.000','ok'),
(1002,12,'2025-01-10T09:10:02.000','ok');
-- WorkQueue: multi-tenant pending work
INSERT dbo.WorkQueue (TenantId, CreatedAt, Priority, Status, Payload)
VALUES
(1,'2025-01-10T09:00:00.000', 1,'Pending','t1-p1-a'),
(1,'2025-01-10T09:00:10.000', 2,'Pending','t1-p2-a'),
(1,'2025-01-10T09:00:20.000', 2,'Pending','t1-p2-b'),
(2,'2025-01-10T09:00:00.000', 1,'Pending','t2-p1-a'),
(2,'2025-01-10T09:00:05.000', 1,'Pending','t2-p1-b'),
(3,'2025-01-10T09:00:00.000', 5,'Pending','t3-p5-a'),
(3,'2025-01-10T09:00:10.000', 1,'Pending','t3-p1-a'),
(3,'2025-01-10T09:00:20.000', 1,'Done', 't3-done');
GO

Pay close attention to the indexes created. They are not random; each one is specifically designed to support the PARTITION BY and ORDER BY clauses of the window function challenges that follow. Proper indexing is critical to making window functions performant on large datasets.

3. The 12 Challenges: From Basics to Advanced Patterns

With your dojo prepared, it's time to begin your training. Each of the following 12 katas (patterns) will build your muscle memory and deepen your understanding of window functions.

3.1 Pattern 1: Find the Previous Event (and Handle Ties)

Goal: For each user, find their previous event and calculate the time elapsed since that event. A key challenge is correctly handling events that occur at the exact same time to ensure a deterministic result.

Solution:

WITH x AS (
    SELECT
        UserId,
        EventId,
        EventTime,
        EventType,
        LAG(EventType) OVER (PARTITION BY UserId ORDER BY EventTime, EventId) AS PrevEventType,
        LAG(EventTime) OVER (PARTITION BY UserId ORDER BY EventTime, EventId) AS PrevEventTime
    FROM dbo.EventLog
)
SELECT *,
    CASE WHEN PrevEventTime IS NULL THEN NULL
        ELSE DATEDIFF(SECOND, PrevEventTime, EventTime)
    END AS SecondsSincePrev
FROM x
ORDER BY UserId, EventTime, EventId;

Analysis: The key function here is LAG(), which accesses data from a previous row within the same partition without a self-join. The PARTITION BY UserId clause defines the window for the function. The critical best practice is adding a unique column like EventId to the ORDER BY clause. This acts as a deterministic tie-breaker, guaranteeing a stable and correct logical ordering even when EventTime values are identical. This is fundamental for analyzing any time-series data.

3.2 Pattern 2: Sessionization (Group by Gaps in Time)

Goal: Group a user's events into "sessions." A new session is defined as starting after a 30-minute period of inactivity or if it's the user's first event.

Solution:

WITH e AS (
    SELECT
        UserId, EventId, EventTime, EventType,
        CASE
            WHEN LAG(EventTime) OVER (PARTITION BY UserId ORDER BY EventTime, EventId) IS NULL THEN 1
            WHEN DATEDIFF(MINUTE,
                LAG(EventTime) OVER (PARTITION BY UserId ORDER BY EventTime, EventId),
                EventTime) > 30 THEN 1
            ELSE 0
        END AS IsNewSession
    FROM dbo.EventLog
),
s AS (
    SELECT *,
        SUM(IsNewSession) OVER (
            PARTITION BY UserId ORDER BY EventTime, EventId
            ROWS UNBOUNDED PRECEDING
        ) AS SessionNo
    FROM e
)
SELECT * FROM s
ORDER BY UserId, EventTime, EventId;

Analysis: This is a powerful two-step pattern common in user analytics. First, we use LAG() to identify the start of a session by flagging rows that appear after a gap. Second, we use a running SUM() over that flag to assign a stable SessionNo. The ROWS UNBOUNDED PRECEDING frame specification is crucial here; it ensures the sum is truly cumulative across the entire partition up to the current row, which is what gives every event in the same session a consistent ID.

3.3 Pattern 3: Isolate Changes ("Edges Only")

Goal: Filter an audit log to show only the rows where the Status column has actually changed from the previous entry for the same order, ignoring consecutive duplicate statuses.

Solution:

WITH x AS (
    SELECT
        OrderId, AuditId, ChangedAt, Status,
        LAG(Status) OVER (PARTITION BY OrderId ORDER BY ChangedAt, AuditId) AS PrevStatus
    FROM dbo.OrderAudit
)
SELECT *
FROM x
WHERE PrevStatus IS NULL OR Status <> PrevStatus
ORDER BY OrderId, ChangedAt, AuditId;

Analysis: We use LAG() to fetch the PrevStatus from the prior row (within the same OrderId partition). A simple WHERE clause then filters for rows where the Status differs from the previous one. This "edges only" pattern is fundamental in ETL pipelines for loading change-data-capture (CDC) streams, ensuring you only process records that represent a meaningful state change.

3.4 Pattern 4: Build a Slowly Changing Dimension (SCD2) History

Goal: Transform a simple audit log into a proper Type 2 Slowly Changing Dimension (SCD2) history table. Each row should have ValidFrom and ValidTo date ranges, indicating the period during which that state was active.

Solution:

WITH v AS (
    SELECT
        OrderId, AuditId, ChangedAt, Status, Amount,
        LEAD(ChangedAt) OVER (PARTITION BY OrderId ORDER BY ChangedAt, AuditId) AS NextAt
    FROM dbo.OrderAudit
)
SELECT
    OrderId, Status, Amount,
    ChangedAt AS ValidFrom,
    CASE WHEN NextAt IS NULL THEN NULL
        ELSE DATEADD(MICROSECOND, -1, NextAt)
    END AS ValidTo
FROM v
ORDER BY OrderId, ValidFrom, AuditId;

Analysis: The LEAD() function is the perfect tool for this. It "peeks ahead" to find the ChangedAt timestamp of the next record within the partition. This future timestamp becomes the end time (ValidTo) for the current record's state. Note the use of NULL for the ValidTo of the last record in each partition. This is standard practice in SCD2 tables to signify that this row represents the current, active state in a data warehouse.

3.5 Pattern 5: Detect Illegal State Transitions

Goal: Audit the OrderAudit log to find invalid status changes, such as an order transitioning from Paid back to Created.

Solution:

WITH x AS (
    SELECT
        OrderId, AuditId, ChangedAt, Status,
        LAG(Status) OVER (PARTITION BY OrderId ORDER BY ChangedAt, AuditId) AS PrevStatus
    FROM dbo.OrderAudit
)
SELECT *
FROM x
WHERE PrevStatus IS NOT NULL
AND (
    (PrevStatus = 'Delivered' AND Status <> 'Delivered')
    OR (PrevStatus = 'Paid' AND Status = 'Created')
)
ORDER BY OrderId, ChangedAt, AuditId;

Analysis: This builds directly on the LAG() technique from Pattern 3, but instead of looking for any change, we are now hunting for specific, invalid changes based on business rules. The WHERE clause contains the logic defining the state machine, making this a powerful pattern for data quality audits and compliance checks.

3.6 Pattern 6: Deduplicate Data with Complex Rules

Goal: From a messy staging table, select the single "best" or "golden" record for each customer. The definition of "best" is based on a prioritized list of business rules.

Solution:

WITH r AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY NaturalKey
            ORDER BY
                CASE WHEN IsVerified = 1 THEN 0 ELSE 1 END,
                SourceRank ASC,
                LastUpdated DESC,
                StageId DESC
        ) AS rn
    FROM dbo.StageCustomer
)
SELECT *
FROM r
WHERE rn = 1
ORDER BY NaturalKey;

Analysis: ROW_NUMBER() is the core of this powerful deduplication pattern. By combining it with a complex ORDER BY clause, we can implement sophisticated, multi-level prioritization logic. This ROW_NUMBER() approach is far more declarative and often more performant than procedural loops or complex self-joins for deduplication, and it keeps all the business logic cleanly defined within the ORDER BY clause.

3.7 Pattern 7: Find Gaps and Duplicates in a Sequence

Goal: First, we'll learn to identify individual gaps and duplicates. In the next pattern, we'll use a more advanced technique to group the 'non-gap' data into consecutive islands. For now, scan a sequence of device events to identify both missing sequence numbers (gaps) and repeated sequence numbers (duplicates).

Solution:

WITH x AS (
    SELECT
        DeviceId, Seq, EventTime, Id,
        LAG(Seq) OVER (PARTITION BY DeviceId ORDER BY Seq, Id) AS PrevSeq
    FROM dbo.DeviceSeqEvent
),
y AS (
    SELECT *,
        CASE WHEN PrevSeq IS NULL THEN 0
            WHEN Seq = PrevSeq THEN 1 ELSE 0 END AS IsDuplicate,
        CASE WHEN PrevSeq IS NULL THEN 0
            WHEN Seq > PrevSeq + 1 THEN 1 ELSE 0 END AS IsGap
    FROM x
)
SELECT * FROM y
WHERE IsDuplicate = 1 OR IsGap = 1
ORDER BY DeviceId, Seq, Id;

Analysis: By using LAG(Seq), we can place the previous sequence number on the same row as the current one. This makes it trivial to write simple comparisons to check if Seq = PrevSeq (a duplicate) or if Seq > PrevSeq + 1 (a gap). This pattern is essential for data integrity checks in telemetry, IoT, or any system that relies on ordered message delivery.

3.8 Pattern 8: Group Consecutive Sequences ("Islands")

Goal: An advanced pattern to identify and summarize "islands" of consecutive sequence numbers, reporting the start, end, and count for each contiguous block.

Solution:

WITH x AS (
    SELECT
        DeviceId, Seq, Id,
        Seq - ROW_NUMBER() OVER (PARTITION BY DeviceId ORDER BY Seq, Id) AS grp
    FROM dbo.DeviceSeqEvent
),
islands AS (
    SELECT DeviceId, grp,
        MIN(Seq) AS StartSeq,
        MAX(Seq) AS EndSeq,
        COUNT(*) AS Cnt
    FROM x
    GROUP BY DeviceId, grp
)
SELECT DeviceId, StartSeq, EndSeq, Cnt FROM islands
ORDER BY DeviceId, StartSeq;

Analysis: This solves the classic "gaps and islands" problem. The key insight is subtracting a generated sequence (ROW_NUMBER()) from the data's own sequence (Seq). This calculation results in a constant value for every row within a contiguous "island," which can then be used as a grouping key. This pattern is invaluable for analyzing user engagement streaks, identifying continuous periods of server uptime from log data, or consolidating inventory availability periods.

3.9 Pattern 9: Sliding Window Anomaly Detection

Goal: Detect a "retry storm" for a user, where a storm is defined as a RetryCountLast5 >= 2. The count should be calculated over the last 5 events, including the current one.

Solution:

WITH x AS (
    SELECT
        UserId, EventId, EventTime, EventType,
        SUM(CASE WHEN EventType = 'Retry' THEN 1 ELSE 0 END) OVER (
            PARTITION BY UserId
            ORDER BY EventTime, EventId
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ) AS RetryCountLast5
    FROM dbo.EventLog
)
SELECT *,
    CASE WHEN RetryCountLast5 >= 2 THEN 1 ELSE 0 END AS IsStorm
FROM x
ORDER BY UserId, EventTime, EventId;

Analysis: The frame ROWS BETWEEN 4 PRECEDING AND CURRENT ROW defines a moving window of exactly 5 rows (4 previous + 1 current). The SUM then operates only on the data within this frame at each step. This "sliding window" is ideal for calculating moving averages, running totals, or, in this case, a running count for real-time anomaly detection in event streams.

3.10 Pattern 10: Top-N-per-Group (With Ties)

Goal: For each tenant, find the top 2 users by number of events. Critically, if multiple users are tied for second place, all of them should be included.

Solution:

WITH c AS (
    SELECT TenantId, UserId, COUNT(*) AS EventCount
    FROM dbo.EventLog
    GROUP BY TenantId, UserId
),
r AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY TenantId ORDER BY EventCount DESC) AS dr
    FROM c
)
SELECT TenantId, UserId, EventCount
FROM r
WHERE dr <= 2
ORDER BY TenantId, EventCount DESC, UserId;

Analysis: DENSE_RANK() is the right choice for this problem. If we had used RANK(), a two-way tie for first place would result in the next user getting rank 3, skipping 2 entirely. If we had used ROW_NUMBER(), one of the tied users would have been arbitrarily ranked higher, causing us to miss valid results. DENSE_RANK() handles ties by giving them the same rank and not skipping the next rank number, making it perfect for "top N with ties" scenarios.

3.11 Pattern 11: Fair-Share Batching

Goal: Select up to 2 pending work items from a queue for each tenant, ordered by Priority, CreatedAt, and WorkId. This ensures that no single tenant can monopolize processing resources.

Solution:

WITH q AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY TenantId
            ORDER BY Priority, CreatedAt, WorkId
        ) AS rn
    FROM dbo.WorkQueue
    WHERE Status = 'Pending'
)
SELECT TenantId, WorkId, Priority, CreatedAt, Payload
FROM q
WHERE rn <= 2
ORDER BY TenantId, Priority, CreatedAt, WorkId;

Analysis: This is a classic and highly practical use case for building fair, multi-tenant job queues. By using ROW_NUMBER() partitioned by TenantId, we assign a simple, ordered rank (1, 2, 3...) to each tenant's pending work. It then becomes trivial to select all rows WHERE rn <= 2 to pull a fair batch for processing.

3.12 Pattern 12: Sharding Data for Parallel Processing

Goal: Split all pending work items into 4 roughly equal batches, or "shards," that could be handed off to parallel workers for processing.

Solution:

-- Assign shard number to each pending item and verify the distribution
WITH p AS (
    SELECT WorkId, TenantId, CreatedAt, Priority, Payload,
        NTILE(4) OVER (ORDER BY TenantId, Priority, CreatedAt, WorkId) AS ShardNo
    FROM dbo.WorkQueue
    WHERE Status = 'Pending'
)
SELECT * FROM p
ORDER BY ShardNo, TenantId, Priority, CreatedAt, WorkId;

-- After assigning the shards, you can easily verify that the distribution is balanced
-- with the following query:
SELECT ShardNo, COUNT(*) AS Cnt
FROM (
    SELECT NTILE(4) OVER (ORDER BY TenantId, Priority, CreatedAt, WorkId) AS ShardNo
    FROM dbo.WorkQueue
    WHERE Status = 'Pending'
) s
GROUP BY ShardNo
ORDER BY ShardNo;

Analysis: The NTILE(N) function is designed specifically for this purpose. It divides an ordered result set into a specified number of groups (N) and assigns a group number to each row. It is an incredibly useful tool for ETL orchestration and designing workloads for parallel processing.

4. Conclusion: What Will You Build?

You have now completed the 12 katas of the Windowing Dojo. You are no longer a novice struggling with self-joins; you now possess a powerful toolkit of patterns that provide clear, performant, and often surprisingly simple solutions for a wide range of complex data problems. From data warehousing and event stream analysis to ETL batching and anomaly detection, these functions are an essential part of the modern data engineer's toolkit.

Take a moment to think about your own work and the data challenges you face.

Which of these 12 patterns could you use to solve a problem or replace a complex query in your projects right now?