Outline
Mental Model of Sui Objects
Querying USDC Coin Transfers on Dune
Transfer Heuristics
Query Optimizations
This post shows how to calculate USDC transfers on the Sui blockchain using Dune’s Trino SQL engine. Sui is object-centric - meaning that every object has their own lifecycle, ownership history, versioning, and special object type data. This means that coins and addresses on Sui are also Move objects. To calculate balance transfers, we must compare the state of multiple coin objects, not from a single account balance like on EVM chains.
Objects, not accounts. Every coin is an object with an object_id, a version that increases with each mutation, an owner, and a coin type (e.g., 0xdba34672e30cb065b1f93e3ab55318768fd6fef66c15942c9f7cb846e2f900e7::usdc::USDC).
Ownership forms. Address-owned, object-owned, shared, or immutable. A wallet can hold many coin objects for the same token.
Causality via transactions. Each new object version is produced by a specific transaction digest (previous transaction).
Practical implication: to compute “transfers,” we trace how coin objects’ ownership and balances change over time, then aggregate the positive inflows to receivers.
Below is a query example to calculate USDC transfers for the first month of the coin’s creation. The dune link can be found here.
The query reconstructs USDC transfers directly from object-state changes rather than relying on emitted events. Each time a new coin object is created, its full coin_balance represents an inflow to the receiver. When an existing object is mutated, the query computes the positive delta between its current and previous balances (max(new − old, 0)), which captures additional inflows such as received transfers or merged coins. To make these deltas accurate without scanning the entire historical table, the query introduces an anchor—the most recent pre-window version of every object—so that LAG(prev_balance) comparisons are local to the analysis range. Finally, by filtering both sui.objects and sui.transactions on their date partitions, Dune’s engine can prune away irrelevant partitions and limit I/O to only the time window being studied, significantly reducing scan cost.
Sui’s object model doesn’t emit standardized “transfer” events like ERC-20 tokens on Ethereum. Instead, value movement must be inferred from how coin objects change owners or balances over time. To reconstruct these movements, the query aggregates all positive inflows on the receiver side of each transaction. This approach is designed to capture most genuine transfers with high recall—if someone truly receives tokens, that inflow will appear as a Created or Mutated coin object with a positive delta. As a result, false negatives are rare: real peer-to-peer (P2P) transfers almost always surface correctly.
However, there is a risk for false positives. Any DeFi applications—AMMs, lending protocols, routers, and bridges—mutate coin objects internally as part of swaps, liquidity operations, or accounting adjustments. These actions also create positive deltas that can look indistinguishable from user-to-user transfers when viewed at the object level.
To control for this, the query introduces a simple but effective classifier: a transaction is labeled a transfer only when it has a single receiver (receiver_cnt = 1). Transactions with multiple receivers are more likely to represent complex DeFi interactions and are therefore classified as “other.”
In practice, this “simple transfer” heuristic serves as a pragmatic first layer of classification: it cleanly isolates high-confidence P2P flows while leaving room for later enrichment into categories like “likely P2P,” “app-mediated,” or “internal protocol movement.” This balance between simplicity and interpretability makes it a good starting point for mapping economic flows in an object-centric system like Sui.
This query is deceptively heavy. Complexity comes from logical SQL and physical layout.
Window functions (LAG) over (PARTITION BY object_id ORDER BY version) require Trino to sort and buffer complete per-object histories that land in the window.
Union of anchors + in-window rows increases input size and sustained memory pressure.
Post-window aggregates mean large intermediate results must be shuffled/aggregated after the sort phases.
Joins to transactions add cross-stage shuffles if filters aren’t aligned.
Result: multi-stage distributed execution with large network shuffles and potential spills when memory is tight.
On Dune, tables are clustered/partitioned by a date column, not by coin_type or object_id.
Implications:
Partition pruning works only along date. The WHERE clause should include a filter on the date partition to avoid scanning every partition in the chosen period.
Filtering by coin_type or grouping by object_id does not reduce partitions scanned; it only filters within each touched partition.
Wrapping a partition column in functions may invalidate partition pruning efficiencies
Takeaway: runtime scales roughly with both the number of active coin objects and the number of date partitions touched.
WITH params AS (
SELECT
CAST('0xdba34672e30cb065b1f93e3ab55318768fd6fef66c15942c9f7cb846e2f900e7::usdc::USDC' AS VARCHAR) AS usdc,
DATE '2024-09-01' AS start_date,
DATE '2024-10-10' AS end_date
),
day_rows AS (
SELECT
o.object_id,
o.version,
o.previous_transaction AS tx_digest,
o.timestamp_ms,
o.owner_address AS receiver,
o.coin_type,
o.object_status,
TRY_CAST(o.coin_balance AS BIGINT) AS coin_balance
FROM sui.objects o
CROSS JOIN params p
WHERE o.object_status IN ('Created', 'Mutated')
AND o.coin_type = p.usdc
-- ✅ partition pruning on objects
AND o.date >= p.start_date
AND o.date < p.end_date
),
anchors AS (
SELECT
p.object_id,
MAX(p.version) AS version,
CAST(NULL AS VARCHAR) AS tx_digest,
max_by(p.timestamp_ms, p.version) AS timestamp_ms,
max_by(p.owner_address, p.version) AS receiver,
p.coin_type,
CAST('ANCHOR' AS VARCHAR) AS object_status,
max_by(TRY_CAST(p.coin_balance AS BIGINT), p.version) AS coin_balance
FROM sui.objects p
CROSS JOIN params pr
WHERE p.coin_type = pr.usdc
-- ✅ prune to pre-window partitions only
AND p.date < pr.start_date
-- only anchor for objects that appear in the main window
AND p.object_id IN (SELECT DISTINCT object_id FROM day_rows)
GROUP BY p.object_id, p.coin_type
),
unioned AS (
SELECT object_id, version, tx_digest, timestamp_ms, receiver, coin_type, object_status, coin_balance FROM anchors
UNION ALL
SELECT object_id, version, tx_digest, timestamp_ms, receiver, coin_type, object_status, coin_balance FROM day_rows
),
calc AS (
SELECT
u.object_id,
u.version,
u.tx_digest,
u.timestamp_ms,
u.receiver,
u.coin_type,
u.object_status,
u.coin_balance,
LAG(u.receiver) OVER (PARTITION BY u.object_id ORDER BY u.version) AS prev_owner,
LAG(u.coin_balance) OVER (PARTITION BY u.object_id ORDER BY u.version) AS prev_balance
FROM unioned u
),
-- Prefilter transactions by partition, then join (avoids scanning all tx partitions)
tx_window AS (
SELECT tx.*
FROM sui.transactions tx
CROSS JOIN params p
WHERE tx.date >= p.start_date
AND tx.date < p.end_date
),
calc_with_tx AS (
SELECT c.*, tx.sender AS tx_sender
FROM calc c
LEFT JOIN tx_window tx
ON c.tx_digest = tx.transaction_digest
),
outs AS (
SELECT
tx_digest,
receiver,
coin_type,
SUM(
CASE
WHEN object_status = 'Created' THEN coin_balance
WHEN object_status = 'Mutated' THEN greatest(coin_balance - COALESCE(prev_balance, 0), 0)
ELSE 0
END
) AS amount_raw
FROM calc
WHERE (object_status = 'Created' AND coin_balance > 0)
OR (object_status = 'Mutated' AND (coin_balance - COALESCE(prev_balance, 0)) > 0)
GROUP BY tx_digest, receiver, coin_type
),
tx_stats AS (
SELECT tx_digest, COUNT() AS receiver_cnt
FROM outs
GROUP BY tx_digest
)
SELECT
c.object_id,
c.version,
c.tx_digest,
c.timestamp_ms,
c.receiver,
CASE WHEN c.object_status = 'Created' THEN c.tx_sender ELSE c.prev_owner END AS sender,
c.coin_type,
c.object_status,
c.coin_balance,
c.prev_balance,
CASE
WHEN c.object_status = 'Created' THEN c.coin_balance
ELSE greatest(c.coin_balance - COALESCE(c.prev_balance, 0), 0)
END AS effective_amount,
CASE WHEN ts.receiver_cnt = 1 THEN 'transfer' ELSE 'other' END AS classification,
CASE WHEN ts.receiver_cnt = 1 THEN TRUE ELSE FALSE END AS is_transfer
FROM calc_with_tx c
LEFT JOIN tx_stats ts
ON c.tx_digest = ts.tx_digest
WHERE (c.object_status = 'Created' AND c.coin_balance > 0)
OR (c.object_status = 'Mutated' AND (c.coin_balance - COALESCE(c.prev_balance, 0)) > 0);
Share Dialog
No comments yet