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 ValueInput: CoffeeShopiddrink1Americano2NULL3Latte4NULLLAST_VALUE(drink IGNORE NULLS)OVER (ORDER BY id)Output: Fixediddrink1Americano2Americano3Latte4Latte
Understanding the Visualization
1
Input
Table with NULL drink values
2
Window Function
LAG/LAST_VALUE carries forward previous values
3
Output
All NULLs replaced with previous drinks
Key Takeaway
🎯 Key Insight: Use window functions to carry forward values across ordered rows when filling missing data

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