Here is the table:
I want to count the missing values across each row for t1, t2, t3,..., and create another column in the same table with the results as shown in the picture.
I can easily do this in something like python pandas. But on SQL (specifically BigQuery and/or postgreSQL, MySQL), I can't seem to figure out the syntax. Here is my attempt:
select array_agg(id), array_agg(date), count(t) from ( select id, date, t1 as t from testdata union all select id, date, t2 as t from testdata union all select id, date, t3 as t from testdata ) as data group by id, date, t
Any ideas where I am going wrong? I think union all unpivots the wide to long table, but how do I count the missing values within a specific range and join all the results back to the original table?