Open agaperste opened 4 months ago
is #5393 related?
if not, can you provide the query used to build odos trades?
@jeff-dude It is not related. Here is a fixed query which processes single token trades https://github.com/duneanalytics/spellbook/pull/5404 But we also want to process the SwapMulti events https://github.com/odos-xyz/odos-router-v2/blob/main/contracts/OdosRouterV2.sol#L74 along to the Swap events. Please let me know if you have further questions.
Here is a fixed query which processes single token trades #5404
approved this one, that looks good to me. i can merge if you want.
But we also want to process the SwapMulti events https://github.com/odos-xyz/odos-router-v2/blob/main/contracts/OdosRouterV2.sol#L74 along to the Swap events. Please let me know if you have further questions.
can you provide the example dune query which processes these multi-swap events?
Jeff @jeff-dude, thank you! Please merge this one. I will go ahead and add with the single Swap event processing for all Odos chains. And in parallel I'll work on the MultSwap event.
Hi @jeff-dude, here is the query and the result
WITH
zipped_input_tokens AS (
SELECT
evt_tx_hash,
evt_block_time,
t.inputAmount,
t2.inputToken,
erc20.symbol AS symbol,
COALESCE(
(t.inputAmount / power(10, erc20.decimals)), 0
) AS human_amount,
COALESCE(
(t.inputAmount / power(10, erc20.decimals)) * p.price, 0
) AS amount_usd
FROM
odos_v2_optimism.OdosRouterV2_evt_SwapMulti
CROSS JOIN UNNEST(amountsIn) WITH ORDINALITY as t(inputAmount, i)
CROSS JOIN UNNEST(
TRANSFORM( -- WETH
tokensIn, element -> IF(element = 0x0000000000000000000000000000000000000000, 0x4200000000000000000000000000000000000006, element)
)
) WITH ORDINALITY as t2(inputToken, i)
LEFT JOIN "delta_prod"."tokens"."erc20" erc20
ON erc20.contract_address = t2.inputToken
AND erc20.blockchain = 'optimism'
LEFT JOIN "delta_prod"."prices"."usd" p
ON p.minute = date_trunc('minute', evt_block_time)
AND p.contract_address = t2.inputToken
AND p.blockchain = 'optimism'
WHERE t.i = t2.i
),
concat_input_tokens AS (
SELECT
evt_tx_hash,
ARRAY_JOIN(ARRAY_AGG(symbol), ' + ') AS concat_input_tokens,
ARRAY_JOIN(ARRAY_AGG(human_amount), ', ') AS concat_input_human_amounts,
SUM(amount_usd) AS usd_input_amount
FROM zipped_input_tokens
GROUP BY evt_tx_hash
),
zipped_output_tokens AS (
SELECT
evt_tx_hash,
evt_block_time,
t3.outputAmount,
t4.outputToken,
erc20.symbol AS symbol,
COALESCE(
(t3.outputAmount / power(10, erc20.decimals)), 0
) AS human_amount,
COALESCE(
(t3.outputAmount / power(10, erc20.decimals)) * p.price, 0
) AS amount_usd
FROM
odos_v2_optimism.OdosRouterV2_evt_SwapMulti
CROSS JOIN UNNEST(amountsOut) WITH ORDINALITY as t3(outputAmount, i)
CROSS JOIN UNNEST(
TRANSFORM( -- WETH
tokensOut, element -> IF(element = 0x0000000000000000000000000000000000000000, 0x4200000000000000000000000000000000000006, element)
)
) WITH ORDINALITY AS t4(outputToken, i)
LEFT JOIN "delta_prod"."tokens"."erc20" erc20
ON erc20.contract_address = t4.outputToken
AND erc20.blockchain = 'optimism'
LEFT JOIN "delta_prod"."prices"."usd" p
ON p.minute = date_trunc('minute', evt_block_time)
AND p.contract_address = t4.outputToken
AND p.blockchain = 'optimism'
WHERE t3.i = t4.i
),
concat_output_tokens AS (
SELECT
evt_tx_hash,
ARRAY_JOIN(ARRAY_AGG(symbol), ' + ') AS concat_output_tokens,
ARRAY_JOIN(ARRAY_AGG(human_amount), ', ') AS concat_output_human_amounts,
SUM(amount_usd) AS usd_output_amount
FROM zipped_output_tokens
GROUP BY evt_tx_hash
)
SELECT concat_input_tokens, concat_output_tokens, usd_input_amount FROM
concat_input_tokens t1
LEFT JOIN concat_output_tokens t2
ON t1.evt_tx_hash = t2.evt_tx_hash
WHERE t1.evt_tx_hash = 0x6b129e9945914ae89038f317a18bdaa283eadc2144062a7243ce19a7de763ba7
Please merge this one.
this one is merged. i'm assuming the "single swap" event spells are intended to populate dex.trades
? or are they still considered dex_aggregator.trades
?
if dex.trades
, please note, we are migrating that entire spell lineage here -- linked directly to optimism spells for example. the readme
in root of that dex trades directory will help you contribute. i notice odos doesn't exist in that directory, which means it won't flow into dex.trades
once we finalize migration (soon)
@jeff-dude Thank you, Jeff!
I understand that dex.trades
refers to transactions executed directly on a DEX, where swaps occur. Odos, on the other hand, operates within the aggregation space, as it primarily routes orders to DEXs without directly executing the swaps itself.
@jeff-dude Any ideas on how we could put the multi token swaps into thedex_aggregator.trades
?
@jeff-dude Any ideas on how we could put the multi token swaps into the
dex_aggregator.trades
?
let me quickly take a look at your provided query
@jeff-dude Any ideas on how we could put the multi token swaps into the
dex_aggregator.trades
?
two quick thoughts:
in this existing spell, reading from different odos decoded table, i see a similar scenario where tokensIn
has an array data type and can be more than one token
select *
from odos_optimism.OdosRouter_evt_Swapped
where cardinality(tokensIn) > 1
limit 10
the logic in that spell simply pulls tokensIn[1] as token_sold_address
for instance -- is this overly simplified? are these the types of spells you're looking to fix?
i don't think the solution is to bring the values back into one row and concat. is the requirement to have one row per token in the tx? is it possible to somehow join to raw traces table and grab a trace_address
to use as a differentiator per row?
dex_aggregator.trades
table includes trace_address
as part of the unique keys, so it can help uniquely identify a rowi'm by no means an aggregator expert, so just trying to understand it all 😅
@jeff-dude
dex_aggregator.trades
table. How it can be done in this case?@jeff-dude
- Yes, this spell also needs a fix, because the Router V1 also supports multi-input and multi-output. Once we figure out how to do that, I'll fix that spell.
- I believe that it should be one row, because:
- It is one atomic trade
- There is no effective way to split up such trades (e.g. it might have 3 inputs and 2 outputs). That is why it should be one row in the
dex_aggregator.trades
table. How it can be done in this case?
condensing into one row breaks the table level of granularity. that introduces some risk relative to other projects that already build the table out.
i did some searching around. there are a few other contracts with event_name = 'SwapMulti
:
select
blockchain,
namespace,
count(1)
from
evms.logs_decoded
where
event_name = 'SwapMulti'
group by
blockchain,
namespace
however, none are used in spellbook at this time. we won't find spell examples for that exact event type.
i looked into a few dex project spells which populate dex_aggregator.trades
already. one project stuck out to me to have similar concept:
https://github.com/duneanalytics/spellbook/blob/main/models/bebop/ethereum/bebop_rfq_ethereum_trades.sql
i compiled that spell into a query, found an example tx_hash
which had either multiple tokens sold or bought in one event. for simplicity, i'll paste query here:
WITH
bebop_raw_data AS (
SELECT
call_block_time AS block_time,
call_block_number AS block_number,
call_tx_hash AS tx_hash,
evt_index,
ex.contract_address,
JSON_EXTRACT_SCALAR(ex."order", '$.expiry') AS expiry,
from_hex(
JSON_EXTRACT_SCALAR(ex."order", '$.taker_address')
) as taker_address,
from_hex(
JSON_EXTRACT_SCALAR(
JSON_EXTRACT(ex."order", '$.maker_addresses'),
'$[0]'
)
) AS maker_address,
JSON_EXTRACT(ex."order", '$.taker_tokens') AS taker_tokens_json,
JSON_EXTRACT(ex."order", '$.maker_tokens') AS maker_tokens_json,
JSON_EXTRACT(ex."order", '$.taker_amounts') AS taker_amounts_json,
JSON_EXTRACT(ex."order", '$.maker_amounts') AS maker_amounts_json,
json_array_length(
json_extract(
(JSON_EXTRACT(ex."order", '$.taker_tokens')),
'$[0]'
)
) as taker_length,
json_array_length(
json_extract(
(JSON_EXTRACT(ex."order", '$.maker_tokens')),
'$[0]'
)
) as maker_length
FROM
(
SELECT
evt_index,
evt_tx_hash,
evt_block_time,
ROW_NUMBER() OVER (
PARTITION BY
evt_tx_hash
ORDER BY
evt_index
) AS row_num
FROM
"delta_prod"."bebop_v3_ethereum"."BebopAggregationContract_evt_AggregateOrderExecuted"
where
evt_tx_hash = 0x1f6de664ff967ea0f5e5690ba90f71e76adcb5c5e6519285ceb6d259aa0e3eb4
UNION ALL
SELECT
evt_index,
evt_tx_hash,
evt_block_time,
ROW_NUMBER() OVER (
PARTITION BY
evt_tx_hash
ORDER BY
evt_index
) AS row_num
FROM
"delta_prod"."bebop_v4_ethereum"."BebopSettlement_evt_AggregateOrderExecuted"
where
evt_tx_hash = 0x1f6de664ff967ea0f5e5690ba90f71e76adcb5c5e6519285ceb6d259aa0e3eb4
) evt
LEFT JOIN (
SELECT
call_success,
call_block_time,
call_block_number,
call_tx_hash,
contract_address,
"order",
ROW_NUMBER() OVER (
PARTITION BY
call_tx_hash
ORDER BY
call_block_number
) AS row_num
FROM
"delta_prod"."bebop_v3_ethereum"."BebopAggregationContract_call_SettleAggregateOrder"
where
call_tx_hash = 0x1f6de664ff967ea0f5e5690ba90f71e76adcb5c5e6519285ceb6d259aa0e3eb4
UNION ALL
SELECT
call_success,
call_block_time,
call_block_number,
call_tx_hash,
contract_address,
"order",
ROW_NUMBER() OVER (
PARTITION BY
call_tx_hash
ORDER BY
call_block_number
) AS row_num
FROM
"delta_prod"."bebop_v4_ethereum"."BebopSettlement_call_SettleAggregateOrder"
where
call_tx_hash = 0x1f6de664ff967ea0f5e5690ba90f71e76adcb5c5e6519285ceb6d259aa0e3eb4
UNION ALL
SELECT
call_success,
call_block_time,
call_block_number,
call_tx_hash,
contract_address,
"order",
ROW_NUMBER() OVER (
PARTITION BY
call_tx_hash
ORDER BY
call_block_number
) AS row_num
FROM
"delta_prod"."bebop_v4_ethereum"."BebopSettlement_call_SettleAggregateOrderWithTakerPermits"
where
call_tx_hash = 0x1f6de664ff967ea0f5e5690ba90f71e76adcb5c5e6519285ceb6d259aa0e3eb4
) ex ON ex.call_tx_hash = evt.evt_tx_hash
and ex.row_num = evt.row_num
WHERE
ex.call_success = TRUE
),
unnested_array_taker AS (
SELECT
block_time,
block_number,
tx_hash,
evt_index,
contract_address,
expiry,
taker_address,
maker_address,
taker_tokens_json,
maker_tokens_json,
taker_amounts_json,
maker_amounts_json,
taker_length,
maker_length,
element_at(
CAST(
json_extract(taker_tokens_json, '$[0]') AS ARRAY < VARCHAR >
),
sequence_number
) AS taker_token_address,
element_at(
CAST(
json_extract(taker_amounts_json, '$[0]') AS ARRAY < VARCHAR >
),
sequence_number
) AS taker_token_amounts,
sequence_number - 1 AS taker_index
FROM
bebop_raw_data
CROSS JOIN UNNEST (
sequence(
1,
json_array_length(json_extract(taker_tokens_json, '$[0]'))
)
) AS t (sequence_number)
),
unnested_array_maker AS (
SELECT
block_time,
block_number,
tx_hash,
evt_index,
contract_address,
expiry,
taker_address,
maker_address,
taker_tokens_json,
maker_tokens_json,
taker_amounts_json,
maker_amounts_json,
taker_token_address,
taker_token_amounts,
taker_index,
taker_length,
maker_length,
element_at(
CAST(
json_extract(maker_tokens_json, '$[0]') AS ARRAY < VARCHAR >
),
sequence_number
) AS maker_token_address,
element_at(
CAST(
json_extract(maker_amounts_json, '$[0]') AS ARRAY < VARCHAR >
),
sequence_number
) AS maker_token_amounts,
sequence_number - 1 AS maker_index
FROM
unnested_array_taker
CROSS JOIN UNNEST (
sequence(
1,
json_array_length(json_extract(maker_tokens_json, '$[0]'))
)
) AS t (sequence_number)
),
simple_trades as (
SELECT
block_time,
block_number,
contract_address,
tx_hash,
evt_index,
taker_address,
maker_address,
taker_length,
maker_length,
CASE
WHEN taker_length = 1
AND maker_length > 1 THEN CAST(
array[taker_index, maker_index] as array < bigint >
)
WHEN maker_length = 1
AND taker_length > 1 THEN CAST(
array[maker_index, taker_index] as array < bigint >
)
ELSE CAST(
array[taker_index, maker_index] as array < bigint >
)
END as trace_address,
CASE
WHEN taker_length = 1
AND maker_length > 1 THEN 'Multi-Buy' -- inverted
WHEN maker_length = 1
AND taker_length > 1 THEN 'Multi-Sell' -- inverted, noted below...
ELSE 'Simple-Swap'
END as trade_type,
from_hex(maker_token_address) as token_bought_address, -- for some weird reason, this is inverted, based on the spark version of this query & also on arbiscan
from_hex(taker_token_address) as token_sold_address, -- noted above
CAST(maker_token_amounts as UINT256) as token_bought_amount_raw,
CAST(maker_token_amounts as double) as token_bought_amount,
CAST(taker_token_amounts as UINT256) as token_sold_amount_raw,
CAST(taker_token_amounts as double) as token_sold_amount
FROM
unnested_array_maker
WHERE
maker_token_address IS NOT NULL
AND taker_token_address IS NOT NULL
)
SELECT
'ethereum' AS blockchain,
'bebop' AS project,
'2' AS version,
CAST(date_trunc('DAY', t.block_time) AS date) AS block_date,
CAST(date_trunc('MONTH', t.block_time) AS date) AS block_month,
t.block_time AS block_time,
t.trade_type,
t_bought.symbol AS token_bought_symbol,
t_sold.symbol AS token_sold_symbol,
CASE
WHEN lower(t_bought.symbol) > lower(t_sold.symbol) THEN concat(t_sold.symbol, '-', t_bought.symbol)
ELSE concat(t_bought.symbol, '-', t_sold.symbol)
END AS token_pair,
t.token_bought_amount / power(10, coalesce(t_bought.decimals, 0)) AS token_bought_amount,
t.token_sold_amount / power(10, coalesce(t_sold.decimals, 0)) AS token_sold_amount,
t.token_bought_amount_raw,
t.token_sold_amount_raw,
CASE
WHEN t.trade_type = 'Multi-Buy' THEN COALESCE(
(
t.token_bought_amount / power(10, t_bought.decimals)
) * p_bought.price,
(t.token_sold_amount / power(10, t_sold.decimals)) * p_sold.price / maker_length
)
WHEN t.trade_type = 'Multi-Sell' THEN COALESCE(
(t.token_sold_amount / power(10, t_sold.decimals)) * p_sold.price,
(
t.token_bought_amount / power(10, t_bought.decimals)
) * p_bought.price / taker_length
)
ELSE COALESCE(
(
t.token_bought_amount / power(10, t_bought.decimals)
) * p_bought.price,
(t.token_sold_amount / power(10, t_sold.decimals)) * p_sold.price
)
END as amount_usd, -- when there's a Multi-trade, the usd value of the multi tokens traded is used as the amount_usd
t.token_bought_address,
t.token_sold_address,
t.taker_address AS taker,
t.contract_address AS maker,
t.contract_address AS project_contract_address,
t.tx_hash,
tx."from" tx_from,
tx.to AS tx_to,
t.trace_address,
t.evt_index
FROM
simple_trades t
INNER JOIN "delta_prod"."ethereum"."transactions" tx ON t.tx_hash = tx.hash
AND tx.block_time >= TIMESTAMP '2023-03-30'
LEFT JOIN "delta_prod"."tokens"."erc20" t_bought ON t_bought.contract_address = t.token_bought_address
AND t_bought.blockchain = 'ethereum'
LEFT JOIN "delta_prod"."tokens"."erc20" t_sold ON t_sold.contract_address = t.token_sold_address
AND t_sold.blockchain = 'ethereum'
LEFT JOIN "delta_prod"."prices"."usd" p_bought ON p_bought.minute = date_trunc('minute', t.block_time)
AND p_bought.contract_address = t.token_bought_address
AND p_bought.blockchain = 'ethereum'
AND p_bought.minute >= TIMESTAMP '2023-03-30'
LEFT JOIN "delta_prod"."prices"."usd" p_sold ON p_sold.minute = date_trunc('minute', t.block_time)
AND p_sold.contract_address = t.token_sold_address
AND p_sold.blockchain = 'ethereum'
AND p_sold.minute >= TIMESTAMP '2023-03-30'
this transaction had 4 tokens sold in the event, then output 4 rows with one per token sold. within the logic, a trace_address
value is built out, which allows the unique keys to represent each row as unique, even though the evt_index
is the same for all 4 rows.
will this approach work for you?
@jeff-dude thanks for sharing this. Such approach doesn't work for Odos unfortunately, because Odos can process N input tokens and M output tokens (e.g. 4 input and 3 output) and in this case there is no good way to record such a transaction with multiple rows. Any other ways to put those trades?
@jeff-dude thanks for sharing this. Such approach doesn't work for Odos unfortunately, because Odos can process N input tokens and M output tokens (e.g. 4 input and 3 output) and in this case there is no good way to record such a transaction with multiple rows. Any other ways to put those trades?
got it. i'm not sure how we should process and store these events to be honest. let me bring this back to the internal team for opinion. here are a few thoughts:
multi_swap_flag
token_pair
, would be USDC.e + WETH + OP - WETH + VELO
Thank you, @jeff-dude I think the first option looks good. Looking forward to the team response.
Hi @jeff-dude Please share the team's response. Thank you.
Hi @jeff-dude Please share the team's response. Thank you.
thank you for the patience.
at the moment, we aren't comfortable modifying the design of the table structure to fit this unique use case. i would suggest two options:
Hi @jeff-dude What do you think of the following approach: In case of multi input have the following values for the fields:
There also should be an additional table e.g. MultiTokenTrades which contains the fields above, but in form of arrays:
This way we don't break the existing dex_aggregator.trades table, have the amount_usd
value in it and for the details on the multi token swap one can refer to a separate table. What do you think?
In case of multi input have the following values for the fields:
- tx_hash=
- token_bought_symbol=TOKEN1+TOKEN2
- token_sold_symbol=TOKEN3+TOKEN4+TOKEN5
- token_pair=TOKEN1+TOKEN2-TOKEN3+TOKEN4+TOKEN5
- token_bought_amount=null
- token_sold_amount=null
- token_bought_amount_raw=null
- token_sold_amount_raw=null
- amount_usd=
- token_bought_address=null
- token_sold_address=null
as long as you populate the unique keys, this should be fine. maybe for symbols / token pair, it's a bit more formatted (still varchar, but looks like array for readability):
for bought and sold [token1, token 2,..]
for pair [token1, token2,..]-[token1, token2,..]
i think the big question comes down to how do you calculate amount_usd
with null for amount raw / amount? if you have a plan, then i'm happy to see in PR.
There also should be an additional table e.g. MultiTokenTrades which contains the fields above, but in form of arrays:
- tx_hash=
- token_bought_symbol=[TOKEN1, TOKEN2]
- token_sold_symbol=[TOKEN3, TOKEN4, TOKEN5]
- token_pair=TOKEN1+TOKEN2-TOKEN3+TOKEN4+TOKEN5
- token_bought_amount=[amount1, amount2]
- token_sold_amount=[amount3, amount4, amount5]
- token_bought_amount_raw=[raw_amount1, raw_amount2]
- token_sold_amount_raw=[raw_amount3, raw_amount4, raw_amount5]
- amount_usd=
- token_bought_address=[token1_address, token2_address]
- token_sold_address=[token3_address, token4_address, token5_address]
should be fine here too. ensure lowercase and underscore naming standard, but same idea (multi_token_trades
). then you can join the two together as needed downstream.
Odos allows users to swap multiple input and multiple output tokens in a single atomic transaction. And it becomes a challenge when we want to put such trades to the dex.trades table.
We definitely can calculate the
amount_usd
field, but what can we do with the following fields when there is more than one input or output token?I see the following options:
symbol fields:
amount fields:
token fields: