Change Null Values in a Table to the Previous Value

Change Null Values in a Table to the Previous Value

What is this problem about?

The "Change Null Values in a Table to the Previous Value interview question" is a SQL/Database challenge often called "Forward Fill" or "Last Observation Carried Forward" (LOCF). You are given a table where some rows have NULL values in a specific column. You need to replace each NULL with the value from the most recent non-null row (based on a chronological ID or timestamp).

Why is this asked in interviews?

Deloitte and other data-heavy firms ask the "Change Null Values in a Table coding problem" because it is a common data cleaning requirement in time-series analysis. It tests your knowledge of Window Functions and the ability to create synthetic grouping keys. It evaluations your understanding of how to process records in a specific order while maintaining state across rows.

Algorithmic pattern used

This problem typically uses Window Functions and a Two-Pass Grouping pattern.

1. Identify Groups: Create a custom group ID for each row. The group ID for a row is the count of non-null values in that column from the beginning of the table up to that row. This ensures that a non-null value and all the following NULLs share the same group ID.
2. Window Function: Within each group, use FIRST_VALUE() or a similar function to propagate the non-null value to the entire group.
3. Ordering: Always ensure you have a column (like an id or timestamp) to define the "previous" relationship.

Example explanation

Table: (1, 'A'), (2, NULL), (3, NULL), (4, 'B'), (5, NULL)

1. Count non-nulls:
   • Row 1: 'A' (Count=1). Group ID = 1.
   • Row 2: NULL (Count=1). Group ID = 1.
   • Row 3: NULL (Count=1). Group ID = 1.
   • Row 4: 'B' (Count=2). Group ID = 2.
   • Row 5: NULL (Count=2). Group ID = 2.
2. Fill: In Group 1, the first value is 'A'. In Group 2, the first value is 'B'. Result: (1, 'A'), (2, 'A'), (3, 'A'), (4, 'B'), (5, 'B').

Common mistakes candidates make

• Incorrect Ordering: Forgetting to specify the ORDER BY clause in the window function, which makes the result non-deterministic.
• Self-Joins: Attempting a complex self-join to find the "nearest" previous ID, which is much less efficient than window functions ($O(N^2)$ vs $O(N \log N)$).
• Hardcoding IDs: Assuming IDs are perfectly sequential (1, 2, 3...) when they might have gaps.

Interview preparation tip

Master the OVER (ORDER BY ...) clause in SQL. Understanding how to use cumulative counts to "partition" data into islands of related records is a powerful "Database interview pattern" skill.