CODEWITHSUNDEEP
mysqlsql
Rakesh Kumar
Rakesh Kumar

Reputation: 177

Mysql query to find percentage change in the value over a time

I have a table with values and date associated with the user and track/units he has invested in. Something like below:

id , track_id , user_id , value  , created_at          , updated_at,
 1 ,        7 ,       7 , 310.00 , 2014-07-11 11:55:20 , 0000-00-00 00:00:00,
 2 ,        2 ,       3 , 400.00 , 2014-07-10 00:00:00 , 0000-00-00 00:00:00,
 3 ,        2 ,       3 , 300.00 , 2014-07-11 00:00:00 , 0000-00-00 00:00:00,
 4 ,        4 ,       7 , 500.00 , 2014-07-11 09:23:17 , 0000-00-00 00:00:00,

I want a query to fetch my result something like

user_id, gain(%)

So basically the query would fetch top N say 3 gainers over last 7 days. My Database in MySql

Upvotes: 1

Views: 2208

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

This should do what you need, I returned the old/new value for illustration, you can remove them from the select list if you just want the user id and % gain -

select x.user_id,
       x.value as new_val,
       y.value as prev_val,
       (y.value / x.value - 1) * 100 as gain_pct
  from tbl x
  join tbl y
    on x.user_id = y.user_id
 where x.created_at =
       (select max(z.created_at)
          from tbl z
         where z.user_id = x.user_id
           and z.created_at between date_sub(current_date, interval 7 day) and
               current_date)
   and y.created_at =
       (select min(z.created_at)
          from tbl z
         where z.user_id = x.user_id
           and z.created_at between date_sub(current_date, interval 7 day) and
               current_date)
order by gain_pct desc
limit 3

Use LIMIT to return top 3, 5, 10, etc.

Fiddle: http://sqlfiddle.com/#!2/037243/10/0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is a bit of a pain. The following query gets the minimum and maximum dates over the prior week:

select user_id, min(created_at) as mind, max(created_at) as maxd
from table t
where created_at >= now() - interval 7 day
group by user_id;

Now, you can use this to get the appropriate values by joining in:

select user_id, tmin.value, tmax.value
from (select user_id, min(created_at) as mind, max(created_at) as maxd
      from table t
      where created_at >= now() - interval 7 day
      group by user_id
     ) umm join
     table tmin
     on umm.user_id = tmin.user_id and umm.mind = tmin.created_at join
     table tmax
     on umm.user_id = tmax.user_id and umm.maxd = tmax.created_at;

This gives you the information to do the query. Something like:

select user_id, tmin.value, tmax.value,
       (tmax.value - tmin.value) / tmax.value as gain_ratio
from (select user_id, min(created_at) as mind, max(created_at) as maxd
      from table t
      where created_at >= now() - interval 7 day
      group by user_id
     ) umm join
     table tmin
     on umm.user_id = tmin.user_id and umm.mind = tmin.created_at join
     table tmax
     on umm.user_id = tmax.user_id and umm.maxd = tmax.created_at;

Upvotes: 3

Related Questions