Hi can anyone help me for this. What im trying to do is to rank my session id but it seems not to work as it returns me just “1”. The third row should be 2, the row 5 should have 2 for rank and the row 6 should have 3 for rank and so on. I try to enumerate the session. Thank u so much
x
WITH
hits AS (
SELECT
TIMESTAMP_SECONDS(visitstarttime) AS timestamp,
fullVisitorId,
CONCAT(fullvisitorId, CAST(visitStartTime AS STRING)) AS sessionId,
hits.page.pagePath,
hits.isEntrance,
hits.isInteraction,
hits.type,
hits.hitNumber,
MIN(hits.hitNumber) OVER (PARTITION BY fullVisitorId, visitStartTime) AS first_hit,
FROM `dl-training-bigquery.ND_177736451.ga_sessions_20191202` AS ga_table,
UNNEST(hits) AS hits
WHERE hits.type = 'PAGE'
)
SELECT
fullVisitorId,
sessionId,
pagePath,
timestamp,
MIN(timestamp) AS visit_time,
RANK() OVER(PARTITION BY CONCAT(fullvisitorId, "-", CAST(timestamp AS STRING)) ORDER BY sessionId ASC) AS Rank
FROM hits
WHERE type = 'PAGE'
GROUP BY 1,2,3,4
ORDER BY sessionId
Advertisement
Answer
Presumably, you want:
RANK() OVER(
PARTITION BY fullvisitorId, sessionId
ORDER BY timestamp
) AS Rank
This ranks hits within groups having the same visitor and session by ascending timestamp – which is how I understand your question.