with account_session_ste as ( SELECT * FROM `table` where exists(select 1 from unnest(hits) h where regexp_contains(h.page.hostname, r'signin.account.gov.uk')) and _table_suffix = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 4 DAY)) ) -- group by 1,2) select fullVisitorId AS user_id, hits.page.hostname as hostname, hits.page.pagePath as pagePath, hits.hitNumber as hit_number -- * from account_session_ste, unnest(hits) as hits
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter