Hi everyone, I am new to flink and I am planning to use flink for one of our usecase when we have batch+streming feature query for model predictions.So we have a scenario where in we have to get user score for last 30days interval including current time and that feature gets executed every time a user visit to our page. so how to create a batch query for the T-1 till T-30 and streaming for T in flink sql and is it good idea to query the batch every time new users visit the page or its required to be cached any suggestion on that ?

The scenario is unclear. Where are the user scores stored?

We don't store scores at the moment since it keeps changing with time we store user visit as a row and time of the visit in postgresfor history from Kafka as source

It is hard to tell without knowing what goes into calculating a feature score and what data you have in the events of the main flow. In general, you can do arbitrary calculations in a ProcessFunction and use Flink’s managed state instead of your database to store the data. If you absolutely need to use SQL further, you could potentially emit the updated scores from the ProcessFunction and turn the resulting stream into a “table”. https://nightlies.apache.org/flink/flink-docs-release-1.15/docs/dev/table/data_stream_api/#converting-between-datastream-and-table

Hmm.Calculation is simple in our case which we do it in postgres sql we do simple for every execution of the model feature which is most likely every minute it get called

Select user-id,count(distinct visit_time) as user_visit_count
from user_journey
Where visit_time::date between CURRENT_DATE and (CURRENT_DATE - INTERVAL '30 DAY')::DATE
group by user-id

Definition of a “good solution” is a vague one, because everything is a tradeoff in the end. Fundamentally, you can store such state in RocksDB. You could use ListState inside of a KeyedProcessFunction to add the timestamps of the user visits. Whenever needed, you could just retrieve the size of the list. It is probably OK to even have a couple of duplicates sometimes and instead of cleaning them up on every update, set a timer (onTimer method) to periodically go over the list and remove the duplicates.

hmm yeah got your point we can define ttl for 30 days in that case to auto erase