Fill in values with the most recent non null with Snowflake SQL

This is my table with values

id category A Apple A NULL A Apple B NULL B Pear B Pear B Peach B NULL B NULL C NULL C NULL C Apple 

This is what I want my table to be

id category A Apple A Apple A Apple B NULL B Pear B Pear B Peach B Peach B Peach C NULL C NULL C Apple 

These are the rules that I want to apply;

• If category is null, then fill it in with the most recent category (for that id)
• If there is no value for category above for that id, then remain null

We can imagine that there's a third column called date and that's what the data is sorted on

I tried to use first_value() but I just got nulls for the category column

I'm using SQL on a Snowflake instance

4 Answers

You seem to want lag(. . . ignore nulls). Just one thing: SQL tables represent unordered sets (technically multisets). You need a column to specify the ordering.

So:

select t.*, coalesce(lag(category ignore nulls) over (partition by id order by <ordering col>) as imputed_category from t; 

Actually, it turns out that last_value() does this without needing the coalesce():

select t.*, last_value(category ignore nulls) over (partition by id order by <ordering col>) as imputed_category from t; 

For those finding this years later, not sure how the non-reproducible example was selected.

Here is functioning Snowflake SQL code that shows the problem of other answers at scale (specifically, back to back nulls!!)

The question specifically states:

If category is null, then fill it in with the most recent category (for that id)

last_value() and first_value() are not the same as (unfortunately non-existent) prev_value().

SELECT column1 as token, column2 as hr, column3 as price, lag(column3) ignore nulls over (partition by column1 order by column2) as lag_price, -- you want to coalesce itself and null removed lags!! coalesce(column3, lag(column3) IGNORE NULLS over (partition by column1 order by column2)) as correct_price, -- commented out because it does not work, not enough arguments to coalesce!! -- coalesce(lag(category ignore nulls) over (partition by id order by <ordering col>) as imputed_category, -- coalesce(lag(column3 ignore nulls) over (partition by column1 order by column2) as imputed_price, LAST_VALUE(column3 IGNORE NULLS) OVER (PARTITION BY column1 ORDER BY column2) AS last_price FROM VALUES ('usd', 1, 10), ('usd',2, NULL), ('usd',3, NULL), ('usd',4, NULL), ('usd',5, 20), ('eth', 1, 20), ('eth', 2, 21), ('eth', 3, NULL), ('eth', 4, 10), ('eth', 5, NULL), ('eth', 6, NULL); 

Notice that CORRECT_PRICE is what meets the definition of question, whereas last_value() only looks correct incidentally because this data was limited.

Image included:

Syntax is:

FIRST_VALUE( ) [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY ] ORDER BY [ { ASC | DESC } ] [ <window_frame> ] )

Default is IGNORE NULLS, but if you specify IGNORE NULLS you won't get them.

Here is the documentation link, if you need it:

with t as ( select ord,id,category from (values (0,'A','Apple'), (1,'A',NULL), (2,'A','Apple'), (3,'A','Orange'), (4,'A',NULL), (5,'A','Apple'), (1,'B',NULL), (2,'B','Pear'), (3,'B','Pear'), (4,'B','Peach'), (5,'B',NULL), (6,'B',NULL), (7,'B','Orange'), (8,'B',NULL), (1,'C',NULL), (2,'C',NULL), (3,'C','Apple'), (4,'C',NULL), (5,'C','Pear'), (6,'C',NULL) ) as t (ord,id,category) ) select * ,lag(category) ignore nulls over(partition by ID order by ord) as lagged ,coalesce(category,lagged) ,last_value(category ignore nulls) over(partition by ID order by ord) as lv from t 

Gordons first answer is the correct one, you need lag with coalesce. last_value will return the last non null value of the id partition. In this more robust example you can see how each works.