Using rank to enumerate the session

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

  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

enter image description here

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.

Advertisement

Answer