final_rev_tax_usd_cents.sql

/*
CREATE TABLE transactions (
    txn_id      VARCHAR(20)  NOT NULL,
    user_id     VARCHAR(20)  NOT NULL,
    item        VARCHAR(10)  NOT NULL,
    amount      DECIMAL(12,2) NOT NULL,
    tax_amount  VARCHAR(20)   NOT NULL,   -- keep original formatting ($, €, spaces)
    currency    VARCHAR(5)    NOT NULL,
    ts          TIMESTAMP     NOT NULL,   -- store in UTC
    system      CHAR(1)       NOT NULL,   -- 'A' or 'B'
    status      VARCHAR(20)   NOT NULL
);

INSERT INTO transactions
(txn_id, user_id, item, amount, tax_amount, currency, ts, system, status)
VALUES
('T1','U1','REV',120.00,'  $9.00','USD','2025-09-20T10:00:00Z','A','APPROVED'),
('T1','U1','REV',120.00,'   9.00 ','USD','2025-09-20T10:05:00Z','B','APPROVED'),
('T2','U2','FEE', 30.00,' $0.50','USD','2025-09-20T10:30:00Z','A','APPROVED'),
('T3','U1','REV', 90.00,'€10.00','EUR','2025-09-20T11:00:00Z','A','APPROVED'),
('T3','U1','REV', 90.00,'  10.00 ','EUR','2025-09-20T11:02:00Z','B','APPROVED'),
('T4','U3','REV',  0.00,'$0.00','USD','2025-09-20T12:00:00Z','B','APPROVED'),
('T5','U2','REV',100.00,'  $8.00','USD','2025-09-20T13:00:00Z','A','VOID'),
('T6','U2','REV', 99.00,'$7.50','USD','2025-09-20T14:00:00Z','A','APPROVED'),
('T6','U2','REV', 99.00,'$7.80','USD','2025-09-20T14:00:03Z','A','APPROVED');


| txn_id | user_id | ts                   | tax_cents |
| ------ | ------- | -------------------- | --------- |
| T1     | U1      | 2025-09-20T10:05:00Z | 900       |
| T3     | U1      | 2025-09-20T11:02:00Z | 1100      |
| T6     | U2      | 2025-09-20T14:00:03Z | 780       |

Assumptions you can state to the candidate:
Finance only cares about item='REV' and status='APPROVED'.
Prefer System B over A for duplicate txn_id.
Within a system, keep the latest ts per txn_id.

Normalize tax_amount to USD cents (integer): strip $, €, spaces, commas; EUR→USD using 1.1; drop zero/negative.
*/

-- Without Window Functions GROUP BY and JOINs only

WITH rev_only AS (
    SELECT
        txn_id,
        user_id,
        system,
        ts,
        TRIM(REPLACE(REPLACE(REPLACE(tax_amount,'$',''),'€',''),' ','')) AS raw_tax,
        currency
    FROM transactions
    WHERE item = 'REV'
      AND status = 'APPROVED'
),

-- get latest ts per txn_id + system
latest_ts AS (
    SELECT txn_id, system, MAX(ts) AS max_ts
    FROM rev_only
    GROUP BY txn_id, system
),

-- join back to get the corresponding row
latest_rows AS (
    SELECT r.*
    FROM rev_only r
    JOIN latest_ts lt
      ON r.txn_id = lt.txn_id
     AND r.system = lt.system
     AND r.ts = lt.max_ts
),

-- prefer system B if exists else A
preferred AS (
    SELECT l.*
    FROM latest_rows l
    LEFT JOIN latest_rows b
      ON l.txn_id = b.txn_id
     AND b.system = 'B'
    WHERE
        (l.system = 'B')
        OR (b.txn_id IS NULL AND l.system = 'A')
),

normalized AS (
    SELECT
        txn_id,
        user_id,
        ts,
        CAST(
            ROUND(
                CASE WHEN currency='EUR'
                     THEN CAST(raw_tax AS numeric)*1.1
                     ELSE CAST(raw_tax AS numeric)
                END * 100
            ) AS integer
        ) AS tax_cents
    FROM preferred
)
SELECT txn_id, user_id, ts, tax_cents
FROM normalized
WHERE tax_cents > 0;


-- With Window Functions

WITH rev_only AS (
    SELECT
        txn_id,
        user_id,
        system,
        ts,
        TRIM(REPLACE(REPLACE(REPLACE(tax_amount,'$',''),'€',''),' ','')) AS raw_tax,
        currency
    FROM transactions
    WHERE item = 'REV'
      AND status = 'APPROVED'
),
latest_per_system AS (
    SELECT *
    FROM (
        SELECT r.*,
               ROW_NUMBER() OVER (
                   PARTITION BY txn_id, system
                   ORDER BY ts DESC
               ) AS rn
        FROM rev_only r
    ) t
    WHERE rn = 1
),
prefer_b AS (
    SELECT *
    FROM (
        SELECT l.*,
               ROW_NUMBER() OVER (
                   PARTITION BY txn_id
                   ORDER BY CASE WHEN system='B' THEN 1 ELSE 2 END
               ) AS rn
        FROM latest_per_system l
    ) t
    WHERE rn = 1
),
normalized AS (
    SELECT
        txn_id,
        user_id,
        ts,
        CAST(
            ROUND(
                CASE WHEN currency='EUR'
                     THEN CAST(raw_tax AS numeric)*1.1
                     ELSE CAST(raw_tax AS numeric)
                END * 100
            ) AS integer
        ) AS tax_cents
    FROM prefer_b
)
SELECT txn_id, user_id, ts, tax_cents
FROM normalized
WHERE tax_cents > 0;