Identify Users with Specific Page Visit Sequence

page_visits

+------------+---------+---------+----------+ | date | user_id | page_id | ts | +------------+---------+---------+----------+ |2024-06-01 | 101 | A |08:00:01 | |2024-06-01 | 101 | B |08:05:10 | |2024-06-01 | 101 | D |08:07:00 | |2024-06-01 | 102 | A |09:00:00 | |2024-06-01 | 102 | B |09:02:00 | +------------+---------+---------+----------+

Scenario

Website analytics team wants to find users who, within one calendar day, visited page A followed by page B where the first page immediately after B is NOT page C.

Question

Given table page_visits(date, user_id, page_id, ts), write SQL to return distinct user_ids who, on the same date, have the sequence A → B with no C directly after that B (A→B and A→B→D→C qualify; A→B→C does not).

Hints

Use LEAD or ROW_NUMBER to look at the next page after each B, filter, then group.