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.

Why can't you just use WHERE rundt BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE() — Charlieface
– Charlieface, Commented Apr 22 at 13:27
i need counts for specific days in each week... not for an entire date range.. for e.g if i execute today, i need for Apr 22, 15 and 8th only (only Tuesdays in every past 3 weeks since today is Tuesday).. hope this clarifies — marie20
– marie20, Commented Apr 22 at 14:11

d r · Accepted Answer · 2025-04-23 08:33:05Z

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

answered Apr 23 at 8:33

d r

8,6622 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2025-04-22 15:02:16Z

0

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'

answered Apr 22 at 15:02

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Comments

user27821072 · Accepted Answer · 2025-04-22 20:21:08Z

0

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'

answered Apr 22 at 20:21

user27821072

11 bronze badge

Comments

tinazmu · Accepted Answer · 2025-04-22 22:13:00Z

0

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()

answered Apr 22 at 22:13

tinazmu

5,5442 gold badges12 silver badges25 bronze badges

Collectives™ on Stack Overflow

Implementing iteration in Standard SQL (BigQuery)

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related