DECLARE Counter INT64;
BEGIN TRANSACTION;
SET Counter = 0;
CREATE TEMP TABLE tmp
(
dte date,
days INT64,
users INT64
);
WHILE Counter < 28 DO
INSERT INTO tmp
with dte as (
SELECT dte
FROM unnest(generate_date_array(date('2021-02-01'), date('2021-12-30'))) dte
),
ids as (
SELECT clientId,
PARSE_DATE("%Y%m%d", date) as dte
FROM `govuk-bigquery-analytics.87773428.ga_sessions_2021*`
WHERE NOT device.operatingSystem = "iOS"
)
SELECT dte.dte as dte, max(Counter) as days, count( distinct ids.clientId) AS users
FROM dte, ids
WHERE ids.dte BETWEEN DATE_SUB(dte.dte, INTERVAL Counter DAY) and dte.dte
group by 1
order by 1 asc;
SET Counter = Counter + 1;
END WHILE;
COMMIT TRANSACTION;
--select * from tmp
SELECT * FROM
(SELECT * FROM tmp)
PIVOT(SUM(users) FOR days IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27))
order by 1 asc