Using the same SELECT SQL query in multiple unions

Question

We have some expensive query in Snowflake that we want to use in multiple union statements:

select 
    g.a as a,
    c.trial as trial,
    a1.x as value,
    g.valuename as valuename,
from 
    (select  
         date, x, 
         dense_rank() over (order by "date") as trial
     from 
         condition_table 
     order by 
         trial desc) c
join 
    acc table_1 on a1.id = c.aid
join 
    gspecs g on g.z = c."z"
             and g.valuename = 'xyz'

union

select 
    g.a as a,
    c.trial as trial,
    a1.x as value,
    g.valuename as valuename,
from 
    (select  
         date, x, 
         dense_rank() over (order by "date") as trial
     from 
         condition_table 
     order by 
         trial desc) c
join 
    table_2 a1 on a1.id = c.aid
join 
    gspecs g on g.z = c."z"
             and g.valuename = 'wsx'

Notice that

from 
    (select  date, x, 
 dense_rank() over (order by "date") as trial
 from condition_table order by trial desc)

Is used multiple times, which is not ideal. One solution is to put it into a temporary table but it does not work for us as we want a single self-contained query that can be used in a view for example. Any way to reuse the same query in several union statements without duplicating it ?

Answer 1

snowflake supports Common Table Expression (CTE) which you can use, to produce a temporary table

With CTE as (
select  date, x, 
    dense_rank() over (order by "date") as trial
 from condition_table order by trial desc)
select 
    g.a as a,
    c.trial as trial,
    a1.x as value,
    g.valuename as valuename
from 
    CTE c
join acc table_1 on a1.id = c.aid
join gspecs g on g.z = c."z"
and g.valuename = 'xyz'
union
select 
    g.a as a,
    c.trial as trial,
    a1.x as value,
    g.valuename as valuename

from 
   CTE c
join table_2 a1 on a1.id = c.aid
join gspecs g on g.z = c."z"
and g.valuename = 'wsx'