Query table which whose name changes everyday

I have a table which is like - 'numberedlanding_20210606_storefront'

this table is generated every day and changes in the time stamp. For example, nextday table name would be 'numberedlanding_20210607_storefront'

I am writing a query that uses this table as a base table and does further does aggregations such as -

For example -

with df1 as (select * from 'numberedlanding_20210606_storefront' where col1>0),

df2 as (select *,col1/col2 as test from df1),

select * from df1 a join df2 b on a.name=b.name

Now since this table name changes everyday how do I make it dynamic in this query?

I tried looking into Bigquery's wildcard but I think it applies on to variable at the end of the name, I want to change the name which is in the middle.

Also, here is something which I tried but was not successful -

CREATE TEMP FUNCTION
  getTableName() AS ((CONCAT("numberedlanding_",FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)),"_storefront")));

SELECT
  getTableName()

This just gives me the table name I want to use. Any suggestions would be really helpful.

CodePudding user response：

SELECT * FROM numberedlanding_* WHERE _TABLE_SUFFIX = CURRENT_DATE() || '_storefront'