CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Justine Mit
Justine Mit

Reputation: 287

How to UNION with multiple CTE tables?

I am currently trying to UNION using 2 different CTE tables.

Here is what my current query looks like (below):

I however get an error in Snowflake and am not sure what is wrong with the syntax of my query.

Anyone know how to fix this?

Thanks!

CREATE TABLE TABLE 1 AS (
  WITH CTE AS(
SELECT 
  USER, 
  DATE,
  FLAG 1
  FROM TABLE 2 OWN 
  INNER JOIN TABLE 3 ACT ON OWN.USER=ACT.USER
  INNER JOIN TABLE 4 SUB ON SUB.USER=OWN.USER)
  SELECT USER, 
  DATE,
  FLAG 1, 
  (CASE WHEN DATE >= CURRENT_DATE - 30 THEN 1 ELSE 0 END) AS HAS_PLAYED_30_DAYS,
  (CASE WHEN DATE < CURRENT_DATE - 30 THEN 1 ELSE 0 END) AS HAS_NOT_PLAYED_30_DAYS
  FROM CTE)
  UNION
  WITH CTE AS(
  SELECT 
    USER, 
   DATE,
  FLAG 1
      FROM TABLE 5 OWN 
  INNER JOIN TABLE 6 ACT ON OWN.USER=ACT.USER
  INNER JOIN TABLE 7 SUB ON SUB.USER=OWN.USER)
  SELECT USER, 
  DATE,
  FLAG 1, 
  (CASE WHEN DATE >= CURRENT_DATE - 30 THEN 1 ELSE 0 END) AS HAS_PLAYED_30_DAYS,
  (CASE WHEN DATE < CURRENT_DATE - 30 THEN 1 ELSE 0 END) AS HAS_NOT_PLAYED_30_DAYS
  FROM CTE);```

Upvotes: 2

Views: 4580

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Multiple CTEs are handled as:

with cte1 as (
      . . . 
     ),
     cte2 as (
      . . .
     ),
     . . .
select . . .
from cte1
union all
select . . .
from cte2
. . .

and so on. Note that union all is much preferred over union because it does not incur overhead to remove duplicates.

There are also errors such as flag 1 and table 1. I assume those are just part of transcribing the question for public consumption.

Upvotes: 4

Related Questions