Given the following data set example, how should I structure my SQL
query in order to determine if the value
has grown over time (given a time range in the query) for a specific UserId
by returning either a positive/negative growth percentage result or a true
/false
result
UserId | timestamp | value |
---|---|---|
1 | 1617711825 | 350 |
1 | 1617711829 | 400 |
1 | 1617711830 | 450 |
5 | 1617711831 | 560 |
Given the above example, we can observe that the value
for UserId=1
has grown by a certain percentage.
The expected result would be:
UserId | growthPercentage | hasValueIncreased |
---|---|---|
1 | 50% | 1 |
Advertisement
Answer
You can get the first and last values and then do whatever calculation you like. One method is:
select userId, value_first, value_last, (value_first < value_last) as is_growing, 100 * ((value_last / value_first) - 1) as increase_percentage from (select t.*, first_value(value) over (partition by userId order by timestamp) as value_first, first_value(value) over (partition by userId order by timestamp desc) as value_last from t ) t group by userId, value_first, value_last;