presto athena tables as variables [duplicate]

Question

I have an SQL in aws-athena which looks more or less like this

SELECT * FROM "db1"."2021_08_31" WHERE condition
Union
SELECT * FROM "db2"."2021_08_31" WHERE condition

...

Is there an intelligent way of rewriting this, so in case I want to switch from 2021_08_31 to 2021_09_30 I would only need to change it in one place. For example as follows (which does not work)

tbl = "2021_09_30"

SELECT * FROM "db1".tbl WHERE condition
Union
SELECT * FROM "db2".tbl WHERE condition

Answer 1

You can use a WITH clause to factor out the access to the underlying table:

WITH data AS (TABLE "db1"."2021_08_31")
SELECT * FROM data WHERE condition1
UNION
SELECT * FROM data WHERE condition2
...