Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE transactions (id, sender_name, transaction_date, amount, currency) AS
SELECT '1111', 'Alice', TIMESTAMP '2023-01-03 01:26:29', 700, 'USD' FROM DUAL UNION ALL
SELECT '2222', 'Alice', TIMESTAMP '2023-01-05 04:26:29', 450, 'EUR' FROM DUAL UNION ALL
SELECT '3333', 'Alice', TIMESTAMP '2023-01-08 18:26:29', 1000, 'EUR' FROM DUAL UNION ALL
SELECT '4444', 'Alice', TIMESTAMP '2023-01-09 04:26:29', 200, 'GBP' FROM DUAL UNION ALL

SELECT '6666', 'Betty', TIMESTAMP '2023-03-12 15:26:29', 845, 'CNY' FROM DUAL UNION ALL
SELECT '7777', 'Betty', TIMESTAMP '2023-03-19 11:00:29', 300, 'CNY' FROM DUAL UNION ALL
  
SELECT '9999', 'Carol', TIMESTAMP '2023-08-26 01:26:29', 100, 'JPY' FROM DUAL UNION ALL
SELECT '8888', 'Carol', TIMESTAMP '2023-08-29 02:26:29', 501, 'JPY' FROM DUAL UNION ALL
SELECT '1234', 'Carol', TIMESTAMP '2023-09-10 03:26:29',  500, 'AED' FROM DUAL UNION ALL
SELECT '4567', 'Carol', TIMESTAMP '2023-09-11 08:26:29',  501, 'USD' FROM DUAL UNION ALL
SELECT '2345', 'Carol', TIMESTAMP '2023-09-17 04:26:29', 1001, 'NZD' FROM DUAL UNION ALL
SELECT '3456', 'Carol', TIMESTAMP '2023-09-30 05:26:29', 700, 'NZD' FROM DUAL UNION ALL
SELECT '5678', 'Carol', TIMESTAMP '2023-10-01 05:26:29', 200, 'GBP' FROM DUAL;
13 rows affected

CREATE TABLE currencyRate (id, currencyName, sellingRate, buyingRate) AS
SELECT '1', 'GBP', '1.25', '1.27' FROM DUAL UNION ALL
SELECT '2', 'EUR', '1.09', '1.11' FROM DUAL UNION ALL
SELECT '3', 'CNY', '0.14', '0.16' FROM DUAL UNION ALL
SELECT '4', 'JPY', '0,0067', '0.007' FROM DUAL UNION ALL
SELECT '5', 'NZD', '0,60', '0.62' FROM DUAL UNION ALL
SELECT '7', 'AED', '0,27', '0.29' FROM DUAL;
6 rows affected

SELECT id,
       sender_name,
       transaction_date,
       amount
FROM   (
  SELECT id,
         sender_name,
         transaction_date,
         amount,
         COUNT(CASE WHEN previous_weekly_amount > 1000 THEN 1 END) OVER (
             PARTITION BY sender_name
             ORDER BY transaction_date
             RANGE BETWEEN INTERVAL '0 00:00:00.000000000' DAY TO SECOND PRECEDING
                       AND INTERVAL '6 23:59:59.999999999' DAY TO SECOND FOLLOWING
         ) AS has_weekly_total_over_1000
  FROM   (
    SELECT id,
           sender_name,
           transaction_date,
           amount,
           SUM(amount) OVER (
             PARTITION BY sender_name
             ORDER BY transaction_date
             RANGE BETWEEN INTERVAL '6 23:59:59.999999999' DAY TO SECOND PRECEDING
                       AND INTERVAL '0 00:00:00.000000000' DAY TO SECOND FOLLOWING
           ) AS previous_weekly_amount
    FROM   transactions
  )
)
WHERE  has_weekly_total_over_1000 > 0;


ID
SENDER_NAME
TRANSACTION_DATE
AMOUNT


1111
Alice
2023-01-03 01:26:29.000000000
700

2222
Alice
2023-01-05 04:26:29.000000000
450

3333
Alice
2023-01-08 18:26:29.000000000
1000

4444
Alice
2023-01-09 04:26:29.000000000
200

6666
Betty
2023-03-12 15:26:29.000000000
845

7777
Betty
2023-03-19 11:00:29.000000000
300

1234
Carol
2023-09-10 03:26:29.000000000
500

4567
Carol
2023-09-11 08:26:29.000000000
501

2345
Carol
2023-09-17 04:26:29.000000000
1001

ID	SENDER_NAME	TRANSACTION_DATE	AMOUNT
1111	Alice	2023-01-03 01:26:29.000000000	700
2222	Alice	2023-01-05 04:26:29.000000000	450
3333	Alice	2023-01-08 18:26:29.000000000	1000
4444	Alice	2023-01-09 04:26:29.000000000	200
6666	Betty	2023-03-12 15:26:29.000000000	845
7777	Betty	2023-03-19 11:00:29.000000000	300
1234	Carol	2023-09-10 03:26:29.000000000	500
4567	Carol	2023-09-11 08:26:29.000000000	501
2345	Carol	2023-09-17 04:26:29.000000000	1001