Skip to content
Advertisement

Determine growth in value in a specific time range

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;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement