MySQL ranking with datetime and sum

Question

I have a database where purchases are stored:

| 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 question.

What I want is a ranking per day for each user like this for user 1:

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

Note that the ranking is calculated for each day.

The next query gives part 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 question is very similar to this one: MySQL ranking, but I can't get it exactly how I want it. It is only possible to make a ranking for the day with all users. If I add the given rank feature it will look to the table and make 2022-01-02 as the first ranking (because 2.90 is higher than 0.75). How can I make the rank look to each day?

"but I can't get it exactly how I want it" You could try to explain, in this question, what you want exactly, referring to other question is only extra info, but the REAL info should be in your question. — Luuk, Mar 18 '22 at 15:26

SOS · Accepted Answer · 2022-03-20T20:46:22.273

The question isn't completely clear. However, what I think you're asking is how to rank the purchases for all users, 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

Then display the results for a single user. So the rankings for user_id = 1 would be:

history_date	user_id	DailyTotal	totalRank
2022-01-01	1	0.75	2
2022-01-02	1	2.90	2

One way is using window functions. Aggregate the total purchases per user, by day and rank the overall total with DENSE_RANK().

Note, instead of hard coding price values, use ABS() to obtain positive numbers.

WITH cte AS (
   SELECT ttl.* 
          , DENSE_RANK() OVER(
              PARTITION BY history_date 
              ORDER BY DailyTotal DESC
          ) AS TotalRank
   FROM   (
        SELECT user_id 
               , product
               , price
               , CAST(`datetime` AS DATE) AS history_date
               , SUM( ABS(price) ) OVER(
                   PARTITION BY user_id, CAST(`datetime` AS DATE)
               ) AS DailyTotal
         FROM  history
         WHERE product IN (1,2,3)
    ) ttl
)
SELECT user_id
      , history_date
      , 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 
      , DailyTotal
      , TotalRank

FROM  cte
WHERE user_id = 1
GROUP BY user_id
       , history_date
       , DailyTotal
       , TotalRank

;

Results:

user_id	history_date	product1	product2	product3	DailyTotal	TotalRank
1	2022-01-01	1	0	0	0.75	2
1	2022-01-02	1	1	1	2.90	2

db<>fiddle here

MySQL ranking with datetime and sum

1 Answers1