Define three average values based on different where conditions:

1
2
3
4
5
WITH since_lspay(difference_in_days) as (select difference_in_days from query_6437 where  account_create_time > '2021-07-01'), 
     since_solpay(difference_in_days) as (select difference_in_days from query_6437 where account_create_time > '2022-05-01'), 
     since_sauron(difference_in_days) as (select difference_in_days from query_6437 where account_create_time > '2022-12-23')
select avg(since_lspay.difference_in_days) as setup_days_since_lspay, avg(since_solpay.difference_in_days) as setup_days_since_lspay_self_serve, avg(since_sauron.difference_in_days) as setup_days_since_lspay_visibility 
from since_lspay, since_solpay, since_sauron

a WITH clause defines a temporary data set whose output is available to be referenced in subsequent queries.

Recursive CTE(Common Table Expression)

Recursive CTEs are one of the more advanced functionalities of the WITH clause, which allows referencing itself within that CTE.

The recursive function of the WITH clause similarly incorporates a base case and the recursive step.