Recursive Query to generate dates not working

Question

I am trying to generate a series of dates by RECURSION in BigQuery. Unfortunately, the solution DOESN'T work !! Can someone please explain where I might be going wrong ??

Note: I understand that BigQuery provides the GENERATE_DATE_ARRAY, but just curious why my method wouldn't work (educational purpose only)!!?? Basically, I am trying to generate series of dates starting '2025-05-29' and going back to '2025-04-29'.

WITH
  RECURSIVE gen_dates AS (
  SELECT
    CURRENT_DATE() AS days
  UNION ALL
  SELECT
    DATE_SUB(days, INTERVAL 1 DAY)
  FROM
    gen_dates
  WHERE
    days >= DATE_SUB(days, INTERVAL 30 DAY) AND days <= CURRENT_DATE())
SELECT *
FROM gen_dates;

Thanks.

Answer 1

You generate an infinite recursion because you start with the current date, go in recursion where the day is greater than itself - 30 days then decrement the day and go in recursion… you always compare the newly calculated day, not the current date of the anchor, so it's always greater than itself less 30 days.

WHERE days > DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) should work.