newbie BigQuery, SQL how to count the number of groups that contain special rows

Question

I am a newbie

I using firebase events to track what my user are doing on my website.

As a simple example my "happy" use case is a user completes steps A, B, C. I want to count the number of user that have are Happy or not Happy. Not happy is defined as their session does not contain all three events

Here is my sample SQL with some simple data. I am able to to count the number of "C" events in the happy sessions. I can not figure out how to identify sessions that are not Happy

WITH testData AS (
  SELECT "id1" as idd, "A" as name UNION ALL
  SELECT "id1" , "B"  UNION ALL
  SELECT "id1" , "C"  UNION ALL

  SELECT "id2" , "A"  UNION ALL
  SELECT "id2" , "B"  UNION ALL

  SELECT "id3" as id, "A" as name UNION ALL
  SELECT "id3" , "B"  UNION ALL
  SELECT "id3" , "C"  UNION ALL

  SELECT "id4" , "A"  UNION ALL
  SELECT "id4" , "B"  UNION ALL

  SELECT "id5" , "A"  UNION ALL
  SELECT "id5" , "B"  UNION ALL
  SELECT "id5" , "C"  UNION ALL
  SELECT "id5" , "A"  UNION ALL
  SELECT "id5" , "B"  UNION ALL
  SELECT "id5" , "C"  
)
SELECT * 
  FROM 
    (SELECT 
        idd, COUNT(name) as PASSED 
      FROM
        testData where name = "C"
      GROUP BY
        idd)

   UNION ALL

   (SELECT 
        idd, NUMERIC '0' as PASSED 
      FROM
        testData where name != "C"
      GROUP BY
        idd)
  ORDER BY
    idd

Row idd PASSED   
1   id1 1    
2   id1 0    
3   id2 0    
4   id3 1    
5   id3 0    
6   id4 0    
7   id5 2    
8   id5 0    

I was expecting the results to be something like

Row idd PASSED   
1   id1 1    
3   id2 0    
4   id3 1    
6   id4 0    
7   id5 2    

Any suggestions would be greatly appreciated.

Also can anyone suggest a really good advanced SQL tutorial?

Andy

Answer 1

You can use aggregation. Assuming that only those three states are allowed, here is one method:

select idd,
       (CASE WHEN count(distinct name) = 3 THEN 'Happy' else 'Unhappy' end) as state_of_mind
from testData
group by idd;

If other states can be present, then:

select idd,
       (CASE WHEN count(distinct case when name in ('A', 'B', 'C') THEN name END) = 3 THEN 'Happy' else 'Unhappy' end) as state_of_mind
from testData
group by idd

Answer 2

Based on the code in your question - you just rely on step C (which makes sense if the only way to get to step C is by completing step A and then step B)
So, I followed the idea in your original query and just fixed it

#standardSQL
SELECT idd, COUNTIF(name = "C") Passed
FROM testData 
GROUP BY idd
-- ORDER BY idd

If to apply to dummy data in your question - result will be as expected

Row     idd     Passed   
1       id1     1    
2       id2     0    
3       id3     1    
4       id4     0    
5       id5     2    

Answer 3

Also can anyone suggest a really good advanced SQL tutorial?

These helped me :-)
Tutorial:
http://www.sql-tutorial.ru/
Exercises (with sample databases and checker):
http://www.sql-ex.ru/

Answer 4

Thanks

The trick as suggested above by Gordon Linoff was to use 'CASE' I have never seen that before

Here is my final working solution

WITH testData AS (
-- happy 
  SELECT "id1" as idd, "A" as name UNION ALL
  SELECT "id1" , "B"  UNION ALL
  SELECT "id1" , "C"  UNION ALL

-- not happy
  SELECT "id2" , "A"  UNION ALL
  SELECT "id2" , "B"  UNION ALL

-- happy
  SELECT "id3" , "A"  UNION ALL
  SELECT "id3" , "B"  UNION ALL
  SELECT "id3" , "C"  UNION ALL

-- not happy  
  SELECT "id4" , "A"  UNION ALL
  SELECT "id4" , "B"  UNION ALL

-- happy
  SELECT "id5" , "A"  UNION ALL
  SELECT "id5" , "B"  UNION ALL
  SELECT "id5" , "C"  UNION ALL
  SELECT "id5" , "A"  UNION ALL
  SELECT "id5" , "B"  UNION ALL
  SELECT "id5" , "C"  
  )
  ,

  isHappyTable AS (
    SELECT
    idd,
    CASE
      WHEN name in ('C') 
        THEN NUMERIC '1'
      ELSE
        NUMERIC '0'
    END as isHappy
    FROM 
      testData

   ORDER BY
        idd
        )

SELECT 
    idd, 
    SUM(isHappy) AS isHappy
  FROM
    isHappyTable
  GROUP BY
    idd
  ORDER BY
    idd

    Row idd isHappy  
1   id1 1    
2   id2 0    
3   id3 1    
4   id4 0    
5   id5 2