Calculating the Transaction Balance using SQL-CodePudding

PROBLEM: You are given the history of your bank account for a year. Each transaction was either a credit card payment or an incoming transfer. There is a fee for holding a credit card ($5) that you have to pay every month. However, you are not charges this amount if you made at least three (3) credit card payments for a total of at least $100 in that month.

GOAL: Compute the balance at the end of the year

Got a table with the following values:

amount|date
-----------------
 1000 |2020-01-06
  -10 |2020-01-14
  -75 |2020-01-02
   -5 |2020-01-25
   -4 |2020-01-29
 2000 |2020-03-10
  -75 |2020-03-12
  -20 |2020-03-15
   40 |2020-03-15
  -50 |2020-03-17
  200 |2020-10-10
 -200 |2020-10-10

Below is the script to create the table:

-- create a table
 create table transactions (
        amount integer not null,
        date date not null
  );
-- insert some values
INSERT INTO transactions VALUES (1000, '2020-01-06'), (-10, '2020-01-14'), (-75, '2020-01-20'), (-5, '2020-01-29'), (-4, '2020-01-29'), (2000, '2020-03-10'), (-75, '2020-03-12'), (-20, '2020-03-15'), (40, '2020-03-15'), (-50, '2020-03-17'), (200, '2020-10-10'), (-200, '2020-10-10');

**I am very close but I can't seem to find a way to sum up the results of sum_payments, **

-- fetch some values
WITH generated_months as(
  SELECT 1 as month UNION
  SELECT 2 UNION
  SELECT 3 UNION
  SELECT 4 UNION
  SELECT 5 UNION
  SELECT 6 UNION
  SELECT 7 UNION
  SELECT 8 UNION
  SELECT 9 UNION
  SELECT 10 UNION
  SELECT 11 UNION
  SELECT 12 ),
charges as(
  select sum(t1.amount) as sum_charges
      , count(1) as month_count
      , extract(month from date) as month
    from transactions t1
   where amount < 0),
   
 payments as(
  select sum(t2.amount) as sum_payments
      , EXTRACT(year from date) as year
      , count(1) month_count
    from transactions t2
   where amount > 0
   group by EXTRACT(year from date)
),
 fees as (
  select sum(CASE WHEN c.month_count >= 3 AND c.sum_charges <= -100
              THEN 0
              ELSE 5
               END            ) as calculated_fee
    from generated_months gm
    left join charges c on gm.month = c.month
)
select p.sum_payments - f.calculated_fee - c.sum_charges as FINAL_BALANCE
from payments p
cross join fees f
cross join charges c;

WHERE I AM STUCK: I am trying to find a way to grab the sum of all charges by attempting to execute the following operation in the main (non-CTE) query (p.sum_payments - f.calculated_fee - c.sum_charges); however, I just can't seem to find a way to add/aggregate c.sum_charges to my final balance (FINAL_BALANCE)... What am I missing here? Thanks in advance!

DESIRED OUTPUT:

FINAL_BALANCE|
-------------|
2746         |

2801 (Sum of all debits/credits  /-) - 55(fees from 11 months x $5 a month) = $2746 (Final Balance)

CodePudding user response：

You need to group your data by months, then find out the number of debits on a credit card, then find out the total amount and subtract the required amount.

select sum(amount) - (12 - (select COUNT(*) FROM (SELECT date_trunc('month', date) AS month, sum(amount) as monthly_sum, count(amount) as monthly_count 
     FROM transactions WHERE amount<0
 GROUP BY month ) t where t.monthly_sum>=-100 and t.monthly_count>=3) ) * 5 as final_balance  from transactions

Demo in DBfiddle