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,
       usd_amount
FROM   (
  SELECT id,
         sender_name,
         transaction_date,
         amount,
         usd_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 t.id,
           t.sender_name,
           t.transaction_date,
           t.amount,
           t.amount
             * CASE WHEN t.currency = 'USD' THEN 1 ELSE c.sellingrate END
             AS usd_amount,
           SUM(
             t.amount
             * CASE WHEN t.currency = 'USD' THEN 1 ELSE c.sellingrate END
           ) OVER (
             PARTITION BY t.sender_name
             ORDER BY t.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 t
           LEFT OUTER JOIN currencyRate c
           ON (t.currency = c.currencyName)
  )
)
WHERE  has_weekly_total_over_1000 > 0;


ID
SENDER_NAME
TRANSACTION_DATE
AMOUNT
USD_AMOUNT


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

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

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

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

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

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

ID	SENDER_NAME	TRANSACTION_DATE	AMOUNT	USD_AMOUNT
1111	Alice	2023-01-03 01:26:29.000000000	700	700
2222	Alice	2023-01-05 04:26:29.000000000	450	490.5
3333	Alice	2023-01-08 18:26:29.000000000	1000	1090
4444	Alice	2023-01-09 04:26:29.000000000	200	250
4567	Carol	2023-09-11 08:26:29.000000000	501	501
2345	Carol	2023-09-17 04:26:29.000000000	1001	600.6