1WITH
2  rlusd_balance_changes AS (
3    SELECT
4      account AS buyer_wallet,
5      ledger_close_time,
6      transaction_type,
7      -- Extract RLUSD balance changes from ModifiedNode FinalFields
8      json_extract_string(
9        metadata,
10        '$.AffectedNodes[*].ModifiedNode.FinalFields.Balance.value'
11      ) AS final_balance,
12      json_extract_string(
13        metadata,
14        '$.AffectedNodes[*].ModifiedNode.PreviousFields.Balance.value'
15      ) AS previous_balance,
16      json_extract_string(metadata, '$.DeliveredAmount.value') AS delivered_amount,
17      json_extract_string(metadata, '$.DeliveredAmount.currency') AS delivered_currency
18    FROM
19      "@blt.xrp_ledger.transactions"
20    WHERE
21      ledger_close_time >= NOW() - INTERVAL 24 HOUR
22      AND LOWER(metadata) LIKE '%rlusd%'
23      AND transaction_type = 'Payment'
24  )
25SELECT
26  buyer_wallet,
27  COUNT(*) AS num_purchases,
28  SUM(CAST(delivered_amount AS DECIMAL(20, 8))) AS total_rlusd_purchased,
29  MAX(ledger_close_time) AS latest_purchase_time
30FROM
31  rlusd_balance_changes
32WHERE
33  delivered_currency = '524C555344000000000000000000000000000000'
34  AND delivered_amount IS NOT NULL
35GROUP BY
36  buyer_wallet
37ORDER BY
38  total_rlusd_purchased DESC
39LIMIT
40  20