Skip to main content
Talentd

Change Null Values in a Table to the Previous Value - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Deloitte
Practice this problem

Problem Statement

Table: CoffeeShop

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| drink       | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row in this table shows the order id and the name of the drink ordered. Some drink rows are nulls.

 

Write a solution to replace the null values of the drink with the name of the drink of the previous row that is not null. It is guaranteed that the drink on the first row of the table is not null.

Return the result table in the same order as the input.

The result format is shown in the following example.

 

Example 1:

Input: 
CoffeeShop table:
+----+-------------------+
| id | drink             |
+----+-------------------+
| 9  | Rum and Coke      |
| 6  | null              |
| 7  | null              |
| 3  | St Germain Spritz |
| 1  | Orange Margarita  |
| 2  | null              |
+----+-------------------+
Output: 
+----+-------------------+
| id | drink             |
+----+-------------------+
| 9  | Rum and Coke      |
| 6  | Rum and Coke      |
| 7  | Rum and Coke      |
| 3  | St Germain Spritz |
| 1  | Orange Margarita  |
| 2  | Orange Margarita  |
+----+-------------------+
Explanation: 
For ID 6, the previous value that is not null is from ID 9. We replace the null with "Rum and Coke".
For ID 7, the previous value that is not null is from ID 9. We replace the null with "Rum and Coke;.
For ID 2, the previous value that is not null is from ID 1. We replace the null with "Orange Margarita".
Note that the rows in the output are the same as in the input.

Approach Overview

Problem Overview: You have a table where some rows contain NULL values. Each NULL should be replaced with the most recent non‑NULL value that appeared earlier in the table order. The task is essentially a forward fill operation implemented using SQL.

Approach 1: Correlated Subquery Backtracking (O(n²) time, O(1) space)

For each row containing NULL, run a correlated subquery that scans earlier rows and retrieves the closest previous non‑NULL value. This works by filtering rows with a smaller ordering column and selecting the latest non‑NULL entry using ORDER BY ... DESC LIMIT 1. The method is simple but inefficient because the database performs a lookup for every row, leading to quadratic behavior on large tables. Use this only for small datasets or when window functions are unavailable.

Approach 2: Window Function with Cumulative Grouping (O(n) time, O(n) space)

The optimal strategy uses SQL window functions. First compute a running group identifier using a cumulative sum that increments whenever a non‑NULL value appears. Then apply MAX() over that group using a window partition. Since each group contains exactly one real value and several NULL rows after it, the window aggregate propagates that value forward to fill missing entries. The query processes the table in a single pass and avoids repeated lookups.

This approach relies heavily on ordered window processing and grouping techniques commonly used in database queries and analytical SQL. Internally the database scans rows once, maintains the running group number, and applies the aggregate across each partition.

Recommended for interviews: The window function solution is what interviewers expect. The correlated subquery demonstrates understanding of the requirement, but the cumulative grouping trick shows strong SQL skills and knowledge of SQL analytics features. It scales well and is the cleanest way to implement forward fill behavior directly inside the database.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Correlated Subquery BacktrackingO(n²)O(1)Small datasets or databases without window function support
Window Function with Cumulative GroupingO(n)O(n)General case; scalable analytical SQL queries

Video Solution

Leetcode MEDIUM 2388 - Change NULL Values to Previous Value - SQL Explained by Everyday Data Science • Everyday Data Science • 892 views views

Watch 2 more video solutions →

Frequently Asked Questions

Is Change Null Values in a Table to the Previous Value easy or hard?
The problem is typically rated Medium because the idea is simple but the SQL implementation requires familiarity with window functions or clever grouping tricks. Developers comfortable with analytical SQL usually recognize the forward‑fill pattern quickly.
Change Null Values in a Table to the Previous Value Python/Java solution
Outside SQL, the same logic can be implemented by iterating through rows in order and storing the last seen non‑NULL value. For each element, update the stored value when a non‑NULL appears and reuse it whenever a NULL entry is encountered. This runs in O(n) time and O(1) extra space.
How to solve Change Null Values in a Table to the Previous Value in O(n)?
Use a window function strategy. Create a cumulative sum that increases whenever a non‑NULL value appears, which forms groups of rows following each valid value. Then apply MAX(value) over each group using a window partition so the non‑NULL value fills all subsequent NULL rows in that group.
What is the best approach for Change Null Values in a Table to the Previous Value?
The most efficient solution uses SQL window functions with cumulative grouping. A running counter increases whenever a non‑NULL value appears, and a window aggregate like MAX() is applied over that group to propagate the last known value forward. This processes the table in a single pass with O(n) time complexity.
Is Change Null Values in a Table to the Previous Value asked at Google/Amazon/Meta?
Database and SQL window function questions like this commonly appear in interviews at data‑focused roles in companies such as Amazon, Google, and Meta. The problem tests familiarity with analytical SQL patterns like forward filling and partitioned window aggregation.
What data structure is used in Change Null Values in a Table to the Previous Value?
The solution relies on SQL window function mechanics rather than traditional data structures. Internally, the database maintains ordered partitions and running aggregates while scanning rows, similar to maintaining a running state during iteration.
What is the time complexity of Change Null Values in a Table to the Previous Value?
The optimal window function approach runs in O(n) time because the database scans the table once while computing the running group and applying the window aggregate. A naive correlated subquery solution can degrade to O(n²) because it searches previous rows for every NULL value.

Ready to solve this problem?

Practice Change Null Values in a Table to the Previous Value with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary