DECLARE dates STRING; SET dates = ( SELECT CONCAT('("', STRING_AGG(DISTINCT REPLACE(LEFT(started_at,10),"-",""), '", "'), '")'), FROM `analytics-dev-308300.dtm_engagement.ft_content_consumption` ); EXECUTE IMMEDIATE FORMAT(""" SELECT DISTINCT * FROM (SELECT user_id, group_id, REPLACE(LEFT(started_at,10),"-","") as started_at, FROM `analytics-dev-308300.dtm_engagement.ft_content_consumption`) PIVOT (COUNT(*) as s for started_at in %s)""",dates)
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