I have a table of the form
| user_id | event | count |
|---|---|---|
| 1 | ev1 | 3 |
| 1 | ev3 | 6 |
| 2 | ev1 | 2 |
| 2 | ev2 | 1 |
| 2 | ev3 | 10 |
| 3 | ev4 | 4 |
and I’m trying to see if there’s a way to apply complex AND and OR filters in a single shot query to count how many user_id satisfy a criteria.
An example of a compound filter here would be (only with OR and ANDs):
AND(OR(ev1 > 1, ev2 > 0), (ev3 > 5))
Which means I want to count how many users have had those event counts and running this condition should yield 2 users: user_id=1 & user_id=2
I tried writing this as a WHERE clause the following way:
SELECT count(user_id) from table
WHERE (
(
(event = 'ev1' AND count > 1)
OR
(event = 'ev2' AND count > 0)
)
AND
(
(event = 'ev3' AND count > 5)
)
)
I can’t figure out how to correctly write the AND parts because it returns 0 rows any time you include an AND check however it works correct with just the OR checks
2