SQL first value by group

Question

In a table like this

acc_id   time   approved_amount   balance
 11       Jan14     580             500
 11       Feb14     580             400
 11       Mar14     580             300
 11       Apr14     580             200
 22       Jan14     .               800
 22       Feb14     .               700
 22       Mar14     .               600

I want to create a column orig_amount that would be equal to approved_amount if this is not null and to the first value of balance - to balance at time = min(time) by acc_id if approved_amount is null.

Therefore the desired output would look like this:

acc_id   time   approved_amount   balance   orig_amount
 11       Jan14     580             500        580
 11       Feb14     580             400        580
 11       Mar14     580             300        580
 11       Apr14     580             200        580
 22       Jan14     .               800        800
 22       Feb14     .               700        800
 22       Mar14     .               600        800

I have now this

create table second_table as
   select *,
      coalesce(approved_amount, case when time = min(time) then 
       balance end ) as orig_amount
   from first_table
   group by acc_id
   order by acc_id, time;

But still not the desired result. Can anybody help please?

Answer 1

You can use the first_value() window function and coalesce().

SELECT acc_id,
       time,
       approved_amount,
       balance,
       coalesce(approved_amount,
                first_value(balance) OVER (PARTITION BY acc_id
                                           ORDER BY time)) orig_amount
       FROM first_table;

db<>fiddle