Fill null values with next non-null value bigquery

Question

I have the following table with data from GA in BigQuery

userid  visitid purchase_date
GH8932  12345   2017-04-09
GH8932  12346   null
GH8932  12347   null
GH8932  12348   null
GH8932  12349   2017-05-30
GH8932  12350   null
GH8932  12351   null
GH8932  12352   2017-06-07
GH8932  12353   null
GH8932  12354   2017-06-30

And I want the resulting table to be

userid  visitid purchase_date
GH8932  12345   2017-04-09
GH8932  12346   2017-05-30
GH8932  12347   2017-05-30
GH8932  12348   2017-05-30
GH8932  12349   2017-05-30
GH8932  12350   2017-06-07
GH8932  12351   2017-06-07
GH8932  12352   2017-06-07
GH8932  12353   2017-06-30
GH8932  12354   2017-06-30

I tried the following

select
a.userid,
a.visitid,
b.purchase_date
from x
left join
( 
select 
userid,
visitid,
purchase_data 
from x 
where purchase_date is not null) as b
on x.userid = b.userid
where x.visitid <= b.visitid

But, this doesn't provide the solution I'm looking for.

Appreciate the help for a BQ rookie

Elliott Brossard · Accepted Answer · 2017-12-01 07:17:13Z

15

Use IGNORE NULLS with FIRST_VALUE to find a non null date in the desired window:

SELECT
  userid,
  visitid,
  FIRST_VALUE(purchase_date IGNORE NULLS) OVER (
    PARTITION BY userid ORDER BY visitid
    ROWS BETWEEN CURRENT ROW AND
    UNBOUNDED FOLLOWING) AS purchase_date
FROM x;

answered Dec 1, 2017 at 7:17

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vishank Over a year ago

Thanks @Elliot Brossard, I just realized that there are a few more 'nulls' after the final visit, which needs to be overwritten with the final 'purchase_date'. Any way we can achieve this?

Elliott Brossard Over a year ago

Can you maybe post a separate question? That way you'll get input on a solution from other people as well.

vishank Over a year ago

Sure, I'll do that. Thanks

Collectives™ on Stack Overflow

Fill null values with next non-null value bigquery

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related