Avoiding Double Counts in Blockchain SQL Joins
Join transactions, transfers and prices without multiplying rows, and verify aggregation grain before trusting wallet or market totals.
Overview
When translating these concepts into an integration, keep the scope described by the wallet history api with the result. The examples in wallet transaction history and smart contract event logs show how the same distinction affects neighbouring data questions.
A correct SQL query needs a declared row grain. Joining two one-to-many tables can multiply records and inflate sums even though every individual join condition appears reasonable.
Identify the grain of each table
Transactions, logs, expanded movements and price observations have different identities. A transaction can contain many transfers, while an asset can have many price observations. Aggregate or select the required record on each side before joining. DISTINCT is not a universal repair: it can remove legitimate equal-valued movements while leaving the underlying model wrong.
Worked example: two by three
One transaction has two transfer rows and three matching price rows. A join on asset alone produces six rows. Summing transfer quantities triples the movement total. First select the price observation under the declared timestamp rule, then join each movement to that single eligible observation or an explicit missing-price state.
Test cardinality before totals
Count rows and distinct evidence IDs before and after each join. Include multiple transfers of the same amount and missing prices. Keep price-selection logic visible in the query. A matched aggregate on one sample is not enough; offsetting errors can hide a bad join until a different wallet or period is analysed.
A practical acceptance test
Join a fixture with two movements and three price observations for the same asset. Assert the six-row expansion before applying the correct price-selection rule. The repaired query should retain two movement rows, including two legitimate equal amounts. This catches a misleading DISTINCT fix that happens to match a total while deleting genuine evidence.
Questions about the topic
Frequently asked questions
Does DISTINCT fix every duplicate join?
No. It can erase legitimate equal-valued records.
What should be checked before SUM?
The intended row grain and join cardinality.
Standards & technical documentation
