Bigquery SQL - Count of array values that appear twice-CodePudding

I have an array of ID values and I wish to count the number of values that appear exactly twice in each array. Below is an example of the data and desired outcome:

I have the following code that can count the total (or could count the distinct total) of values but I can't figure out how I need to modify it so that it only counts the ID's that appear twice.

SELECT mth_end_date, 
  (SELECT COUNT(id) FROM UNNEST(SPLIT(user)) AS id) segment_count
FROM temp2

I know I am missing something really obvious here, but I just can't crack it.

Appreciate your suggestions!

CodePudding user response：

Consider below approach

if applied to sample data in your question - output is