How to run sql queries with multiple with clauses(sub-query refactoring)?

Question

I have a code block that has 7/8 with clauses( sub-query refactoring) in queries. I'm looking on how to run this query as I'm getting 'sql compilation errors' when running these!, While I'm trying to run them I'm getting errors in snowflake. for eg:

with valid_Cars_Stock as (
select car_id
from vw_standard.agile_car_issue_dime
where car_stock_expiration_ts is null
and car_type_name in ('hatchback')
and car_id = 1102423975
)

, car_sale_hist as (
select vw.issue_id, vw.delivery_effective_ts, bm.car_id,
lag(bm.sprint_id) over (partition by vw.issue_id order by vw.delivery_effective_ts) as previous_stock_id
from valid_Cars_Stock  i
join vw_standard.agile_car_fact vw on vw.car_id = bm.car_id
left join vw_standard.agile_board_stock_bridge b on b.board_stock_bridge_dim_key = vw.issue_board_sprint_bridge_dim_key
order by vw.car_stock_expiration_ts desc
)
,

So how to run this 2 queries separately or together! I'm new to sql aswell any help would be ideal

Answer 1

So lets just reformate that code as it stands:

with valid_Cars_Stock as (
    select 
        car_id
    from vw_standard.agile_car_issue_dime
    where car_stock_expiration_ts is null
        and car_type_name in ('hatchback')
        and car_id = 1102423975
), car_sale_hist as (
    select 
        vw.issue_id, 
        vw.delivery_effective_ts, 
        bm.car_id,
        lag(bm.sprint_id) over (partition by vw.issue_id order by vw.delivery_effective_ts) as previous_stock_id
    from valid_Cars_Stock  i
    join vw_standard.agile_car_fact vw 
        on vw.car_id = bm.car_id
    left join vw_standard.agile_board_stock_bridge b 
        on b.board_stock_bridge_dim_key = vw.issue_board_sprint_bridge_dim_key
    order by vw.car_stock_expiration_ts desc
),

There are clearly part of a larger block of code.

For an aside of CTE, you should 100% ignore anything anyone (including me) says about them. They are 2 things, a statical sugar, and they allow avoidance of repetition, thus the Common Table Expression name. Anyways, they CAN perform better than temp tables, AND they CAN perform worse than just repeating the say SQL many times in the same block. There is no one rule. Testing is the only way to find for you SQL what is "fastest" and it can and does change as updates/releases are made. So ignoring performance comments.

if I am trying to run a chain like this to debug it I alter the point I would like to stop normally like so:

with valid_Cars_Stock as (
    select 
        car_id
    from vw_standard.agile_car_issue_dime
    where car_stock_expiration_ts is null
        and car_type_name in ('hatchback')
        and car_id = 1102423975
)--, car_sale_hist as (
    select 
        vw.issue_id, 
        vw.delivery_effective_ts, 
        bm.car_id,
        lag(bm.sprint_id) over (partition by vw.issue_id order by vw.delivery_effective_ts) as previous_stock_id
    from valid_Cars_Stock  i
    join vw_standard.agile_car_fact vw 
        on vw.car_id = bm.car_id
    left join vw_standard.agile_board_stock_bridge b 
        on b.board_stock_bridge_dim_key = vw.issue_board_sprint_bridge_dim_key
    order by vw.car_stock_expiration_ts desc
;), NEXT_AWESOME_CTE_THAT_TOTALLY_MAKES_SENSE (
-- .....

and now the result of car_sale_hist will be returned. because we "completed" the CTE chain by not "starting another" and the ; stopped the this is all part of my SQL block.

Then once you have that steps working nicely, remove the semi-colon and end of line comments, and get of with value real value.