SQL find first value greater than X in a sorted column

Question

Given a table like this

id      total_cost  margin
a       2           10%
b       4           15%
c       6           4%
x       7           90%
y       8           13%
z       9           0%

Where the total cost is defined as a running aggregate of some positive column, so it's always sorted. I need to find out in a single SQL command (needs to be efficient) the average margin where cost first exceeds or equals a number X.

i.e. given X = 7.5

I need to find the average margin where total_cost first exceeds or equal to 7.5. In this case the condition would be applied to the first 5 columns since

id      total_cost  margin
y       8           13%

is the first column where total_cost exceeds 7.5. The result would be

avg(10%, 15%, 4%, 90%, 13%)

Answer 1

Use the window function lag():

select id, total_cost, margin
from (
    select *, lag(total_cost) over (order by total_cost) prev_total_cost
    from the_table
    ) s
where coalesce(prev_total_cost, 0) < 7.5

 id | total_cost | margin 
----+------------+--------
 a  |          2 |   0.10
 b  |          4 |   0.15
 c  |          6 |   0.04
 x  |          7 |   0.90
 y  |          8 |   0.13
(5 rows)

To get the average:

select avg(margin)
from (
    select *, lag(total_cost) over (order by total_cost) prev_total_cost
    from the_table
    ) s
where coalesce(prev_total_cost, 0) < 7.5

          avg           
------------------------
 0.26400000000000000000
(1 row)