SQL query to fill null values with previous non-null data

Data and Databases
1

I’ve got a tricky SQL problem. There’s this table with fruit sales data. It has columns for fruit type, quantity, total, and time. Some rows have null values. I need to replace these nulls with the most recent non-null data for each fruit type.

Here’s a simplified example of what I’m after:

CREATE TABLE fruit_sales (
    fruit_type VARCHAR(10),
    quantity INT,
    total INT,
    sale_time TIME
);

INSERT INTO fruit_sales VALUES
    (NULL, NULL, NULL, '08:00:00'),
    ('banana', 3, 3, '08:30:00'),
    (NULL, NULL, NULL, '09:00:00'),
    ('apple', 5, 5, '09:30:00');

I want a query that fills in the nulls like this:

SELECT
    COALESCE(fruit_type, 'unknown') AS fruit_type,
    COALESCE(quantity, 0) AS quantity,
    COALESCE(total, 0) AS total,
    sale_time
FROM
    -- Some magic SQL here
;

This should give me rows for each fruit type at every time point, using the last known values. Any ideas on how to tackle this? I’m stumped!

2

ooh, interesting problem! have you tried using window functions? maybe something like LAST_VALUE() with IGNORE NULLS could work? it might look like this:

SELECT
COALESCE(LAST_VALUE(fruit_type IGNORE NULLS) OVER (ORDER BY sale_time), ‘unknown’) AS fruit_type,
– similar for quantity and total
FROM fruit_sales

what do you think? would that solve your issue?

3

hey there! i’ve dealt with similar stuff before. have u considered using a subquery with LAG() function? something like:

SELECT
COALESCE(fruit_type, LAG(fruit_type IGNORE NULLS) OVER (ORDER BY sale_time), ‘unknown’) AS fruit_type,
– same for quantity and total
FROM fruit_sales

this might do the trick for ya. lemme know if it helps!

4

This is indeed a tricky problem, but I’ve encountered similar scenarios in my work. You’re looking for a technique called ‘last non-null value carry forward.’ Here’s an approach using window functions that should work:

WITH filled_data AS (
  SELECT 
    fruit_type,
    quantity,
    total,
    sale_time,
    LAST_VALUE(fruit_type IGNORE NULLS) OVER (ORDER BY sale_time) AS filled_fruit_type,
    LAST_VALUE(quantity IGNORE NULLS) OVER (ORDER BY sale_time) AS filled_quantity,
    LAST_VALUE(total IGNORE NULLS) OVER (ORDER BY sale_time) AS filled_total
  FROM fruit_sales
)
SELECT
  COALESCE(filled_fruit_type, 'unknown') AS fruit_type,
  COALESCE(filled_quantity, 0) AS quantity,
  COALESCE(filled_total, 0) AS total,
  sale_time
FROM filled_data
ORDER BY sale_time;

This query utilizes window functions to propagate the last non-null values forward and applies COALESCE to ensure no nulls remain in the output. It should provide the complete dataset at each time point with the appropriate substitutions.