Search for a command to run...
Series
A focused guide to window functions in BigQuery — cumulative sums, rolling averages, ranking functions, named windows, RANGE frames, and more.
title: "Combining STRUCTs with Window Functions in BigQuery" seoTitle: "Use STRUCTs with LEAD/LAG in BigQuery Window Functions" seoDescription: "Reduce repeated LEAD and LAG window function calls in BigQuery by wrapping multiple attributes into a STR...
So, if you ever find yourself working with multiple window functions in BigQuery, leverage the named windows specification for tidier, leaner code. Say you have a function like: DENSE_RANK() OVER (PARTITION BY country ORDER BY hire_date). You can ext...
One of the most common sightings in SQL code is using ranking functions. It's simple but we must surely get it right. I use ROW_NUMBER very often for de-duplication, also used DENSE_RANK a couple of times - but I've never used RANK. So how are they d...
What function do you use to explicitly de-duplicate in BigQuery?I normally use ROW_NUMBER(), but I've recently encountered a really interesting blog post suggesting ARRAY_AGG might be more performant for the task. The explanation given is that the OR...
title: "Filling up missing values with LAST_VALUE" seoTitle: "Fill Missing Values in BigQuery with LAST_VALUE IGNORE NULLS" seoDescription: "Learn how to forward-fill missing sensor readings in BigQuery using LAST_VALUE with IGNORE NULLS and an unbou...
How do you compute a cumulative SUM in BigQuery? Today we're going to look at how to compute a cumulative sum in BigQuery, a scenario that pops up now and then and is quite easy to solve using window functions. In the below example, we have a dataset...
