sql get balance at end of year

I have a transactions table for a single year with the amount indicating the debit transaction if the value is negative or credit transaction values are positive.

Now in a given month if the number of debit records is less than 3 or if the sum of debits for a month is less than 100 then I want to charge a fee of 5.

I want to build and sql query for this in postgre:

select sum(amount), count(1), date_part('month', date) as month from transactions where amount < 0 group by month;

I am able get records per month level, I am stuck on how to proceed further and get the result.

Answer

You can start by generating the series of month with generate_series(). Then join that with an aggregate query on transactions, and finally implement the business logic in the outer query:

select sum(t.balance) 
    - 5 * count(*) filter(where coalesce(t.cnt, 0) < 3 or coalesce(t.debit, 0) < 100) as balance
from generate_series(date '2020-01-01', date '2020-12-01', '1 month') as d(dt)
left join (
    select date_trunc('month', date) as dt, count(*) cnt, sum(amount) as balance,
        sum(-amount) filter(where amount < 0) as debit
    from transactions t 
    group by date_trunc('month', date)
) t on t.dt = d.dt

Demo on DB Fiddle:

| balance |
| ------: |
|    2746 |