SQL most recent using row_number() over partition
You should be able to move your query to a subquery and add where
criteria:
SELECT user_id, page_name, recent_click
FROM (
SELECT user_id,
page_name,
row_number() over (partition by session_id order by ts desc) as recent_click
from clicks_data
) T
WHERE recent_click = 1
You should move the row_number()
function into a subquery and then filter it in the outer query.
Something like this:
SELECT * FROM (
SELECT
[user_id]
,[page_name]
,ROW_NUMBER() OVER (PARTITION BY [session_id]
ORDER BY [ts] DESC) AS [recent_click]
FROM [clicks_data]
)x
WHERE [recent_click] = 1