'SQL | Get subsequent action of each user and aggregate into a row
I am currently working with a system with a search engine, and I'm trying to find out if the search result is sorted well so that the users don't have to scroll down to see the result they want.
We have to work with logs that writes down the action of the users such as:
- Search / Search Text
- Results they clicked
and the schema is as following:
user_id | time | action_type | details
--------+---------------------+-------------+--------------
jack | 2022-02-01 15:51:33 | search | query="text1"
sally | 2022-02-01 15:52:00 | search | query="text2"
sally | 2022-02-01 15:52:10 | search | query="text3"
jack | 2022-02-01 15:52:20 | click | target="system1"
sally | 2022-02-01 15:52:30 | click | target="system2"
mike | 2022-02-01 15:53:22 | search | query="text4"
...
What I want to do is to find out "what did they search?" and "what did they click afterwards?", so I want to make a table like below from the table above.
user_id | search_query | click_target
--------+--------------+--------------
jack | text1 | system1
sally | text2 | (null)
sally | text3 | system2
mike | text4 | (null)
How can I accomplish this? My guess is that I have to split the table into 'search' part and 'click' part, then match the search row with the closest click row but before the next search row. However I cannot come up with an query to take account of user_id.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|