Compare current value with previous and list the results if meet criteria

I have a raw table that consists of Timestamp, location and metric. I would like to generate a another table where outputs only the rows where current row is lower than 40% of the previous row and the difference in terms of percentage between the previous record.

Example Input:

Timestamp location metric
2021-10:00:00 Dallas 150
2021-10:05:00 Dallas 120
2021-10:10:00 Dallas 180
2021-10:15:00 Dallas 100
2021-10:20:00 Dallas 59
2021-10:25:00 Dallas 100

Expected Output:

Timestamp location metric percentage
2021-10:15:00 Dallas 100 56%
2021-10:20:00 Dallas 59 59%

Answer

I think you want lag():

select t.*, metric / prev_metric as ratio
from (select t.*,
             lag(metric) over (partition by location order by timestamp) as prev_metric
      from t
     ) t
where metric < 0.4 * prev_metric ;