Dealing with cases where CTE names and physical tables collide

[mk3008]

There is a technique to "intentionally" make CTE and physical table names the same. We will deal with this.

Sample

with
sale_summarys as (
    select
        store_id
        , sum(price) as price
    from
        --Physical
        sales
    group by
        store_id
),
sales as (
    select
        *
    from
        --Physical
        sales
)
select
    *
from
    --CTE
    sale_summarys
;
with
sales as (
    select
        *
    from
        --Physical
        sales
),
sale_summarys as (
    select
        store_id
        , sum(price) as price
    from
        --CTE
        sales
    group by
        store_id
)
select
    *
from
    --CTE
    sale_summarys