2013 update: BigQuery supports CASE:
SELECT CASE WHEN x=1 THEN 'one' WHEN x=2 THEN 'two' ELSE 'more' END
FROM (SELECT 1 AS x)
'one'
It turns out that in our case more than one when can be true which is messing with the result. The CASE-WHEN-ELSE statement is unable to deal with that.
All should work as below
Nore use ofCURRENT_TIMESTAMP()TIMESTAMPSELECT
CASE
WHEN TIMESTAMP_TO_USEC(CURRENT_TIMESTAMP()) >= TIMESTAMP_TO_USEC(TIMESTAMP('2016-03-04 00:00:00')) THEN 'afterChanges'
ELSE 'beforeChanges'
END AS beforeOrAfter1,
CASE
WHEN CURRENT_TIMESTAMP() >= TIMESTAMP('2016-03-04 00:00:00') THEN 'afterChanges'
ELSE 'beforeChanges'
END AS beforeOrAfter2,
CASE
WHEN CURRENT_DATE() >= DATE('2016-03-04 00:00:00') THEN 'afterChanges'
ELSE 'beforeChanges'
END AS beforeOrAfter3,
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;