Matching sets of records where a shared ID meets multiple conditions?

I’m working with a BigQuery dataset and am trying to essentially match a series of page hits based on users having hit 4 specific pages — not any of the 4, but ALL 4.

Anyway, I have no troubles querying using OR statements for the page path, but I have no idea how to say “only give me results where the userID has records for ALL 4 pages, not just some of them”

Base query (anonymized):

SELECT "RECORD_DATE", CONCAT("FULL_VISITOR_ID",'.',"VISIT_ID") as CLIENTID, "HITS__TIME", "HITS__PAGE__PAGE_PATH"

FROM "SOURCE TABLE"

WHERE

("HITS__PAGE__PAGE_PATH" ILIKE '%PAGEVAL1%'

OR "HITS__PAGE__PAGE_PATH" ILIKE '%PAGEVAL2%'

OR "HITS__PAGE__PAGE_PATH" ILIKE '%PAGEVAL3%'

OR "HITS__PAGE__PAGE_PATH" ILIKE '%PAGEVAL4%')

AND "RECORD_DATE" >= '2021-01-01'

AND "RECORD_DATE" < '2021-02-01'

submitted by /u/mightyarrow
[link] [comments]


Go to Source of this post
Author Of this post: /u/mightyarrow
Title Of post: Matching sets of records where a shared ID meets multiple conditions?
Author Link: {authorlink}