Select customers who purchased BOTH this year and last

I’m fairly new to SQL and I’m using BigQuery to find customers who purchased in 2019 and 2018.

This is the query I’m using to find the customers who purchased in 2019.

SELECT DISTINCT contact_email
FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY id) AS instance
  FROM `table.orders`
) orders -- identify duplicate rows
WHERE 
    instance = 1 
    AND processed_at between '2019-01-01 00:00:00 UTC' AND '2020-01-01 00:00:00 UTC'

I’m struggling now with how to pull in distinct users who purchased this year AND last year. Can anyone point me in the correct direction? Thank you.

Answer

Hmmm. I think I might do this as an aggregation query:

select o.contact_email
from `table.orders o`
where instance = 1 and
      processed_at >= timestamp('2018-01-01') and
      processed_at < timestamp('2020-01-01')
group by o.contact_email
having count(distinct year(processed_at)) = 2;

Advertisement

Answer