1

I have a query that runs every day and returns a counter of a column over time:

SELECT app_id, env, count(distinct session_id) as num_of_sessions, date(from_unixtime(timestamp/1000)) as st_date
FROM "etl_data_parquet"."sessions" 
where date(from_unixtime(timestamp/1000))>date_add('week', -3, CURRENT_DATE)
group by date(from_unixtime(timestamp/1000)), app_id, env
order by date(from_unixtime(timestamp/1000))

chart:

image
image777×242 7.41 KB

And I want to add another query that would show me the difference in volume per date:
so I tried to do something like this (query_40 is the original query):

select new_sessions_volume.num_of_sessions, old_sessions_volume.num_of_sessions
from query_40 as new_sessions_volume
inner join cached_query_40 as old_sessions_volume on query_40.st_date=cached_query_40.st_date

I tried to join the cached table containing the old data and the new result of the query and then subtracting them but i get this error: Error running query: no such column: query_40.st_date even tho the table does have that column.

2

What happens if you fully qualify the column name? "query_40"."st_date"

4

Sadly still the same error.

5

The problem is you’re referencing both cached_query_40 and query_40. Don’t do this. Either use the cached query or don’t. But when you use query_40 it empties the cache so cached_query_40 won’t resolve.

I should have caught this the first time I read your question.

1 Like