Get difference from an aggregated value and another column in SQL

Question

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 1

You have to use a subquery to calculate the last_week_value :

select v.user_id, sum(v.value_awarded) as last_week_value
from value_awarded_stats_history v
where v.date > (current_date - '1 week')
group by v.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 v.user_id, sum(v.value_awarded) as last_week_value
from value_awarded_stats_history v
where v.date > (current_date - '1 week')
group by v.user_id) t2 on t2.user_id = t1.user_id