Variable to use in a Query

[JMJ1]

Hi,

I would like to create or define a environment variable/s to add in a query , this variables should be get from a table. 

I mean:

variable last_day_dt_Madrid= (select max(day_dt) from table_raw where city=’Madrid’ )

variable last_day_dt_Barcelona=(select max(day_dt) from table_raw where city=’Barcelona’ )

select city,  count(*)

from table_raw

where  ( city = ‘Madrid’ and date_dt = ${last_day_dt_Madrid} ) 

or         ( city = ‘Barcelona’ and date_dt = ${last_day_dt_Barcelona} ) 

group by 1

table_raw is a very huge table partitioned by date_dt in BQ.

I am tested to include the max date as a join of PDT table with the max date_dt values by city but the performance it’s not good because in this way it don’t use the partition field (date_dt).

Anyone can help me?