why i cannot use let or for clauses in my query, it always says its a reserved keyword, following is my query:
for "FOR" clause:
`SELECT properties.hotelId, MIN(ARRAY_AVG(rate.base_rate) FOR rate IN ARRAY_AGG(inventory.property_rates) END) AS min_avg_base_rate
FROM global properties
JOIN global inventory ON properties.hotelId = inventory.hotelId
WHERE inventory.date BETWEEN "2024-01-01" AND "2024-01-05"
GROUP BY properties.hotelId;`
for "LET" clause:
SELECT properties.hotelId, MIN(avg_base_rate) AS min_avg_base_rate
FROM global properties
JOIN global inventory ON properties.hotelId = inventory.hotelId
UNNEST inventory.property_rates AS rate
WHERE inventory.date BETWEEN "2024-01-01" AND "2024-01-05"
GROUP BY properties.hotelId
LET avg_rates = ARRAY_AGG(rate.base_rate) FOR rate IN inventory.property_rates END
SELECT properties.hotelId, MIN(ARRAY_AVG(rate) FOR rate IN avg_rates END) AS min_avg_base_rate;```
SELECT properties.hotelId,
MIN(ARRAY_AVG(ARRAY rate.base_rate FOR rate IN inventory.property_rates END)) AS min_avg_base_rate
FROM global AS properties
JOIN global AS inventory ON properties.hotelId = inventory.hotelId
WHERE inventory.date BETWEEN "2024-01-01" AND "2024-01-05"
GROUP BY properties.hotelId;