Bharathwaaj Bharathwaaj - 1 month ago 7
SQL Question

Get difference from an aggregated value and another column in SQL

Suppose I have the following tables:

user_current_value_stats

id user_id current_total_value
1 12 175
2 14 125
3 17 170
4 18 115


value_awarded_stats_history

id user_id value_awarded date
1 12 55 2016-10-5 00:00:00+05:30
2 14 50 2016-10-5 00:00:00+05:35
3 17 70 2016-10-5 00:00:00+06:35
4 18 40 2016-10-5 00:00:00+07:34
5 12 50 2016-10-11 00:00:00+04:30
6 14 65 2016-10-11 00:00:00+04:40
7 17 75 2016-10-11 00:00:00+05:40
8 18 -35 2016-10-11 00:00:00+06:40
9 12 30 2016-10-12 00:00:00+04:30
10 14 65 2016-10-12 00:00:00+04:40
11 17 35 2016-10-12 00:00:00+05:40
12 18 65 2016-10-12 00:00:00+06:40
13 12 40 2016-10-13 00:00:00+04:40
14 14 -55 2016-10-13 00:00:00+05:40
15 17 -10 2016-10-13 00:00:00+05:45
16 18 45 2016-10-13 00:00:00+06:40


Expected Result

id user_id current_total_value last_week_value difference
1 12 175 130 45
2 14 125 140 -15
3 17 170 180 -10
4 18 115 70 45


I need to


  • select all values from from
    user_current_value_stats

  • Aggregate value of the user from
    value_awarded_stats_history
    for the last week as
    last_week_value
    (date will be given)

  • The difference between
    last_week_value
    and
    current_total_value
    as difference



The result should have the following columns id, user_id, current_total_value, last_week_value, difference.

(Also
current_total_value
can also be got as aggregate of all the
value_awarded
for the particular user. The column
value_awarded
is actually redundant data and is the sum of
value_awarded
for from
value_awarded_stats_history
for that user.)

Answer

You have to use a subquery to calculate the last_week_value :

select u.user_id, sum(v.value_awarded) as last_week_value
from user_current_value_stats u
inner join value_awarded_stats_history v on v.user_id = u.user_id
where v.date > (current_date - '1 week')
group by u.user_id

So the complete query would be something like this:

select t1.id
, t1.user_id
, t1.current_total_value
, t2.last_week_value
, (t1.current_total_value - t2.last_week_value) as difference
from user_current_value_stats t1
left outer join
(select u.user_id, sum(v.value_awarded) as last_week_value
from user_current_value_stats u
inner join value_awarded_stats_history v on v.user_id = u.user_id
where v.date > (current_date - '1 week')
group by u.user_id) t2 on t2.user_id = t1.user_id
Comments