Mastering SQL Window Functions for Advanced Error Handling

SQL window functions are powerful tools that allow you to perform calculations across sets of rows related to the current row, without collapsing your result set. They are especially useful for advanced error handling, letting you identify and manage problematic data within your queries without losing context.

In this article, we'll explore practical ways to use window functions to detect errors like duplicates, data inconsistencies, and missing values, and how to handle those errors right inside your SQL queries.

### 1. Detecting Duplicate Rows with ROW_NUMBER()

Duplicates can cause serious issues in analytics and application logic. The ROW_NUMBER() function can assign a unique rank to rows within partitions, allowing you to find and isolate duplicates easily.

SELECT
  id,
  user_email,
  ROW_NUMBER() OVER (PARTITION BY user_email ORDER BY id) AS row_num
FROM users;

-- Rows with row_num > 1 are duplicates for the same user_email.

You can use this to filter out duplicates or flag them for review.

### 2. Identifying Missing Data Using COUNT() and PARTITION

Missing or NULL values can be tricky to spot if you need to consider groups of data. Combining COUNT() as a window function allows you to count how many non-null entries exist in partitions.

SELECT
  order_id,
  customer_id,
  order_date,
  COUNT(order_date) OVER (PARTITION BY customer_id) AS orders_with_date
FROM orders;

-- If orders_with_date is less than total orders for customer, some order_date values are missing.

This helps you pinpoint customers or groups that may have incomplete data.

### 3. Highlighting Errors in Time Series Data Using LAG()

LAG() allows you to compare a row with a previous row in the ordered dataset. This is useful to spot unexpected jumps, missing sequences, or regressions.

SELECT
  transaction_id,
  transaction_date,
  amount,
  LAG(transaction_date) OVER (ORDER BY transaction_date) AS prev_date,
  CASE
    WHEN transaction_date < LAG(transaction_date) OVER (ORDER BY transaction_date) THEN 'ERROR: Date out of order'
    ELSE 'OK'
  END AS error_flag
FROM transactions;

You can tag rows where dates go backward, indicating potential data entry errors.

### 4. Combining Window Functions for Complex Error Checks

Say you want to detect customers who have placed multiple orders with the exact same amount consecutively — which could be either an error or a fraud indicator.

SELECT
  customer_id,
  order_id,
  amount,
  LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
  CASE
    WHEN amount = LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) THEN 'Duplicate consecutive amount'
    ELSE NULL
  END AS error_flag
FROM orders;

This query flags orders with duplicate consecutive amounts per customer. You can then filter or investigate flagged rows.

### Conclusion

Mastering SQL window functions like ROW_NUMBER(), LAG(), LEAD(), and COUNT() empowers you to perform advanced error detection without losing granular data context. These functions help maintain data quality by letting you filter, flag, or fix errors early in your data pipeline. Practice these patterns to become confident in handling data errors efficiently and effectively.