Search code examples
joingoogle-bigqueryinner-join

Querying two partitions from same table in Bigquery

I am looking to do some querying on two partitions (different partition date) on BQ.

Table format is the following

crm_id, attribute#1, attribute#2,attribute#3

For the IDs that are common from week 1 and week 2, I want to see how many changed attribute#1, attribute#2 and attribute# 3

I started with the inner join using:

WITH t1 AS 
(SELECT crm_id AS w1_crm
FROM `table`
WHERE DATE(_PARTITIONTIME) = "date1" 
)
SELECT crm_id
FROM `table`
WHERE DATE(_PARTITIONTIME) = "date2" 
INNER JOIN
  t1
ON 
  w1_crm = crm_id

I am getting an error on the INNER JOIN

Solution

  • Try below 

    WITH t1 AS (
  SELECT crm_id AS w1_crm
  FROM `table`
  WHERE DATE(_PARTITIONTIME) = "date1" 
), t2 AS (
  SELECT crm_id
  FROM `table`
  WHERE DATE(_PARTITIONTIME) = "date2" 
)
SELECT crm_id
FROM t2
INNER JOIN t1
ON w1_crm = crm_id