Retrieve user wise sum cumulative reached records-CodePudding

I have workouts_data table which have users cycling details with distance they achieved on daily basis. I would like to get each user records once they crossed 1000 value in distance column and if that user not crossed 1000,then can get max value of records.

I am looking to order record by 1000 reached user and date wise in ascending order,then distance descending order.So then only i can get first achieved user on top and remaining not achieved user next wise versa.

Example Data:
Date       User        Distance
1614944833   1           100
1614944232   2           100
1624944831   1           150
1615944832   3           250
1614644836   1           500
1614954835   2           100
1614344834   3           100
1614964831   1           260
1614944238   1           200

I have tried with below cumulative sum query,but it not works,

select  date,
            sum(distance)  as distance,
            (case when (sum(distance) >= 1000)  
                  THEN  1000  ELSE sum(distance) END) as distance_completed  
from `workouts_data` as `w1` 
where `date` between 1609372800 and 1640995140 
and `w1`.`User` in (1,2,3)  group by `User` 
order by `distance_completed` desc,`date` asc

Expected Output

Date       User        distance_completed
1614964831   1           1010
1614954835   2           200
1614344834   3           350

Can anyone suggest with proper query or stored procedure method?

CodePudding user response：

You can try:

 select   w1.`user`, 
         case when t1.distance >='1000' 
         then '1000' else t1.distance end as distance,
         t3.date
from workouts_data w1
inner join (
               select   `user`,
                         sum(distance)  as `distance`           
               from `workouts_data`
               where `date` between 1609372800 and 1640995140
               and  `user` in (1,2,3)  
               group by `user`
             ) as t1 on w1.user=t1.user
inner join (
              select `date`,
                      id 
               from workouts_data
               where (id,user) in (select max(id),user from workouts_data group by user)
              ) as t3 on w1.date=t3.date                                  
order by t1.distance  desc;

Result:

user  distance    date
1     1000       1614964831
3     350        1614344834
2     200        1614954835

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/164

CodePudding user response：

You can use the sums in that way, you need a outer Select to process the data after grouping

I can't figure out, why you choose these dates, so ou need to check the MAX(date) if it fits

SELECT 
    `Date`, `User`, IF(distance_completed > 1000, 1000,distance_completed)  as distance_completed
FROM (SELECT MAX(`Date`) as 'date', `User`, SUM(`Distance`) AS  distance_completed
FROM exdata w1
where `date` between 1609372800 and 1640995140 
and `w1`.`User` in (1,2,3)  group by `User` ) t1
order by `distance_completed` desc,`date` asc

      Date | User | distance_completed
---------: | ---: | -----------------:
1624944831 |    1 |               1000
1615944832 |    3 |                350
1614954835 |    2 |                200

db<>fiddle here

SELECT 
    `Date`, `User`, IF(distance_completed > 1000, 1000,distance_completed)  as distance_completed
FROM (SELECT MIN(`Date`) as 'date', `User`, SUM(`Distance`) AS  distance_completed
FROM exdata w1
where `date` between 1609372800 and 1640995140 
and `w1`.`User` in (1,2,3)  group by `User` ) t1
order by `distance_completed` desc,`date` asc

      Date | User | distance_completed
---------: | ---: | -----------------:
1614644836 |    1 |               1000
1614344834 |    3 |                350
1614944232 |    2 |                200

db<>fiddle here

When we add an id you can get the wanted numbers, but this vital information that should gave been in the question all along.

SELECT 
    w2.`Date`, t1. `User`, IF(distance_completed > 1000, 1000,distance_completed)  as distance_completed
FROM (SELECT  `User`, SUM(`Distance`) AS  distance_completed
FROM workouts_data  w1
where `date` between 1609372800 and 1640995140 
and `w1`.`User` in (1,2,3)  group by `User` ) t1 
inner join (SELECT `Date` , `User` 
FROM workouts_data  wd2 
WHERE (id,`User`) IN (SELECT MAX(id),`User` FROM  workouts_data GROUP BY `User`)) w2 ON t1.`User` = w2.`User`
order by `distance_completed` desc,`date` asc

      Date | User | distance_completed
---------: | ---: | -----------------:
1624944831 |    1 |                950
1614344834 |    3 |                350
1614954835 |    2 |                200

db<>fiddle here