CODEWITHSUNDEEP
sqlgoogle-bigquerylaglead
Mr.Mahajan
Mr.Mahajan

Reputation: 309

Fill null values in BigQuery table with next non-null value from specific column

I have a BigQuery table with the following structure:

enter image description here [ { "case_id": "1234567890", "reply_date": "2024-06-25 13:31:07.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" }, { "case_id": "1234567890", "reply_date": "2024-06-26 18:51:34.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-01 14:36:51.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-09 18:17:05.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-09 18:17:05.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-09 18:17:22.000000 UTC", "next_reply_target": "2024-07-11T18:17:21.000Z", "comment_type": "agent_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-09 21:26:25.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-09 21:26:40.000000 UTC", "next_reply_target": "2024-07-11T21:26:39.000Z", "comment_type": "agent_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-10 14:30:50.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-10 19:49:58.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-10 19:50:12.000000 UTC", "next_reply_target": "2024-07-12T19:50:12.000Z", "comment_type": "agent_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-10 21:11:52.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-10 21:12:06.000000 UTC", "next_reply_target": "2024-07-12T21:12:06.000Z", "comment_type": "agent_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-10 21:18:45.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" }, { "case_id": "1234567890", "reply_date": "2024-07-11 16:50:42.000000 UTC", "next_reply_target": null, "comment_type": "customer_comment" } ]

I need to fill the null values in the next_reply_target column with the next non-null value from that same column, partitioned by case_id and ordered by reply_date. For example, rows 1 to 5 should have the value "2024-07-11T18:17:21.000Z", row 7 should have "2024-07-11T21:26:39.000Z", and so on.

I know that in other SQL dialects, I could use the LAG or LEAD functions to access values from previous or next rows. However, BigQuery doesn't support these functions. I'm not sure how to achieve the desired result using BigQuery SQL.

Upvotes: 0

Views: 35

Answers (0)

Related Questions