Oracle SQL count of unique IDs in moving time window-CodePudding

I need to query data on a large transaction dataset in a Oracle SQL Developer database but unfortunately I didn't came up with the correct solution yet - hopefully someone can help me.

The dataset consists of the following: Customer_ID, Counterparty_ID, Transaction ID, Transaction_Amount, Date

Task : Flag the transactions of a customer, when a customers made transactions (>= 1000 € each) with >= 5 different counterparties in a 7day time window.

The time window should be a moving time window: E.g. if the transaction date is 17.6. the time window would be - 6 days (11. - 23.06.). Within a time window only distinct counterparties should be counted. E.g. if a customer has made 5 transactions with counterparty X in time window A, it is counted as 1. If the customer made additional transactions with counterparty X but in time window B, it is again counted as 1 for that time window.

So far I was only able to solve the task with calendar weeks as time window but that is not as intended.

CodePudding user response：

You haven't provided any sample data or expected output so its difficult to know exactly what you want to output; however, you can immediately filter out all the rows with transaction_amount < 1000 and then, from Oracle 12, you use MATCH_RECOGNIZE to perform row-by-row matching:

SELECT *
FROM   (
  SELECT m.*,
         COUNT(DISTINCT counterparty_id) OVER (
           PARTITION BY customer_id, match
         ) AS num_counterparties
  FROM   (
    SELECT *
    FROM   table_name
    WHERE  transaction_amount >= 1000
  )
  MATCH_RECOGNIZE (
    PARTITION BY customer_id
    ORDER BY "DATE"
    MEASURES
      MATCH_NUMBER() AS match
    ALL ROWS PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN ( within_week  )
    DEFINE
      within_week AS "DATE" < FIRST("DATE")   INTERVAL '7' DAY
  ) m
)
WHERE  num_counterparties >= 5;

Which, for the sample data:

CREATE TABLE table_name (
  Customer_ID, Counterparty_ID, Transaction_ID, Transaction_Amount, "DATE"
) AS
SELECT 1, 1, 1, 1000, DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 1, 2, 2, 1000, DATE '1970-01-02' FROM DUAL UNION ALL
SELECT 1, 3, 3, 1000, DATE '1970-01-03' FROM DUAL UNION ALL
SELECT 1, 4, 4, 1000, DATE '1970-01-04' FROM DUAL UNION ALL
SELECT 1, 5, 5, 1000, DATE '1970-01-05' FROM DUAL;

Outputs:

CUSTOMER_ID DATE MATCH COUNTERPARTY_ID TRANSACTION_ID TRANSACTION_AMOUNT NUM_COUNTERPARTIES

1 01-JAN-70 1 1 1 1000 5

1 02-JAN-70 1 2 2 1000 5

1 03-JAN-70 1 3 3 1000 5

1 04-JAN-70 1 4 4 1000 5

1 05-JAN-70 1 5 5 1000 5

CUSTOMER_ID	DATE	MATCH	COUNTERPARTY_ID	TRANSACTION_ID	TRANSACTION_AMOUNT	NUM_COUNTERPARTIES
1	01-JAN-70	1	1	1	1000	5
1	02-JAN-70	1	2	2	1000	5
1	03-JAN-70	1	3	3	1000	5
1	04-JAN-70	1	4	4	1000	5
1	05-JAN-70	1	5	5	1000	5

db<>fiddle here

CodePudding user response：

Wow that was quick, thanks a lot! At first glance it looks like intended but I'll validate it on Monday. In case there is anything missing, I'll provide some sample data or maybe I can solve it by myself with your example. Thanks & have a nice weekend!!!