Rank MySQL by date time and sum

Question

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:

<code>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`)
</code>

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?

Answer 1

The question is not entirely clear. However, I think what you're asking is how to rank all users' purchases by day:

history_date	user_id	DailyTotal	totalRank
2022-01-01	2	1.50	1
2022-01-01	1	0.75	2
2022-01-01	3	0.65	3
2022-01-02	3	3.65	1
2022-01-02	1	2.90	2
2022-01-02	2	0.75	3