Snippets Collections
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 
star

Wed Jul 28 2021 07:46:52 GMT+0000 (Coordinated Universal Time)

#sql #athena

Save snippets that work with our extensions

Available in the Chrome Web Store Get Firefox Add-on Get VS Code extension