Skip to main content

SQL tips & tricks

Using named windows (WINDOW clause)

This section describes handy SQL features and functions in BigQuery. This article covers named windows (aliases) for window functions. The last part of the article shows it on GA4 data and is only available with premium subscription.

Using window alias (named windows) in SQL.
Using window alias (named windows) in SQL.
Editor's note: this post was originally written by Tamas Ujhelyi—follow him for handy SQL tips on Snowflake and BigQuery. I've worked with window functions for more years than I'd like to publicly state, but I have to admit I did not know about this one.

Basic SQL tutorials won't show you this window function trick.

It's a shame, because you can learn it in what... 3 mins? And it saves you tons of lengthy repetition. AND makes your query more readable.

I'm talking about the window alias:

  1. write WINDOW after FROM (or WHERE if you have one).
  2. 2) give it an alias, like "lag_window".
  3. 3) define the window you want to reference with that alias.
  4. 4) reference the alias in your query instead of repeating the same window definition over and over.

Now you can go brag about this cool trick you just learned. (Or keep it to yourself and wait for your colleagues to notice your cleaner queries: "Oh, that? that's a window alias. I use it a LOT. how do I know about it? idk, it just comes naturally.")

Anyway. This works in BigQuery, PostgreSQL, and T-SQL, but double-check if it's available in your SQL dialect too. Have an awesome day! ✌️

Editor's extension: let's look at this on a GA4 example to provide a last click attribution type logic. This method can be used when we need to fill the source, medium, campaign columns based on the last available value within the session (e.g. the preceding pageview).
💡
This trick is particularly useful due to a "bug" introduced in many properties since the summer of 2023, which resulted in most events losing their collected_traffic_source values (i.e. they become NULL). In some properties, only pageviews and session_start events have this populated (and other events that were sent in the same batch as these), and therefore for all other events it needs to be (re-)populated in the below manner.