Trades Flagged 🌱 - Allium Documentation Hub

Table Details

Property	Value
Table Name	`robinhood.dex.trades_flagged`
Table Status	Beta 🌱
Unique Key	`transaction_hash`, `unique_id`
Clustering Key(s)	`block_timestamp::date`

Trades from robinhood.dex.trades flagged as wash or otherwise inorganic, one row per trade. Use it to see what was flagged and why, or to join flags onto your own queries.

Flagged trades are not removed from robinhood.dex.trades. For every trade with flags already joined, use robinhood.dex.trades_enriched.

flagged_details holds one object per trade. reason is the highest-priority match, source is what matched it, and reasons lists every match in priority order.

`reason`	Source	Meaning
`roundtrip_wash`	Registry (tx hash, tx sender or called contract)	The transaction buys and sells through the same pool within itself, printing volume with no net position.
`same_pool_roundtrip_v1`	`heuristic`	The transaction trades one pool both ways and its flow cancels out.
`supply_shock_v1`	`heuristic`	The trade touches a token flagged for a supply shock.
`self_issued_v1`	`heuristic`	The trade touches a token flagged as self-issued by its creator.

Sample Queries

Flagged volume by day and reason:

select
    f.block_timestamp::date as date,
    f.flagged_details:reason::varchar as flag_reason,
    count(*) as trades,
    sum(t.usd_amount) as flagged_volume_usd
from robinhood.dex.trades_flagged f
join robinhood.dex.trades t
    on t.block_timestamp = f.block_timestamp
    and t.unique_id = f.unique_id
group by 1, 2
order by 1

Methodology

Table Columns

Column Name	Data Type	Description
block_timestamp	TIMESTAMP_NTZ(9)	Block timestamp of the swap event.
block_number	NUMBER(38,0)	Block number of the swap event.
transaction_hash	VARCHAR(66)	Transaction hash that this swap belongs to.
transaction_from_address	VARCHAR(42)	Transaction sender address. I.e. the address of the transaction initiator. (from_address in the raw.transactions field for the transaction_hash of this swap).
project	VARCHAR(16777216)	The project (decentralized exchange) of the liquidity pool that the swap occurred from.
protocol	VARCHAR(16777216)	DEX protocol (& version, if applicable) of the contract address facilitating the swap.
liquidity_pool_address	VARCHAR(42)	Contract address of the liquidity pool holding the asset. For protocol without the concept of LP such as airswap, this will be null.
flagged_details	VARIANT	Why the trade is flagged, as one object. `reason`: the highest-priority match. `source`: what matched it, a registry key (`transaction_hash`, `transaction_from_address`, `transaction_to_address`) or `heuristic`. `flagged_at`: the registry entry’s date, or the date a heuristic first flagged the trade; it is not the date the change reached production. `reasons`: every match, in priority order. Each reason key holds a list of its matches, each with its own `flagged_at`. `supply_shock_v1` / `self_issued_v1` = the trade touches a flagged token: `token_address`, `token_flagged_at`, `flagged_value` (units over pre-shock supply, or the creator’s share of legs). `same_pool_roundtrip_v1` = the transaction trades one pool both ways and its flow cancels out: `pool_key`, `score` (1 = perfect round trip), `has_liquidity_event`. `roundtrip_wash` (registry): `source`, `value`. Examples: `flagged_details:reason::varchar`; one rule: `array_contains('same_pool_roundtrip_v1'::variant, flagged_details:reasons)`.
_created_at	TIMESTAMP_NTZ(9)	Timestamp of when the entry was created in the database.
_updated_at	TIMESTAMP_NTZ(9)	Timestamp of when the entry was last updated in the database.
unique_id	VARCHAR(16777216)	Unique ID of each trade.

EVM DEX Trades Flagged

EVM

​Table Details

​Sample Queries

​Methodology

​Table Columns

​Related Docs

Table Details

Sample Queries

Methodology

Table Columns

Related Docs