I have a raw table that consists of
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.
I think you want
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 ;