I have a database that stores purchase records:

| user_id | product | price | datetime | ---------------------------------------- | 1 | 1 | -0.75 | 2022-01-01 | | 2 | 1 | -0.75 | 2022-01-01 | | 3 | 2 | -0.65 | 2022-01-01 | | 2 | 1 | -0.75 | 2022-01-01 | | 1 | 1 | -0.75 | 2022-01-02 | | 1 | 3 | -1.50 | 2022-01-02 | | 1 | 2 | -0.65 | 2022-01-02 | | 2 | 1 | -0.75 | 2022-01-02 | | 3 | 2 | -0.65 | 2022-01-02 | | 3 | 3 | -1.50 | 2022-01-02 | | 3 | 3 | -1.50 | 2022-01-02 |

N.B. Time is not important in this problem.

What I want is the ranking of each user every day. For example, the situation of user 1 is as follows:

| datetime | product1 | product2 | product3 | total | ranking | -------------------------------------------------- ------------------ | 2022-01-01 | 1 | 0 | 0 | 0.75 | 2 | | 2022-01-02 | 1 | 1 | 1 | 2.90 | 2 |

Please note that rankings are calculated on a daily basis.

The following query gives a portion of the table:

SELECT DATE(`datetime`) AS datetime, SUM(CASE WHEN product = 1 THEN 1 ELSE 0 END) AS product1, SUM(CASE WHEN product = 2 THEN 1 ELSE 0 END) AS product2, SUM(CASE WHEN product = 3 THEN 1 ELSE 0 END) AS product3, SUM(CASE WHEN product = 1 THEN 0.75 ELSE 0 END) SUM(CASE WHEN product = 2 THEN 0.65 ELSE 0 END) SUM(CASE WHEN product = 3 THEN 1.5 ELSE 0 END) as total, FROM `history` WHERE user_id=1 GROUP BY DATE(`datetime`) 

My problem is very similar to this one: MySQL ranking, but I can't get the results exactly as I asked. Only all users for each day can be ranked. If I add the given ranking function, it will look at the table and put 2022-01-02 as first (because 2.90 is higher than 0.75). How do I make rankings work for each day?