You could definitely use a workflow in Retool to call the api query on a schedule! However, workflows don't push live updates to apps yet.
Retool app queries also have a "run periodically" option, as Scott alluded to, which might be nice here since this will only run when someone is interacting with the app.
We don't have a setting for limiting a query to run x number of times in a day, so I agree that you'll still have to create some logic to track that if you think you may be close to hitting the 1000 requests (and possibly some visual indicator that tells the user they can no longer run the query).