Determine growth in value in a specific time range

Question

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

Answer 1

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;