How to check the value of any row in a group after a previous one fulfils a condition?

user_14406972 0
Viewed 34

I have a dataset grouped by test subjects that is filled according to the actions they perform. I need to find which customer does A and then, at some point, does B; but it doesn't necessarily have to be in the next action/row. And it can't be first does B and then A, it has to be specifically in that order. For example, I have this table:

Subject   ActionID  ActionOrder
1         A         1
1         C         2
1         D         3
1         B         4
1         C         5
2         D         1
2         A         2
2         C         3
2         B         4
3         B         1
3         D         2
3         A         3
4         A         1

Here subjects 1 and 2 are the ones that fulfil the order of actions condition. While 3 does not because it performs the actions in reverse order. And 4 only does action A

How can I get only subjects 1 and 2 as results? Thank you very much

google-bigquery sql
2 Answers

Use conditional aggregation:

SELECT Subject
FROM tablename
WHERE ActionID IN ('A', 'B')
GROUP BY Subject
HAVING MAX(CASE WHEN ActionID = 'A' THEN ActionOrder END) <
       MIN(CASE WHEN ActionID = 'B' THEN ActionOrder END)

See the demo.

user_104988280

Consider below option

select Subject 
from (
  select Subject, 
    regexp_replace(string_agg(ActionID, '' order by ActionOrder), r'[^AB]', '') check
  from `project.dataset.table`
  group by Subject
)
where not starts_with(check, 'B')
and check like '%AB%'

Above assumes that Subject can potentially do same actions multiple times that's why few extra checks in where clause. Other wise it would be just check = 'AB'

user_52219440
Related