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

user_11117255 0
Viewed 5030

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

snowflake-cloud-data-platform sql
2 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;
user_11440350
user_121789800
Related