Modules

Using the WITH clause (also known as CTEs) in SQL can make your queries more readable and structured, especially when dealing with complex subqueries. Let's dive into an example so that it makes more sense.

Use Case: E-commerce Data

The objective is to: to measure the number of orders and GMV (Gross Merchandise Value) for each month, along with the number of new users who signed up in that same month. You will be using 2 tables:

users table:

orders table:

Step 1: Measure the number of orders and GMV using the orders table

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS number_of_orders,
    SUM(order_value) AS gmv
FROM orders
GROUP BY 1;

Step 2: Get the number of monthly signups using users table

SELECT
    DATE_TRUNC('month', signup_date) AS month,
    COUNT(*) AS nb_of_signups
FROM users
GROUP BY 1;

Step 3: Link these 2 tables together

Here is where we gonna use the "WITH" statement to start your first subquery. Note that you will need to use only once the "WITH" statement once at the beginning of your query.

Then you want to give your subquery a name.

Let's start with the first query to get orders and GMV. We will call this table my_orders and the syntax looks like this:

WITH my_orders AS (

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS number_of_orders,
    SUM(order_value) AS gmv
FROM orders
GROUP BY 1)

Now we want to add our next query which is the one with the number of signup. If we want to add this to our first query, we just add another comma in between like this:

WITH my_orders AS (

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS number_of_orders,
    SUM(order_value) AS gmv
FROM orders
GROUP BY 1),

my_signups AS (

SELECT
    DATE_TRUNC('month', signup_date) AS month,
    COUNT(*) AS nb_of_signups
FROM users
GROUP BY 1)

Now we have both of these tables, we just need to call them on another query. And join these 2 tables we created on month.

WITH my_orders AS (

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS number_of_orders,
    SUM(order_value) AS gmv
FROM orders
GROUP BY 1),

my_signups AS (

SELECT
    DATE_TRUNC('month', signup_date) AS month,
    COUNT(*) AS nb_of_signups
FROM users
GROUP BY 1)

SELECT
    my_orders.month,
    my_orders.number_of_orders,
    my_orders.gmv,
    my_signups.nb_of_signups
FROM my_orders
INNER JOIN my_signups on my_orders.month = my_signups.month

And you got your results! You were able to create 2 subqueries and then join them together! This is so powerful when dealing with so many tables and that makes your SQL so readable!

Let's now practice this new concept!