I am trying to find cust_id who made transactions larger than 2 millions within 30 days.

Days are calculated by DATE column.

cust_id ABCD BUY AAA in 2021/10/01 and sell in 2021/10/05, within 4 days, the amt is larger than 2 millions,this is what i want.

cust_id QRST BUY AAA in 2021/10/07 with 900,000 and 100, then sell in 2021/10/07 with 100, within 1 days, but the amt is less than 2 millions, not my result

cust_id ZZYY BUY CCC with 1,000,000 in 2021/10/08 and sell with 1,000,000 in 2021/12/07, amt larger than 2,000,000 but buy date to sell date is longer than 30 days, not my result

what i want is

CodePudding user response：

From Oracle 12, you can find values that are within a 30-day rolling window using MATCH_RECOGNIZE:

SELECT cust_id,
       amt,
       "DATE",
       prd_id,
       tran,
       match_number
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY cust_id, prd_id
  ORDER     BY "DATE"
  MEASURES
    MATCH_NUMBER() AS match_number
  ALL ROWS PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN (within_30_days* over_2m)
  DEFINE
    within_30_days AS "DATE" <= FIRST("DATE")   INTERVAL '30' DAY,
    over_2m        AS "DATE" <= FIRST("DATE")   INTERVAL '30' DAY
                      AND SUM(amt) >= 2000000
)

Which, for the sample data:

CREATE TABLE table_name (CUST_ID, AMT, "DATE", PRD_ID, TRAN) AS
SELECT 'ABCD', 1000000, DATE '2021-10-01', 'AAA', 'BUY'  FROM DUAL UNION ALL
SELECT 'ABCD', 1000000, DATE '2021-10-05', 'AAA', 'SELL' FROM DUAL UNION ALL
SELECT 'ABCD', 1000000, DATE '2021-10-06', 'BBB', 'BUY'  FROM DUAL UNION ALL
SELECT 'QRST',  900000, DATE '2021-10-07', 'AAA', 'BUY'  FROM DUAL UNION ALL
SELECT 'QRST',     100, DATE '2021-10-07', 'AAA', 'BUY'  FROM DUAL UNION ALL
SELECT 'QRST',     100, DATE '2021-10-07', 'AAA', 'SELL' FROM DUAL UNION ALL
SELECT 'ZZYY', 1000000, DATE '2021-10-08', 'CCC', 'BUY'  FROM DUAL UNION ALL
SELECT 'ZZYY', 1000000, DATE '2021-12-07', 'CCC', 'SELL' FROM DUAL;

Outputs:

CUST_ID	AMT	DATE	PRD_ID	TRAN	MATCH_NUMBER
ABCD	1000000	01-OCT-21	AAA	BUY	1
ABCD	1000000	05-OCT-21	AAA	SELL	1

db<>fiddle here

CodePudding user response：

You updated the question later. Hence, I am answering again based upon more information.

WITH buy_date AS (
    SELECT
        b.cust_id,
        b.date,
        b.prd_id
    FROM
        table_name b
    WHERE
            b.tran = 'BUY'
        AND b.amt > 2000000
), sell_date AS (
    SELECT
        s.cust_id,
        s.date,
        s.prd_id
    FROM
        table_name s
    WHERE
            s.tran = 'SELL'
        AND s.amt > 2000000
)
SELECT
    *
FROM
    buy_date   bd,
    sell_date  sd
WHERE
        bd.cust_id = sd.cust_id
    AND bd.prd_id = sd.prd_id
    AND sd.date - bd.date <= 30;