Convert Talend Studio jobs to BigQuery SQL

// Job: Customer_Revenue_Segmentation
// tMySQLInput_1: CUSTOMER_TRANSACTIONS
//   SELECT cust_id, amount, txn_date
//   FROM transactions WHERE amount > 1000
//
// tMap_1:
//   Main: row1 (transactions)
//   Lookup: row2 (customer_master)
//   Join: row1.cust_id = row2.cust_id
//   Expression: segment =
//     row1.total_spend > 50000 ? "Platinum"
//     : row1.total_spend > 10000 ? "Gold"
//     : "Standard"
//   Output: cust_id, segment, amount
//
// tAggregateRow_1:
//   GroupBy: segment
//   Sum: amount → segment_revenue
//   Count → customer_count
//
// tMySQLOutput_1: CUSTOMER_SEGMENTS

-- tMap + tAggregateRow → BigQuery SQL
WITH segmented AS (
    SELECT
        t.cust_id,
        CASE
            WHEN t.total_spend > 50000 THEN 'Platinum'
            WHEN t.total_spend > 10000 THEN 'Gold'
            ELSE 'Standard'
        END AS segment,
        t.amount
    FROM `project.dataset.customer_transactions` t
    INNER JOIN `project.dataset.customer_master` c
        ON t.cust_id = c.cust_id
    WHERE t.amount > 1000
),
agg AS (
    SELECT
        segment,
        SUM(amount) AS segment_revenue,
        COUNT(*) AS customer_count
    FROM segmented
    GROUP BY segment
)
MERGE `project.dataset.customer_segments` tgt
USING agg src
    ON tgt.segment = src.segment
WHEN MATCHED THEN UPDATE SET
    tgt.segment_revenue = src.segment_revenue,
    tgt.customer_count = src.customer_count
WHEN NOT MATCHED THEN INSERT
    (segment, segment_revenue, customer_count)
    VALUES (src.segment, src.segment_revenue,
            src.customer_count);