Implementing iteration in Standard SQL (BigQuery)

Question

I have an audit table (with tablename, date and rec counts) with data as far back as 5 yrs as shown below. My requirement is to retrieve the record counts for the tablename for a specific day and it's corresponding days in the past 3 weeks. For e.g. if I run the query today (Tue, Apr 22) it should give me the counts for Apr 22, Apr 15 (Tue), Apr 8 (Tue).
I am looking for a generic solution for this.

Input (contains 5 yrs data):

tabnm | rundt      | rec_cnt
emp   | 2025/04/22 | 100
emp   | 2025/04/21 | 110
emp   | 2025/04/20 | 96
....
emp   | 2025/04/16 | 117
emp   | 2025/04/15 | 156
....
emp   | 2025/04/10 | 176
emp   | 2025/04/09 | 160
emp   | 2025/04/08 | 154
emp   | 2025/04/07 | 182
....

Expected Output:

tabnm | rundt      | rec_cnt
emp   | 2025/04/22 | 100
emp   | 2025/04/15 | 156
emp   | 2025/04/08 | 154

A crude way could be like -

select tabnm, rundt, rec_cnt 
from audit_tbl
where rundt = CURRENT_DATE()
and tabname = 'emp'
union all
select tabnm, rundt, rec_cnt 
from audit_tbl
where rundt = DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
and tabname = 'emp'
union all
select tabnm, rundt, rec_cnt 
from audit_tbl
where rundt = DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
and tabname = 'emp';

However, I don't want to hardcode all the 3 UNION ALLs blocks (one for each of the past 3 weeks), since we might need to change the number of required weeks from 3 to let's say 5 someday. So I was looking for a generic solution with SQL query (with struct/array/recursion) in any way to avoid the repetition as evident above ?

A flexible solution could be achieved with an SP something like (pseudocode)-

DECLARE itr INT64; -- this will be the no. of weeks required to look back at
DECLARE i INT64;
SET itr = in_param; -- passed in via an input parameter
SET i = 0;
WHILE i < itr 
DO
select tabname, rundt, rec_cnt from audit_tbl where rundt = DATE_SUB(CURRENT_DATE(), INTERVAL (i * 7) DAY);
SET i = i + 1;
END WHILE;

But, is there a smarter way to achieve the above requirement using a SQL query without hardcoding the UNION ALL blocks?

Thanks.

Answer 1

One option could be using recursive cte to generate rows (3 or 5 or whatever) and join to table on some date math = rundt

WITH
  RECURSIVE days (pass) as
  ( Select 1
  UNION ALL
    Select pass + 1
    From days
    Where pass < 3
  )
SELECT  t.*
FROM    tbl t
Inner Join days ON(t.rundt = Current_Date - (pass-1)*7)

tabnm	rundt	rec_cnt
emp	2025-04-23	100
emp	2025-04-16	156
emp	2025-04-09	154

SELECT 3

fiddle

Answer 2

You can just use IN

select
  tabnm,
  rundt,
  rec_cnt 
from audit_tbl
where rundt IN (
  CURRENT_DATE(),
  DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY),
  DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
)
  and tabname = 'emp'

Answer 3

The approach by Charlieface is an interesting one. However, it does not address the fact that the number of time intervals (3 in that case) are hardcoded.

Using a stored procedure with a parameter, such as @nTimeInterval = 3 (and a default value of 1) could work.. This code could be modified as such:

select
  tabnm,
  rundt,
  rec_cnt 
from audit_tbl
where rundt IN (
    EXEC Procedure @nTimeInterval = 3; 
)
  and tabname = 'emp'

Answer 4

Why not simply compare the day of the week:

select *
from MyTbl
where EXTRACT(DAYOFWEEK FROM rundt)=EXTRACT(DAYOFWEEK FROM CURRENT_DATE())

If you want to limit the number of weeks extracted, you can add another condition (example for 5 weeks):

AND rundt BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 5 WEEK) AND CURRENT_DATE()