INNER JOIN after a GROUP BY statement with COUNT Function-CodePudding

I have 2 tables called TRANSACTIONS and FEES.

TRANSACTIONS contains my transaction data like this:

id	date	bank_name	tranfer_amount
1	2014-02-21	Bank A	1000
2	2014-02-30	Bank B	250
3	2014-03-14	Bank B	150
N	...	...	...

FEE contains the fee base on number of transactions. Note: if needed you can change this table definition

bank_name	from_transaction	fee
Bank A	1	1.5
Bank A	11	1.4
Bank A	21	1.3
Bank B	1	1.0
Bank B	11	0.9
Bank B	21	0.8

I am trying to write a SELECT statement which will return the results like this:

month	bank_name	transaction_count	fee	total_fees
2014-02	Bank A	5	1.5	7.5
2014-02	Bank B	15	0.9	13.5
2014-03	Bank A	30	1.3	39
2014-03	Bank B	25	0.8	20

I'd created a group by select statement to so have the data = YYYY-MM, back_name, transaction_count, but how do I create a join statement and compare the transaction count with the from_transaction column?

SELECT 
    to_char(date, 'YYYY-MM') AS month, 
    transactions.bank_name, 
    COUNT(transfer_amounts) AS transaction_count
FROM 
    transactions
GROUP BY 
    to_char(date, 'YYYY-MM'), transactions.bank_name

Result:

month	bank_name	transaction_count
2014-02	Bank A	5
2014-02	Bank B	15
2014-03	Bank A	30
2014-03	Bank B	25

Any ideas are appreciated

CodePudding user response：

One possible approach is

WITH fees_expanded AS (
  SELECT *, COALESCE(LEAD(from_transaction) OVER w - 1, 9999999) AS to_transaction
    FROM fees
  WINDOW w AS (PARTITION BY bank_name ORDER BY from_transaction)
)
SELECT t.*, f.fee, t.transaction_count * f.fee AS total_fees 
  FROM transactions t
  JOIN fees_expanded f
    ON t.bank_name = f.bank_name
   AND t.transaction_count BETWEEN f.from_transaction AND f.to_transaction;

CodePudding user response：

Try a derived table with a join.

SELECT t.*, t.transaction_count * f.fee as [total_fees]
FROM ( 
    SELECT to_char(date, 'YYYY-MM') AS month, transactions.bank_name, 
    COUNT(transfer_amounts) as transaction_count
    FROM transactions
    GROUP BY to_char(date, 'YYYY-MM'), transactions.bank_name
    ) t
INNER JOIN FEES f
ON f.bank_name = t.bank_name