Watch 4 video solutions for Change Null Values in a Table to the Previous Value, a medium level problem involving Database. This walkthrough by Everyday Data Science has 864 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
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.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subquery Backtracking | O(n²) | O(1) | Small datasets or databases without window function support |
| Window Function with Cumulative Grouping | O(n) | O(n) | General case; scalable analytical SQL queries |
