CODEWITHSUNDEEP
mysqlsqljoinsubtraction
Wader
Wader

Reputation: 9883

Mysql subtracting values using row selected from min timestamp, grouping by id

I've been at this for a few hours now to no avail, pulling my hair out.

Edit: Im wanting to calculate the difference between the overall_exp column by using the same data from 1 day ago to calculate the greatest 'gain' for each user

Currently I'm take a row, then select a row from 1 day ago based on the first rows timestamp then subtract the overall_exp column from the 2 rows and order by that result whilst grouping by user_id

SQL Fiddle: http://sqlfiddle.com/#!2/501c8

Here is what i currently have, however the logic is completely wrong so im pulling 0 results

SELECT rsn, ts.timestamp, @original_ts := SUBDATE( ts.timestamp, INTERVAL 1 DAY), ts.overall_exp, ts.overall_exp - previous.overall_exp AS gained_exp
    FROM tracker AS ts
INNER JOIN (
    SELECT user_id, MIN( TIMESTAMP ) , overall_exp
    FROM tracker
    WHERE TIMESTAMP >= @original_ts
    GROUP BY user_id
) previous 
    ON ts.user_id = previous.user_id
JOIN users
    ON ts.user_id = users.id
GROUP BY ts.user_id
ORDER BY gained_exp DESC

Upvotes: 0

Views: 1636

Answers (2)

user359040
user359040

Reputation:

Try:

select u.*, max(t.`timestamp`)-min(t.`timestamp`) gain
from users u
left join tracker t 
on u.id = t.user_id and
   t.`timestamp` >= date_sub(date(now()), interval 1 day) and
   t.`timestamp` < date_add(date(now()), interval 1 day)
group by u.id
order by gain desc

SQLFiddle here.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can do this with a self-join:

select t.user_id, max(t.overall_exp - tprev.overall_exp)
from tracker t join
     tracker tprev
     on tprev.user_id = t.user_id and
        date(tprev.timestamp) = date(SUBDATE(t.timestamp, INTERVAL 1 DAY))
group by t.user_id

A key here is converting the timestamps to dates, so the comparison is exact.

Upvotes: 1

Related Questions