Case when in nested tables BigQuery

user_11879339 0
Viewed 126

I need to do the case when on the nested table. The table is looked like this:

enter image description here

I want to take each customerId and the counts of their completed referral. The completed referral rules is, when isActive Yes, balanceFullfilment Yes, and disbursmentStatus Yes.

The output may look like this:

+------------------------------+-------------------------+
|   CustomerId                 |   completedReferrals    |
+------------------------------+-------------------------+
|  5ee3069303e3ba522d200ecf    |           0             |    
|  0330633746                  |           1             |
|  5ecf73d14dc2ad166325a447    |           0             |
|  5f0ece768b16a2aa59ab31f3    |           0             |    
+------------------------------+-------------------------+

I think this is similliar to

SELECT SUM(CASE WHEN isActive = 'Yes' and balanceFullfillment = 'Yes' and disbursementStatus = 'Yes' then 1 else 0)

But I don't know how to perform it on nested table, can someone help?

google-bigquery sql
2 Answers

I would strongly recommend doing the unnest() in a subquery like this:

SELECT t.customerId,
       (SELECT COUNT(*)
        FROM UNNEST(t.referee) r
        WHERE r.isActive = 'Yes' and 
              r.balanceFullfillment = 'Yes' and 
              r.disbursementStatus = 'Yes' 
      ) as completedReferrals
FROM t;

Avoiding the outer aggregation should make the query much faster (by avoiding the data movement needed for the outer aggregation) and use fewer resources. In addition, it works even when CustomerId is repeated on different rows.

user_11440350

I am not sure what you meant by nested table but maybe if you can share the correct example or the real case. Otherwise you can try the code below. Not sure if that helps.

Here you are selecting id and per id you are counting only if the conditions you added above are met

select 
    CustomerId,
    SUM(CASE WHEN isActive = 'Yes' and balanceFullfillment = 'Yes' and disbursementStatus = 'Yes' then 1 else 0) as completedReferrals
    
    from table x
    group by 1
user_125136930
Related