SQL query for deposits and withdrawals. With condition the withdrawals after 3 months don't cou-CodePudding

I'm trying to solve this task. But I can't understand how to add 3 months condition right. Please help

Partners receive bonuses for every client that they introduce to the company. Assume that the company has 3 active partners.

Partner paid on a monthly based - for every introduced client partner gets 0.5% of the amount deposited by the client excluding withdrawals. But, with the condition that if the client withdraws the amount more than 3 months after the amount was deposited – such withdrawals are not included in the partner’s bonus reduction.

For example, in August partner’s client deposited $1000 and withdraw $100. Partner gets (1000-100)*0.5%=$4.5. But in case the $100 withdrawal is from the deposit made by the client in April, in this case, the partner’s bonus is (1000)*0.5%=$5

Task: write a SQL script that calculates the monthly bonus amount for each partner.

Data is in the link [1]: https://docs.google.com/spreadsheets/d/17xo_gnu9nEwXuCNb8vBLEVrhPEKJaIRN/edit?usp=sharing&ouid=112201519964034772995&rtpof=true&sd=true

CodePudding user response：

Between worls only when the date left of the AND is lower tha the right.

So your code mus look like

select 
     * 
from 
    transactions 
where date=any (select 
date 
from 
transactions 
where type='withdrawal' 
and date between (date - interval '3 month') AND  date)  
and type='deposit' 
order by 1,3,7