I have a question in regards to finding the sequence between 2 page types using sql:
Sample data we have:
| SessionID | Page Type |
|---|---|
| 123 | account |
| 123 | raw |
| 123 | plp |
| 123 | pdp |
| 123 | account |
| 123 | plp |
| 123 | sample |
| 123 | pdp |
| 123 | plp |
| 123 | pdp |
| 123 | raw |
Ideally output will look like below, only to include records when pdp is clicked right after plp
| SessionID | Page Type |
|---|---|
| 123 | plp |
| 123 | pdp |
| 123 | plp |
| 123 | pdp |
CodePudding user response：
In order to operate based on order - you must have something to order on
Usually it is timestamp column or position or something else
I assume you have one (in my sample I will use ts column as such)
Consider below approach to get what you want
select * from your_table
qualify (lag(page_type) over win = 'plp' and page_type = 'pdp')
or (lead(page_type) over win = 'pdp' and page_type = 'plp')
window win as (partition by sessionId order by ts)
with output
