CODEWITHSUNDEEP
sqlgoogle-bigquery
Wolf
Wolf

Reputation: 576

BigQuery Group By Case When

I am trying to identify when a user has completed event "a1", at any point in time.

So far I have this

WITH data as (
  SELECT 'franz' user, 'a1' event UNION ALL
  SELECT 'franz', 'a2' UNION ALL
  SELECT 'bob', 'a2' UNION ALL
  SELECT 'bob', 'a3' UNION ALL
  SELECT 'hans', 'a1' UNION ALL
  SELECT 'hans', 'a3'
  )
  
SELECT 
  user,
  (CASE WHEN event = "a1" THEN 1 ELSE 0 END) as exposed,
FROM data
GROUP BY event, user

enter image description here

But I am trying to to get a window function (OVER (PARTITION BY...) in this query somehow to group the users - and have all users marked as 1 in the exposed column that have ever done event a1.

Thank you.

Upvotes: 0

Views: 937

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Below is for BigQuery Standard SQL

#standardSQL
SELECT user,
  SIGN(COUNTIF(event = 'a1')) AS exposed
FROM data
GROUP BY user

If applied to sample data from your question - output is

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You obviously need a time column of some sort to answer your question -- or even for the question to make sense. So given that, you would want:

SELECT user,
       (COUNTIF(event = 'a1') OVER (PARTITION BY user ORDER BY timecol) < 0) as exposed
FROM data;

Upvotes: 2

Related Questions