maximum hitNumber per visitID - google bigquery

Question

I need to collect only the last hitnumber per visitID in a query from a Google database Analytics via google big query. Below follows my current code. I have reached the point of ordering the query where the maximum hitnumber appears first by visitId.

SELECT 
    visitNumber,
    visitId,
    date, 
    hitNumber,
    device.operatingSystem,
    device.operatingSystemVersion,
    device.mobileDeviceBranding,
    device.screenResolution,
    appInfo.exitScreenName,
    appInfo.screenName,
    eventInfo.eventCategory,
    eventInfo.eventAction,
    eventInfo.eventLabel,
  FROM `nomeConta.idConta.ga_sessions_yyyymmdd`  
  CROSS JOIN UNNEST (hits)
  WHERE appInfo.exitScreenName = "nomeTelaErro"
    and appInfo.screenName like "%identificacaoTela%"
    and date >= 'yyyymmdd'
  order by  visitID, hitNumber DESC

Answer 1

You can use a Function window to find out the biggest user hitNumber and then filter only the hit that is equal to this hitNumber. It would be something like that:

with user_hits as (
    SELECT 
        visitNumber,
        visitId,
        date, 
        hitNumber,
        device.operatingSystem,
        device.operatingSystemVersion,
        device.mobileDeviceBranding,
        device.screenResolution,
        appInfo.exitScreenName,
        appInfo.screenName,
        eventInfo.eventCategory,
        eventInfo.eventAction,
        eventInfo.eventLabel,
        -- descobre o maior hitNumber por visitId, sem agregar a tabela
        MAX(hitNumber) OVER (PARTITION BY visitId) as maxHitNumber
    FROM `nomeConta.idConta.ga_sessions_yyyymmdd`  
    CROSS JOIN UNNEST (hits)
    WHERE appInfo.exitScreenName = "nomeTelaErro"
    and appInfo.screenName like "%identificacaoTela%"
    and date >= 'yyyymmdd'
    order by  visitID, hitNumber DESC
)
SELECT 
    *
FROM user_hits
WHERE hitNumber = maxHitNumber

You can see more about window functions in the Bigquery documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts?hl=pt-br