How do I find all distinct values for each record (identified by ID) in BigQuery?

Question

Data

This is an import from another place, so my data in BigQuery looks something like this:

order_id | owner_pk | owner_sk | other_properties | currency

Notes

• PK+SK are the index in the original dataset (but not here in BQ)
• Order ID is unique for PK+SK in the original dataset
• Together, OrderID+PK+SK are unique for each record (as per the original dataset)

Problem

I'd like to find if there are orders (order_id) coming from the same owner (PK+SK) that have different (distinct?) currency values. I don't need the actual orders, true/false is ok.

• option 1: all orders coming from the same owner have the same currency
• option 2: there is an order with a different currency within one owner

Any help with querying for that is appreciated! :)

Answer 1

You can use aggregation. I would return the currencies as well as the number:

select owner_pk, owner_sk,
       array_agg(distinct currency) as currencies,
       count(distinct currency) as num_currencies
from t
group by owner_pk, owner_sk;