I have the following tables in my data base with following columns:
Table 1(Water_Usage_per_hour): user_id, water_used_per_hour, Update_time (when data updated)
Table 2(User_info): user_id, locality, state
There are two applications interacting with this database:
Application 1: it updates the data of different users at different instant of time. We believe that different users started using the application at different times therefore their hourly updation will also differ
Application 2: this does the following query->
it gets the total water used every hour by the users belonging to a particular locality ( say 'a' ) for the last 6 hours ie
total water used by all users of locality 'a' in last hour, total water used by all users of locality a in hour before last and so on.
can any body help us with the possible query of the 2nd application ???
To do the query on Application2, the latest data should be inserted instead of being updated when working with Application1 or the history data could be stored in other place before being updated ,otherwise, you could not get the
data last 6 hours data.