Pivot in SQL w/ dynamic calculation of column values

PHOTO EMBED

Sun Aug 22 2021 17:23:00 GMT+0000 (UTC)

Saved by @jmbenedetto #sql

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)
content_copyCOPY

It cannot be saved as a view in GBQ, as only SELECT clauses can.