I have this Transactions table in Oracle 11G. Please, don't judge my asking question.
Table has columns:
id (guid type)
sender_name
transaction_date
amount
sender_address
sender_phone
receiver_name
Format of transaction_date column is like 2014-11-30 04:26:29.000000.
What I want is: to display all data from Transactions table where those sender_name who sent more than 1000 amount (sum(amount)) on 5 consecutive days in given transaction_date between 01.01.2023 to 01.10.2023.
Even if, for example sender_name sent money on April month 5 consecutive days and on June also have sent 5 consecutive days display all.
Could someone tell how to do this, please?
Sample data:
CREATE TABLE transactions (id, sender_name, transaction_date, amount) AS
SELECT '1111', 'Alice', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '2222', 'Alice', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '3333', 'Alice', TIMESTAMP '2014-11-28 03:26:29', 1001 FROM DUAL UNION ALL
SELECT '4444', 'Alice', TIMESTAMP '2014-11-29 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '5555', 'Alice', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '6666', 'Betty', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '7777', 'Betty', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '9999', 'Carol', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '8888', 'Carol', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '1234', 'Carol', TIMESTAMP '2014-11-28 03:26:29', 500 FROM DUAL UNION ALL
SELECT '4567', 'Carol', TIMESTAMP '2014-11-28 08:26:29', 501 FROM DUAL UNION ALL
SELECT '2345', 'Carol', TIMESTAMP '2014-11-29 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '3456', 'Carol', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '5678', 'Carol', TIMESTAMP '2014-12-01 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '9999', 'Debra', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '8888', 'Debra', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '1234', 'Debra', TIMESTAMP '2014-11-28 03:26:29', 1001 FROM DUAL UNION ALL
SELECT '4567', 'Debra', TIMESTAMP '2014-11-28 08:26:29', 1001 FROM DUAL UNION ALL
SELECT '2345', 'Debra', TIMESTAMP '2014-11-30 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '3456', 'Debra', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '5678', 'Debra', TIMESTAMP '2014-12-01 05:26:29', 1001 FROM DUAL;
The result I want all datas of Alice-s rows. Because of total amount each they sent more than 10000 on 5 consecutive days. If I change 5 consecutive days to more than 5, than display Carol-s rows.
Since you can have multiple transactions occurring on a single day then you can use the DENSE_RANK analytic function over the days for each sender_name to find the groups of consecutive days. Then for each group you can find the start and end days and filter to only those rows where there are 5 or more days:
SELECT id,
sender_name,
transaction_date,
amount
FROM (
SELECT id,
sender_name,
transaction_date,
amount,
MAX(TRUNC(transaction_date)) OVER (PARTITION BY sender_name, grp)
- MIN(TRUNC(transaction_date)) OVER (PARTITION BY sender_name, grp)
+ 1
AS num_days
FROM (
SELECT id,
sender_name,
transaction_date,
amount,
TRUNC(transaction_date)
- DENSE_RANK() OVER (
PARTITION BY sender_name ORDER BY TRUNC(transaction_date)
) AS grp
FROM (
SELECT id,
sender_name,
transaction_date,
amount,
SUM(amount) OVER (
PARTITION BY sender_name, TRUNC(transaction_date)
) AS daily_amount
FROM transactions
)
WHERE daily_amount > 1000
)
)
WHERE num_days >= 5
ORDER BY sender_name, transaction_date;
Which, for the sample data:
CREATE TABLE transactions (id, sender_name, transaction_date, amount) AS
SELECT '1111', 'Alice', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '2222', 'Alice', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '3333', 'Alice', TIMESTAMP '2014-11-28 03:26:29', 1001 FROM DUAL UNION ALL
SELECT '4444', 'Alice', TIMESTAMP '2014-11-29 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '5555', 'Alice', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '6666', 'Betty', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '7777', 'Betty', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '9999', 'Carol', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '8888', 'Carol', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '1234', 'Carol', TIMESTAMP '2014-11-28 03:26:29', 500 FROM DUAL UNION ALL
SELECT '4567', 'Carol', TIMESTAMP '2014-11-28 08:26:29', 501 FROM DUAL UNION ALL
SELECT '2345', 'Carol', TIMESTAMP '2014-11-29 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '3456', 'Carol', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '5678', 'Carol', TIMESTAMP '2014-12-01 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '9999', 'Debra', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '8888', 'Debra', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '1234', 'Debra', TIMESTAMP '2014-11-28 03:26:29', 1001 FROM DUAL UNION ALL
SELECT '4567', 'Debra', TIMESTAMP '2014-11-28 08:26:29', 1001 FROM DUAL UNION ALL
SELECT '2345', 'Debra', TIMESTAMP '2014-11-30 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '3456', 'Debra', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '5678', 'Debra', TIMESTAMP '2014-12-01 05:26:29', 1001 FROM DUAL;
Outputs:
| ID | SENDER_NAME | TRANSACTION_DATE | AMOUNT |
|---|---|---|---|
| 1111 | Alice | 2014-11-26 01:26:29.000000000 | 1001 |
| 2222 | Alice | 2014-11-27 02:26:29.000000000 | 1001 |
| 3333 | Alice | 2014-11-28 03:26:29.000000000 | 1001 |
| 4444 | Alice | 2014-11-29 04:26:29.000000000 | 1001 |
| 5555 | Alice | 2014-11-30 05:26:29.000000000 | 1001 |
| 9999 | Carol | 2014-11-26 01:26:29.000000000 | 1001 |
| 8888 | Carol | 2014-11-27 02:26:29.000000000 | 1001 |
| 1234 | Carol | 2014-11-28 03:26:29.000000000 | 500 |
| 4567 | Carol | 2014-11-28 08:26:29.000000000 | 501 |
| 2345 | Carol | 2014-11-29 04:26:29.000000000 | 1001 |
| 3456 | Carol | 2014-11-30 05:26:29.000000000 | 1001 |
| 5678 | Carol | 2014-12-01 05:26:29.000000000 | 1001 |
If you were using Oracle 12 or later then you can greatly simplify the solution by using MATCH_RECOGNIZE:
SELECT *
FROM (
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)
) AS daily_amount
FROM transactions
)
WHERE daily_amount > 1000
)
MATCH_RECOGNIZE (
PARTITION BY sender_name
ORDER BY transaction_date
ALL ROWS PER MATCH
PATTERN (consecutive_days+ last_day)
DEFINE
consecutive_days AS NEXT(transaction_date) < TRUNC(transaction_date) + 2,
last_day AS FIRST(TRUNC(transaction_date)) + 4 <= transaction_date
)