I asked a question in the following link and one of the members helped me to solve most of it (to calculate column t and column pre_score). But I need to calculate one more column. I explained the details in the following link.
In summary, how I can calculate the intellectual-capital column using column t and column pre_score? intellectual-capital column considers the pre-score from all previous competitions and then multiplies each pre-score by the e^(number of days that have passed from that competition/500). in this example for each user we have at most 2 previous competitions but in my dataset it may be even more than 200 competitions therefore I need to have query that considers all scores from competitions and the time that have passed from each competition. --> the value of e is approximately 2.71828
competitionId UserId t pre_score intelectual-capital
1 100
2 100 -4 3000 3000* POWER (e, -4/500)
3 100 -5 4000 3000*POWER(e,-9/500) + 4000*POWER(e, -5/500)
1 200
4 200 -19 3000 3000*POWER(e,-19/500)
1 300
3 300 -9 3000 3000*POWER(e,-9/500)
4 300 -10 1200 3000*POWER(e,-19/500)+ 1200*POWER(e,-10/500)
1 400
2 400 -4 3000 3000* POWER(e, -4/500)
3 400 -5 4000 3000* POWER(e, -9/500) + 4000*POWER(e,-5/500)This result:
| prev_score | intellectual_capital | competitionsId | UserId | date | score | day_diff | t | prev_score |
|------------|----------------------|----------------|--------|----------------------|-------|----------|--------|------------|
| (null) | (null) | 1 | 100 | 2015-01-01T00:00:00Z | 3000 | -4 | (null) | (null) |
| 3000 | 2976.09 | 2 | 100 | 2015-01-05T00:00:00Z | 4000 | -5 | -4 | 3000 |
| 4000 | 6936.29 | 3 | 100 | 2015-01-10T00:00:00Z | 1200 | (null) | -5 | 4000 |
| (null) | (null) | 1 | 200 | 2015-01-01T00:00:00Z | 3000 | -19 | (null) | (null) |
| 3000 | 2888.13 | 4 | 200 | 2015-01-20T00:00:00Z | 1000 | (null) | -19 | 3000 |
| (null) | (null) | 1 | 300 | 2015-01-01T00:00:00Z | 3000 | -9 | (null) | (null) |
| 3000 | 2946.48 | 3 | 300 | 2015-01-10T00:00:00Z | 1200 | -10 | -9 | 3000 |
| 1200 | 4122.72 | 4 | 300 | 2015-01-20T00:00:00Z | 1000 | (null) | -10 | 1200 |
| (null) | (null) | 1 | 400 | 2015-01-01T00:00:00Z | 3000 | -4 | (null) | (null) |
| 3000 | 2976.09 | 2 | 400 | 2015-01-05T00:00:00Z | 4000 | -5 | -4 | 3000 |
| 4000 | 6936.29 | 3 | 400 | 2015-01-10T00:00:00Z | 1200 | (null) | -5 | 4000 |
Produced by this query, which now contains e
with Primo as (
select
*
, datediff(day,lead([date],1) over(partition by userid order by [date]),[date]) day_diff
from Table1
)
, Secondo as (
select
*
, lag(day_diff,1) over(partition by userid order by [date]) t
, lag(score,1) over(partition by userid order by [date]) prev_score
from primo
)
select
prev_score
, sum(prev_score*power(2.71828,t/500.0)) over(partition by userid order by [date]) intellectual_capital
, competitionsId,UserId,date,score,day_diff,t,prev_score
from secondo