How the WITH Clause Can Simplify Your SQL Queries

2021-01-15 · 2 min read  ·
Share on:

This is a quick post to highlight how you can simplify your SQL using the WITH clause. I've used this technique a lot lately to prepare queries for clients that are easy to understand and work with (the queries, not necessarily the clients).

Contents

Benefits of using the WITH Clause

The WITH clause allows you to reduce joins and logically sequence your code. It does this by creating temporary tables (technically they are views) that are usable by your main SQL statement so that you can break your code into smaller and easier to understand snippets.

Syntax of the WITH Clause

NOTE: As always I am working with Google Analytics 4 data in BigQuery for this post, but the following will be applicable to many other database systems.

The WITH clause is a prefix for your main query. You can use it to create one or more SELECT statements, give each a name, and then reference them in your main query. As an example, here is how you might pull the unique ID's of users who visited your site yesterday and then came back again today:

 1# Step 1: Get a list of users who visited the site yesterday
 2WITH yesterday AS (
 3    SELECT
 4        user_pseudo_id AS yesterday_user_pseudo_id
 5    FROM `<project name>.<dataset name>.events_08012021`
 6)
 7
 8# Step 2: Get a list of users who also visited the site today
 9SELECT
10    user_pseudo_id
11FROM `<project name>.<dataset name>.events_09012021`,yesterday
12WHERE
13    yesterday.yesterday_user_pseudo_id = user_pseudo_id

In the example above I'm defining a temporary table with the name "yesterday". You could also put a comma at the end to add other temporary tables if you choose, like this:

1WITH yesterday AS (
2    SELECT
3        user_pseudo_id AS yesterday_user_pseudo_id
4    FROM `<project name>.<dataset name>.events_08012021`
5), last_week AS (
6    SELECT
7        user_pseudo_id AS yesterday_user_pseudo_id
8    FROM `<project name>.<dataset name>.events_02012021`
9)

Performance

Typically the WITH clause is not executed until it is referenced, and the results are cached in case the reference is used multiple times within the query. However, I have not confirmed whether or not BigQuery operates this way (I'll update this post when I do).

Other Resources for BigQuery and GA4

Here are a few other posts that I’ve created for using BigQuery with data from Google Analytics 4: