How to organize the storage of records of sold goods in the database?

Question

PHP

Good afternoon
Purpose:
There was a need to keep records of sold goods in the database.
Why:
In each category (example: TV, household appliances, cell phones) display the best-selling product of this category. Those. when viewing a catalog with TVs, the block "bestseller" will display the best-selling product of this category, i.е. the best-selling TV, etc. by category.
Table structure:
id (autoincrement)
product_id (id of the product sold, this is a sales offer) (int)
parent_product_id (id of the parent product) (int)
category_id (id of the product category) (int)
qty (quantity of product sold) (int)
Question:
How to do it better? Make a query to the database every time or store the general data by product_id in another table? And by cron once a day to recalculate the number of goods sold?

Answer 1

Let me give you an example of how it was with our store.
At first we had 10,000 units. there were no problems with each visitor entering the database and taking up-to-date data on what sells best from there.
However, when there were 2 million products and the project almost went into high-load, it was decided to go into the database every half a day, look for the best-selling product for each group of products and immediately build a cache for display.
So it all depends on what systems you use, what load, what scaling. In each case, the solutions are good in their own way.

Answer 2

Something like

SELECT  *
FROM    (SELECT category_id,
                ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY qty DESC) AS RowNumber
         FROM   table_name
        ) AS a
WHERE   a.RowNumber = 1