with dte as (
SELECT dte
FROM unnest(generate_date_array(date('2021-03-11'), date('2021-07-31'))) dte
),
ids as (
SELECT clientId,
PARSE_DATE("%Y%m%d", date) as dte
FROM `*table*`
)
SELECT dte.dte, count( distinct ids.clientId)
FROM dte, ids
WHERE ids.dte BETWEEN DATE_SUB(dte.dte, INTERVAL 7 DAY) and dte.dte
group by 1
order by 1 asc