Group rows where there is at least one column with true value

Question

I have a table like this

+-----+-------------------+------+-------+-------+-------+---+
| Row |       email       | year | month | flag1 | flag2 |   |
+-----+-------------------+------+-------+-------+-------+---+
|   1 | user1@example.com | 2018 |     1 | true  | true  |   |
|   2 | user1@example.com | 2018 |     1 | false | true  |   |
|   3 | user1@example.com | 2018 |     1 | true  | true  |   |
|   4 | user2@example.com | 2018 |     1 | false | false |   |
|   5 | user2@example.com | 2018 |     1 | false | false |   |
|   6 | user2@example.com | 2018 |     1 | false | false |   |
|   7 | user3@example.com | 2018 |     1 | true  | false |   |
|   8 | user3@example.com | 2018 |     1 | true  | false |   |
|   9 | user3@example.com | 2018 |     1 | false | false |   |
+-----+-------------------+------+-------+-------+-------+---+

which can be generated with this statement

#standardSQL
WITH table AS (
  SELECT "user1@example.com" as email, 2018 as year, 1 as month, TRUE AS flag1, TRUE as flag2
  UNION ALL
  SELECT "user1@example.com",2018,1,FALSE,TRUE
  UNION ALL
  SELECT "user1@example.com",2018,1,TRUE,TRUE
  UNION ALL
  SELECT "user2@example.com",2018,1,FALSE,FALSE
  UNION ALL
  SELECT "user2@example.com",2018,1,FALSE,FALSE
  UNION ALL
  SELECT "user2@example.com",2018,1,FALSE,FALSE
  UNION ALL
  SELECT "user3@example.com",2018,1,TRUE,FALSE
  UNION ALL
  SELECT "user3@example.com",2018,1,TRUE,FALSE
  UNION ALL
  SELECT "user3@example.com",2018,1,FALSE,FALSE
)

Grouping by email,year,month, the output table require to have true value (for each of the two flag columns), if in the grouped data there is at least ONE row with true value

The resulting table should be this one

+-----+-------------------+------+-------+-------+-------+---+
| Row |       email       | year | month | flag1 | flag2 |   |
+-----+-------------------+------+-------+-------+-------+---+
|   1 | user1@example.com | 2018 |     1 | true  | true  |   |
|   2 | user2@example.com | 2018 |     1 | false | false |   |
|   3 | user3@example.com | 2018 |     1 | true  | false |   |
+-----+-------------------+------+-------+-------+-------+---+

I started grouping all the flags by the first 3 column, but now I'm now stuck to determine if there is at least one true value inside each array

SELECT email,
  year,
  month,
  ARRAY_AGG(flag1) as flag1,
  ARRAY_AGG(flag2) as flag2
FROM table
GROUP BY 1,2,3

Answer 1

#standardSQL
SELECT email,
  year,
  month,
  LOGICAL_OR(flag1) AS flag1,
  LOGICAL_OR(flag2) AS flag2
FROM table
GROUP BY 1,2,3