How to implement the storage and output of user statistics?

Question

PHP

Hello!
There is an application, for example, a finance tracker, where each user can keep track of their income and expenses. This application has an analytics section where you can see the most detailed statistics (both for the selected period and for the entire time). The question is, how to store these statistics? Well, that is, it will not be correct to count everything every time this page is opened on the server / client? With 500-1000 transactions, there should be no problem, but what if the user has 10,000, 100,000, 1,000,000 transactions? How are such tasks solved?
I had two ideas:
1) When adding a transaction, recalculate the indicators and save it to the database. But the problem remains the same, for a long time.
2) When adding, add a task to the cron and recalculate and update it in the background. But, very often, after adding a transaction, the user will go to the analytics and see what has changed, and nothing will change, since the server will not have time to calculate everything.

PS I took the financial application as just an example. I'm just interested in how similar tasks are solved in similar applications.

Answer 1

Well, that is, it will not be correct to count everything every time this page is opened on the server / client?

Why, very even correctly, fast storages and SQL/noSLQ DB are thought up for this purpose.

but what if the user has 10,000, 100,000, 1,000,000 transactions? How are such tasks solved?

Firstly, the user still does not receive all the multiplication of transactions in the statistics at a time, he receives it page by page, and these are quite normal requests for 50-100 records at a time, which is quite fast. Well, all sorts of rarely updated and frequently requested data is stored in the cache so as not to pull the database in vain.

go to analytics and see what has changed, and nothing will change, since the server will not have time to calculate everything.

It's just that I don't know what a dead server should be in order not to have time to "calculate" the time required to go through 1-2 application screens.

Answer 2

Create a table where statistics will be stored (replenishment, etc.), then when you receive the current balance, calculate it from this very table. You still have to get the whole history (otherwise how the user will get it)