CODEWITHSUNDEEP
google-bigqueryconditional-statementscase
Iren Ramadhan
Iren Ramadhan

Reputation: 339

CASE WHEN case in BigQuery comparing value in the same field

I need your help in BigQuery. I'm trying to do CASE WHEN using several conditional within the same field. Here's the data:

CASE WHEN within the same field

In this case I wanna have one aggregated output that says TRUE when 'Resurrected' movement_type happens AFTER 'Churned' movement_type based on its month_key (regardless of the gap in between them).

So, from that data I want the output to be:

+----------+-----------+
| group_id | condition |
+----------+-----------+
| A12345   | TRUE      |
+----------+-----------+

I've tried using LEAD but had no luck finding the right answer. Thank you.

Upvotes: 0

Views: 402

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Consider below approach

select group_id, logical_or(flag) as condition 
from (
  select group_id, 
    countif(movement_type = 'Churned') over prev_rows > 0
    and movement_type = 'Resurrected' as flag
  from `project.dataset.table` 
  window prev_rows as (partition by group_id order by month_key)
)
group by group_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

wanna have one aggregated output that says TRUE when 'Resurrected' movement_type happens AFTER 'Churned' movement_type based on its month_key

Just use aggregation:

select group_id,
       (max(case when movement_type = 'Resurrected' then month_key end) >
        max(case when movement_type = 'Churned' then month_key end)
       ) as flag
from t
group by group_id;

Upvotes: 1

Related Questions