Oracle 11g. Output data within seven days

There is Transactions table in Oracle 11g, like example:

CREATE TABLE transactions (id, sender_name, transaction_date, amount) AS
SELECT '1111', 'Alice', TIMESTAMP '2023-01-03 01:26:29', 700 FROM DUAL UNION ALL
SELECT '2222', 'Alice', TIMESTAMP '2023-01-05 04:26:29', 450 FROM DUAL UNION ALL
SELECT '3333', 'Alice', TIMESTAMP '2023-01-08 18:26:29', 1000 FROM DUAL UNION ALL
SELECT '4444', 'Alice', TIMESTAMP '2023-01-09 04:26:29', 200 FROM DUAL UNION ALL

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

I apologize for this, but I need two options to output data :)))

Option: To display transactions of client (sender_name) with total amount sent money more than (amount) > 1000 within seven days after the first transaction_date. In this case, the result should be: all Alices transactions because the first payment is 03.01 and within seven days until 09.01 and her transactions total amount during these days is more than 1000. And all Bettys transactions also have the same logic.For Carol transactions 10.09, 11.09 and 17.09

2.Option: Is it possible to determine the beginning of the week (starting from Monday) and display client transactions carried out during the week (from Monday to Sunday) with a total amount of more than 1000 (total amount during week)? If yes, than how can be done.

Solution

For Option 1, you can use analytic functions with a range window to first total the amounts for the past 7 days and then, in a second pass, to count how many rows in the future week have a previously calculated total over 1000 and filter to only those rows:

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;

Which, for the sample data, outputs:

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

Option 2, is simpler as you just need to use an analytic function and aggregate by the sender and the ISO week:

SELECT id,
       sender_name,
       transaction_date,
       amount
FROM   (
  SELECT id,
         sender_name,
         transaction_date,
         amount,
         SUM(amount) OVER (PARTITION BY sender_name, TRUNC(transaction_date, 'IW'))
           AS weekly_amount
  FROM   transactions
)
WHERE  weekly_amount > 1000;

Which, for the sample data, outputs:

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
4567	Carol	2023-09-11 08:26:29.000000000	501
2345	Carol	2023-09-17 04:26:29.000000000	1001

fiddle