Bigquery Sql how many products were only sold in this year-CodePudding

I have a question regarding to Bigquery sql. I am trying to find how many products were only sold in this year e.g.

Year	Product
2022	A
2021	B
2021	C
2022	B

Therefore, there will be only one product that has been sold this year.

Is there a way to solve it without using NOT IN orjoin Thank you!

CodePudding user response：

You could use NOT EXISTS with a correlated subquery as the following:

Select Product 
From table_name T
Where Year=2022 And
Not Exists (
  Select 1 From table_name D 
  Where D.Product = T.Product And
        D.Year <> T.Year
  )

Another option, you may use aggregation as the following:

Select Product 
From table_name
Group By Product
Having Count(*) = 1 And Max(Year) = 2022