Change Null Values in a Table to the Previous Value

Change Null Values in a Table to the Previous Value - Problem

Database Unknown

Imagine you're working at a coffee shop where orders are recorded in a table, but sometimes the cashier forgets to enter the drink name, leaving it as null. Your task is to fill in the missing drink names by using the most recent non-null drink ordered.

You have a CoffeeShop table with two columns:

id - A unique order ID (primary key)
drink - The name of the drink ordered (may contain null values)

Goal: Replace each null value with the drink name from the most recent previous row that is not null. The first row is guaranteed to have a non-null drink value.

Example:

id	drink
1	Latte
2	null
3	null
4	Cappuccino
5	null

Should become:

id	drink
1	Latte
2	Latte
3	Latte
4	Cappuccino
5	Cappuccino

Input & Output

example_1.sql — Basic Case

$ Input: CoffeeShop table: | id | drink | |----|-------------| | 1 | Latte | | 2 | null | | 3 | null | | 4 | Cappuccino | | 5 | null |

› Output: | id | drink | |----|-------------| | 1 | Latte | | 2 | Latte | | 3 | Latte | | 4 | Cappuccino | | 5 | Cappuccino |

💡 Note: Row 2 and 3 get filled with 'Latte' from row 1. Row 5 gets filled with 'Cappuccino' from row 4. Each null value takes the most recent non-null drink.

example_2.sql — Consecutive Non-Nulls

$ Input: CoffeeShop table: | id | drink | |----|------------| | 1 | Espresso | | 2 | Americano | | 3 | null | | 4 | null | | 5 | Mocha |

› Output: | id | drink | |----|------------| | 1 | Espresso | | 2 | Americano | | 3 | Americano | | 4 | Americano | | 5 | Mocha |

💡 Note: Rows 3 and 4 are filled with 'Americano' since it's the most recent non-null value before them. Row 5 already has a value so no change needed.

example_3.sql — Single Null

$ Input: CoffeeShop table: | id | drink | |----|-------------| | 1 | Frappuccino | | 2 | null | | 3 | Tea |

› Output: | id | drink | |----|-------------| | 1 | Frappuccino | | 2 | Frappuccino | | 3 | Tea |

💡 Note: Only row 2 has a null value, which gets replaced with 'Frappuccino' from row 1. Row 3 already has 'Tea' so it remains unchanged.

Constraints

1 ≤ number of rows ≤ 10⁵
1 ≤ id ≤ 10⁹
The first row is guaranteed to have a non-null drink value
drink values are strings with maximum length of 50 characters
id values are unique and serve as the primary key

Visualization

Tap to expand

Understanding the Visualization

Process Orders Sequentially

Read each order in sequence by ID, maintaining the natural order of the coffee shop queue

Track Last Complete Order

Keep a mental note of the last drink that was properly recorded (non-null)

Fill Missing Information

When encountering an incomplete order (null drink), use the last remembered drink name

Update Memory

Whenever a complete order is found, update the 'last drink' memory for future incomplete orders

Key Takeaway

🎯 Key Insight: The window function `LAG(drink IGNORE NULLS)` efficiently implements the "remember last complete order" logic in a single SQL operation, eliminating the need for complex nested queries or iterative processing.

Asked in

G Google 12 a Amazon 8 ⊞ Microsoft 6 f Meta 4

The optimal solution uses SQL window functions, specifically LAG(drink IGNORE NULLS) OVER (ORDER BY id), to efficiently fill null values in a single pass. This approach leverages the database engine's optimized window function processing to achieve O(n) time complexity with minimal space overhead, making it far superior to nested subqueries or iterative approaches.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Window Functions)	O(n²)	O(1)	Use nested queries to find the previous non-null value for each row
Window Function LAG (Optimal)	O(n)	O(1)	Use LAG window function with IGNORE NULLS to efficiently fill missing values in one pass

Brute Force (Window Functions) — Algorithm Steps

Identify all rows with null drink values
For each null row, create a subquery to find the maximum id that is less than current id and has non-null drink
Replace null with the drink value from that previous row
Repeat for each null value independently

Visualization

Tap to expand

Step-by-Step Walkthrough

Find Null Value

Identify a row with null drink value

Search Backwards

Scan all previous rows to find the most recent non-null drink

Replace Value

Replace the null with the found drink name

Repeat Process

Move to next null and repeat the entire backward search

Code -

solution.c — C

// SQL Solution (Brute Force)
const char* sqlQuery = 
"SELECT \n"
"    id,\n"
"    CASE \n"
"        WHEN drink IS NOT NULL THEN drink\n"
"        ELSE (\n"
"            SELECT drink \n"
"            FROM CoffeeShop c2 \n"
"            WHERE c2.id < c1.id \n"
"            AND c2.drink IS NOT NULL \n"
"            ORDER BY c2.id DESC \n"
"            LIMIT 1\n"
"        )\n"
"    END as drink\n"
"FROM CoffeeShop c1\n"
"ORDER BY id;";

// C implementation
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    int id;
    char* drink;
} CoffeeOrder;

void solution(CoffeeOrder* orders, int size) {
    for (int i = 0; i < size; i++) {
        if (orders[i].drink == NULL || strcmp(orders[i].drink, "null") == 0) {
            // Look backwards for last non-null drink
            for (int j = i - 1; j >= 0; j--) {
                if (orders[j].drink != NULL && strcmp(orders[j].drink, "null") != 0) {
                    orders[i].drink = strdup(orders[j].drink);
                    break;
                }
            }
        }
    }
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of n rows, we might scan up to n previous rows to find the last non-null value

⚠ Quadratic Growth

Space Complexity

O(1)

Only uses temporary variables for the subqueries, no additional storage needed

✓ Linear Space

23.5K Views

Medium Frequency

~15 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Window Functions) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler