'SQL Cumulative Count with BigQuery for active

I would like to know how to the rows date falls into once truncated into week. Current data is

Ad Name Launch Date Last Seen
Ad 1 9 Dec, 2021 16 Dec, 2021
Ad 2 1 Dec, 2021 9 Dec, 2021
Ad 3 1 Dec, 2021 31 Dec, 2021

Desired output would be, imaginging that December has 4 weeks. The dates provided are examples only, they would be automatically generated by the SQL function date_trunc

Week Active Ads
Dec Date 1-7 2
Dec Date 7-14 3
Dec Date 14-21 2
Dec Date 21-30 1
sqldategroup-bygoogle-bigquerybucket


Solution 1:[1]

Consider below approach

select 
  date_trunc(seen_day, week) week_start,
  last_day(seen_day, week) week_end,
  count(distinct ad_name) Active_Ads
from your_table, 
unnest(generate_date_array(
  parse_date('%d %B, %Y', launch_date), 
  parse_date('%d %B, %Y', last_seen)
)) seen_day
group by 1, 2

if applied to sample data in your question - output is

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mikhail Berlyant

Related Questions