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

You are given a CoffeeShop table that tracks coffee orders by ID and drink name.

The table contains some NULL values in the drink column. Your task is to replace each NULL drink value with the drink name from the previous row that contains a non-null value.

Key requirements:

  • Fill NULL values with the most recent non-null drink name
  • Process rows in order by id
  • The first row is guaranteed to have a non-null drink value

Table Schema

CoffeeShop
Column Name Type Description
id PK int Primary key, order ID
drink varchar Name of the drink ordered, some values are NULL
Primary Key: id
Note: Each row represents a coffee order. Some drink values are NULL and need to be filled with the previous non-null drink name.

Input & Output

Example 1 — Basic NULL Filling
Input Table:
id drink
1 Americano
2
3 Latte
4
5
Output:
id drink
1 Americano
2 Americano
3 Latte
4 Latte
5 Latte
💡 Note:

The NULL values in rows 2, 4, and 5 are replaced with the most recent non-null drink name. Row 2 gets 'Americano' from row 1, while rows 4 and 5 get 'Latte' from row 3.
Example 2 — No NULL Values
Input Table:
id drink
1 Espresso
2 Cappuccino
3 Mocha
Output:
id drink
1 Espresso
2 Cappuccino
3 Mocha
💡 Note:

When there are no NULL values in the drink column, the output remains unchanged as all drinks already have valid names.
Example 3 — Consecutive NULLs
Input Table:
id drink
1 Tea
2
3
4
Output:
id drink
1 Tea
2 Tea
3 Tea
4 Tea
💡 Note:

Multiple consecutive NULL values are all filled with the same previous non-null value 'Tea' from row 1.

Constraints

  • 1 ≤ id ≤ 1000
  • drink length is between 1 and 20 characters when not NULL
  • The first row is guaranteed to have a non-null drink value

Visualization

Tap to expand
Change NULL Values to Previous Value INPUT TABLE id drink 1 Coffee 2 NULL 3 Tea 4 NULL 5 NULL 6 Juice = NULL values to fill Ordered by id First row is NOT NULL ALGORITHM STEPS 1 Self Join Table Join table on itself 2 Find Previous Non-NULL WHERE c2.id <= c1.id AND c2.drink IS NOT NULL 3 Get Max ID MAX(c2.id) gets closest previous non-null row 4 Replace NULLs Select drink from the matching previous row SELECT c1.id, c2.drink FROM CoffeeShop c1, c2 WHERE c2.id = MAX(...) GROUP BY c1.id FINAL RESULT id drink 1 Coffee 2 Coffee 3 Tea 4 Tea 5 Tea 6 Juice = Filled from previous OK - DONE All NULLs replaced! Key Insight: The optimal solution uses a correlated subquery with self-join to find the maximum id that is less than or equal to current row's id AND has a non-null drink value. This effectively "carries forward" the last known drink value to fill all subsequent NULL values until next non-null. TutorialsPoint - Change Null Values in a Table to the Previous Value | Optimal Solution Time Complexity: O(n^2) | Space Complexity: O(n)

Related Problems

Asked in
Amazon 15 Meta 12 Microsoft 8
23.4K Views
Medium Frequency
~12 min Avg. Time
856 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