By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE payments_history(payment_date , account_id , currency, amount) AS
SELECT TO_DATE ('05/12/2021','DD/MM/YYYY'), 2291969088, 'GBP', 10.00 FROM DUAL UNION ALL
SELECT TO_DATE ('05/12/2021','DD/MM/YYYY'), 7851880663, 'USD', 20 FROM DUAL UNION ALL
SELECT TO_DATE ('06/12/2021','DD/MM/YYYY'), 5326844767, 'USD', 3.000 FROM DUAL UNION ALL
SELECT TO_DATE ('05/12/2021','DD/MM/YYYY'), 3668657617, 'EUR', 40 FROM DUAL UNION ALL
SELECT TO_DATE ('06/12/2021','DD/MM/YYYY'), 9040142052, 'GBP', 30.000 FROM DUAL
5 rows affected
Create TABLE Historics_rates(t_date,from_ccy,to_ccy,rate) AS
SELECT TO_DATE ('06/12/2021','DD/MM/YYYY'),'GBP','EUR',1.1832 FROM DUAL UNION ALL
SELECT TO_DATE ('06/12/2021','DD/MM/YYYY'),'AUD','GBP',0.5263 FROM DUAL UNION ALL
SELECT TO_DATE ('06/12/2021','DD/MM/YYYY'),'EUR','GBP',0.8452 FROM DUAL UNION ALL
SELECT TO_DATE ('05/12/2021','DD/MM/YYYY'),'USD','GBP',0.7388 FROM DUAL UNION ALL
SELECT TO_DATE ('05/12/2021','DD/MM/YYYY'),'EUR','USD',1.1441 FROM DUAL
5 rows affected
select PH.PAYMENT_DATE,
SUM(
CASE
WHEN PH.CURRENCY = 'GBP'
THEN PH.AMOUNT
WHEN PH.CURRENCY IN ('USD', 'AUD', 'EUR')
AND HR.from_ccy = PH.CURRENCY
AND HR.to_ccy = 'GBP'
THEN PH.AMOUNT*HR.rate
END
) As total
FROM historics_rates HR
RIGHT JOIN PAYMENTS_HISTORY PH
on HR.t_date = PH.payment_date
AND PH.Currency = HR.from_ccy
WHERE PH.payment_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3)
AND PH.payment_date < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2)
GROUP BY PH.PAYMENT_DATE;