Case when in nested tables BigQuery

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

I want to take each customerId and the counts of their completed referrals. 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 similar 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?

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.