SQL distinct rolling count


Wed Jul 28 2021 07:46:52 GMT+0000 (UTC)

Saved by @nyzl #sql #athena

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 

INTERVAL x DAY is n-1 for 7 days x = 6