CREATE TABLE HTD (
    TRAN_DATE DATE,
    TRAN_ID VARCHAR2(9),
    PART_TRAN_SRL_NUM VARCHAR2(4),
    PART_TRAN_TYPE VARCHAR2(1),
    TRAN_AMT NUMBER(20,4),
    TRAN_PARTICULAR VARCHAR2(50)
);



-- 1. The Initial Debit (Large relative amount, but under 1000)
INSERT INTO HTD VALUES ('01-07-2026', 'TXN000001', '0001', 'D', 900.0000, 'Initial Account Activation Fee');

-- 2. 39 Credits Surpassing the Debit Amount
INSERT INTO HTD VALUES ('01-07-2026', 'TXN000002', '0001', 'C', 50.0000, 'Cashback Refund');
INSERT INTO HTD VALUES ('01-07-2026', 'TXN000003', '0001', 'C', 65.0000, 'User Reward Deposit');
INSERT INTO HTD VALUES ('01-07-2026', 'TXN000004', '0001', 'C', 80.0000, 'Small Group Contribution');
INSERT INTO HTD VALUES ('02-07-2026', 'TXN000005', '0001', 'C', 95.0000, 'Affiliate Commission Payout');
INSERT INTO HTD VALUES ('02-07-2026', 'TXN000006', '0001', 'C', 110.0000, 'Online Survey Credit');
INSERT INTO HTD VALUES ('02-07-2026', 'TXN000007', '0001', 'C', 50.0000, 'Micro-task Milestone Payment');
INSERT INTO HTD VALUES ('02-07-2026', 'TXN000008', '0001', 'C', 65.0000, 'App Referral Bonus');
INSERT INTO HTD VALUES ('03-07-2026', 'TXN000009', '0001', 'C', 80.0000, 'Interest Yield Credit');
INSERT INTO HTD VALUES ('03-07-2026', 'TXN000010', '0001', 'C', 95.0000, 'Digital Asset Royalty');
INSERT INTO HTD VALUES ('03-07-2026', 'TXN000011', '0001', 'C', 110.0000, 'Loyalty Points Conversion');
INSERT INTO HTD VALUES ('03-07-2026', 'TXN000012', '0001', 'C', 50.0000, 'Overcharge Correction');
INSERT INTO HTD VALUES ('04-07-2026', 'TXN000013', '0001', 'C', 65.0000, 'Vendor Discount Rebate');
INSERT INTO HTD VALUES ('04-07-2026', 'TXN000014', '0001', 'C', 80.0000, 'Peer Money Transfer A');
INSERT INTO HTD VALUES ('04-07-2026', 'TXN000015', '0001', 'C', 95.0000, 'Shared Bill Settlement');
INSERT INTO HTD VALUES ('05-07-2026', 'TXN000016', '0001', 'C', 110.0000, 'E-wallet Balance Sweep');
INSERT INTO HTD VALUES ('05-07-2026', 'TXN000017', '0001', 'C', 50.0000, 'Promo Code Inflow');
INSERT INTO HTD VALUES ('05-07-2026', 'TXN000018', '0001', 'C', 65.0000, 'Content Tip Received');
INSERT INTO HTD VALUES ('05-07-2026', 'TXN000019', '0001', 'C', 80.0000, 'Secondary Account Link Bonus');
INSERT INTO HTD VALUES ('06-07-2026', 'TXN000020', '0001', 'C', 95.0000, 'Automated Round-Up Credit');
INSERT INTO HTD VALUES ('06-07-2026', 'TXN000021', '0001', 'C', 110.0000, 'Community Token Swap');
INSERT INTO HTD VALUES ('06-07-2026', 'TXN000022', '0001', 'C', 50.0000, 'Beta Tester Incentive');
INSERT INTO HTD VALUES ('06-07-2026', 'TXN000023', '0001', 'C', 65.0000, 'Subscription Partial Refund');
INSERT INTO HTD VALUES ('07-07-2026', 'TXN000024', '0001', 'C', 80.0000, 'Micro-dividend Payout');
INSERT INTO HTD VALUES ('07-07-2026', 'TXN000025', '0001', 'C', 95.0000, 'Online Store Cashback');
INSERT INTO HTD VALUES ('07-07-2026', 'TXN000026', '0001', 'C', 110.0000, 'Marketplace Sale Proceed');
INSERT INTO HTD VALUES ('07-07-2026', 'TXN000027', '0001', 'C', 50.0000, 'Token Gas Fee Reimbursement');
INSERT INTO HTD VALUES ('08-07-2026', 'TXN000028', '0001', 'C', 65.0000, 'Freelance Micro-Gig Item A');
INSERT INTO HTD VALUES ('08-07-2026', 'TXN000029', '0001', 'C', 80.0000, 'Gift Card Residual Load');
INSERT INTO HTD VALUES ('08-07-2026', 'TXN000030', '0001', 'C', 95.0000, 'Streaming Platform Royalty');
INSERT INTO HTD VALUES ('08-07-2026', 'TXN000031', '0001', 'C', 110.0000, 'Peer Money Transfer B');
INSERT INTO HTD VALUES ('09-07-2026', 'TXN000032', '0001', 'C', 50.0000, 'Eco-incentive Credit');
INSERT INTO HTD VALUES ('09-07-2026', 'TXN000033', '0001', 'C', 65.0000, 'Recycling Depot Settlement');
INSERT INTO HTD VALUES ('09-07-2026', 'TXN000034', '0001', 'C', 80.0000, 'Bug Bounty Reward Part 1');
INSERT INTO HTD VALUES ('09-07-2026', 'TXN000035', '0001', 'C', 95.0000, 'Digital Art Pack Sale');
INSERT INTO HTD VALUES ('10-07-2026', 'TXN000036', '0001', 'C', 110.0000, 'Online Gaming Prize Pool');
INSERT INTO HTD VALUES ('10-07-2026', 'TXN000037', '0001', 'C', 50.0000, 'Survey Profile Update Bonus');
INSERT INTO HTD VALUES ('10-07-2026', 'TXN000038', '0001', 'C', 65.0000, 'Cloud Infrastructure Rebate');
INSERT INTO HTD VALUES ('10-07-2026', 'TXN000039', '0001', 'C', 80.0000, 'Social Media Creator Payout');
INSERT INTO HTD VALUES ('10-07-2026', 'TXN000040', '0001', 'C', 95.0000, 'Final Surplus Margin Inflow');



WITH DEBIT AS (
    SELECT TRAN_ID, TRAN_DATE, TRAN_AMT,PART_TRAN_SRL_NUM
    FROM   HTD
    WHERE  TRAN_ID   = 'TXN000001'
    AND    TRAN_DATE = '01-07-2026'
    AND    PART_TRAN_TYPE = 'D'
),
CREDITS AS (
    SELECT T.TRAN_ID,
           T.TRAN_DATE,
           T.TRAN_PARTICULAR,
           T.TRAN_AMT,
           SUM(T.TRAN_AMT) OVER (ORDER BY T.TRAN_DATE, T.TRAN_ID) AS RUNNING_SUM
    FROM   HTD T
    JOIN   DEBIT D ON T.PART_TRAN_SRL_NUM = D.PART_TRAN_SRL_NUM
    WHERE  T.PART_TRAN_TYPE = 'C'
    AND   (T.TRAN_DATE > D.TRAN_DATE
           OR (T.TRAN_DATE = D.TRAN_DATE AND T.TRAN_ID > D.TRAN_ID))
),
  SQUAREOFF AS (
    SELECT C.*, D.TRAN_AMT AS DEBIT_AMT, D.TRAN_DATE AS DEBIT_DATE
    FROM   CREDITS C
    CROSS JOIN DEBIT D
    WHERE  C.RUNNING_SUM - C.TRAN_AMT < D.TRAN_AMT
)