Order Log Forward Fill for Missing Drink Labels

Medium

A beverage analytics pipeline receives order rows where some drink names are missing due to upstream capture gaps. Analysts want a corrected stream where missing drink labels inherit the latest known value. Table: CoffeeShop id: unique order-row identifier drink: beverage name for that row; may be NULL Stream semantics: The row sequence in the input is meaningful. A row with NULL drink should reuse the most recent non-NULL drink from an earlier row in that same sequence. The first row is guaranteed to contain a non-NULL drink. Task: Return a corrected stream where every NULL drink has been forward-filled from the prior known drink. Output requirements: Return exactly these columns in this order: id drink Preserve original row sequence. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

CoffeeShop:
| id | drink             |
|----|-------------------|
| 9  | Rum and Coke      |
| 6  | null              |
| 7  | null              |
| 3  | St Germain Spritz |
| 1  | Orange Margarita  |
| 2  | null              |

Output

[
  {"id":9,"drink":"Rum and Coke"},
  {"id":6,"drink":"Rum and Coke"},
  {"id":7,"drink":"Rum and Coke"},
  {"id":3,"drink":"St Germain Spritz"},
  {"id":1,"drink":"Orange Margarita"},
  {"id":2,"drink":"Orange Margarita"}
]

Explanation

Rows with NULL drink inherit the most recent known drink in stream order.

Example 2

Input

CoffeeShop:
| id | drink      |
|----|------------|
| 50 | Zebra Fizz |
| 10 | null       |
| 70 | Apple Soda |
| 5  | null       |
| 99 | null       |
| 1  | Mango Tea  |
| 2  | null       |

Output

[
  {"id":50,"drink":"Zebra Fizz"},
  {"id":10,"drink":"Zebra Fizz"},
  {"id":70,"drink":"Apple Soda"},
  {"id":5,"drink":"Apple Soda"},
  {"id":99,"drink":"Apple Soda"},
  {"id":1,"drink":"Mango Tea"},
  {"id":2,"drink":"Mango Tea"}
]

Explanation

Even if id values are non-monotonic, filling follows row sequence, not numeric id order.

Example 3

Input

CoffeeShop:
| id | drink        |
|----|--------------|
| 1  | Iced Latte   |
| 2  | null         |
| 3  | null         |
| 4  | null         |
| 5  | null         |

Output

[
  {"id":1,"drink":"Iced Latte"},
  {"id":2,"drink":"Iced Latte"},
  {"id":3,"drink":"Iced Latte"},
  {"id":4,"drink":"Iced Latte"},
  {"id":5,"drink":"Iced Latte"}
]

Explanation

A single known value can propagate through a long null run.

Constraints

1 <= number of rows in CoffeeShop <= 1800
1 <= id <= 2000000000
id values are unique
drink is NULL or a non-empty string
1 <= length(drink) <= 40 when drink is not NULL
The first input row always has non-NULL drink
Return exactly two columns: id, drink
Preserve original row sequence in output
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: id, drink

Preparing your editor. You can read the problem while it loads.

Order Log Forward Fill for Missing Drink Labels

Medium

A beverage analytics pipeline receives order rows where some drink names are missing due to upstream capture gaps. Analysts want a corrected stream where missing drink labels inherit the latest known value. Table: CoffeeShop id: unique order-row identifier drink: beverage name for that row; may be NULL Stream semantics: The row sequence in the input is meaningful. A row with NULL drink should reuse the most recent non-NULL drink from an earlier row in that same sequence. The first row is guaranteed to contain a non-NULL drink. Task: Return a corrected stream where every NULL drink has been forward-filled from the prior known drink. Output requirements: Return exactly these columns in this order: id drink Preserve original row sequence. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

CoffeeShop:
| id | drink             |
|----|-------------------|
| 9  | Rum and Coke      |
| 6  | null              |
| 7  | null              |
| 3  | St Germain Spritz |
| 1  | Orange Margarita  |
| 2  | null              |

Output

[
  {"id":9,"drink":"Rum and Coke"},
  {"id":6,"drink":"Rum and Coke"},
  {"id":7,"drink":"Rum and Coke"},
  {"id":3,"drink":"St Germain Spritz"},
  {"id":1,"drink":"Orange Margarita"},
  {"id":2,"drink":"Orange Margarita"}
]

Explanation

Rows with NULL drink inherit the most recent known drink in stream order.

Example 2

Input

CoffeeShop:
| id | drink      |
|----|------------|
| 50 | Zebra Fizz |
| 10 | null       |
| 70 | Apple Soda |
| 5  | null       |
| 99 | null       |
| 1  | Mango Tea  |
| 2  | null       |

Output

[
  {"id":50,"drink":"Zebra Fizz"},
  {"id":10,"drink":"Zebra Fizz"},
  {"id":70,"drink":"Apple Soda"},
  {"id":5,"drink":"Apple Soda"},
  {"id":99,"drink":"Apple Soda"},
  {"id":1,"drink":"Mango Tea"},
  {"id":2,"drink":"Mango Tea"}
]

Explanation

Even if id values are non-monotonic, filling follows row sequence, not numeric id order.

Example 3

Input

CoffeeShop:
| id | drink        |
|----|--------------|
| 1  | Iced Latte   |
| 2  | null         |
| 3  | null         |
| 4  | null         |
| 5  | null         |

Output

[
  {"id":1,"drink":"Iced Latte"},
  {"id":2,"drink":"Iced Latte"},
  {"id":3,"drink":"Iced Latte"},
  {"id":4,"drink":"Iced Latte"},
  {"id":5,"drink":"Iced Latte"}
]

Explanation

A single known value can propagate through a long null run.

Constraints

1 <= number of rows in CoffeeShop <= 1800
1 <= id <= 2000000000
id values are unique
drink is NULL or a non-empty string
1 <= length(drink) <= 40 when drink is not NULL
The first input row always has non-NULL drink
Return exactly two columns: id, drink
Preserve original row sequence in output
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: id, drink