[SOLVED] MySQL ranking with datetime and sum

Issue

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?

Solution

The question isn’t completely clear. However, what I think you’re asking is how to rank the purchases for all users, by day:

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

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:

db<>fiddle here

Answered By – SOS

Answer Checked By – Robin (BugsFixing Admin)