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