# Spellbook DevLog #1

**Published by:** [Geyang Huang](https://paragraph.com/@hosuke/)
**Published on:** 2022-11-01
**URL:** https://paragraph.com/@hosuke/spellbook-devlog-1

## Content

如何寻找 Sushiswap 在 avalanche_c 上的 swap 事件表开发dex.trades时，如果有一个表 Swap 事件表，则事半功倍 拿sushiswap举例，我们要寻找sushiswap在avalanche_c链上已部署的合约地址，最直接的方法就是进官方开发文档里找。 https://docs.sushi.com/docs/Developers/Deployment%20Addressessushiswap deployment address然后我们去snowtrace.io，查询SushiV2Factory地址SushiswapV2Factory transactions可以看到工厂合约在Transactions页面有许多创建交易对的记录。 这时我们进入Internal Txns页面，可以看到新建的合约。 这些新建的合约便是SushiswapPair，每个Pair合约下面我们会SushiswapV2Factory Internal Txns确认了之后，我们只要去Dune上提交工厂合约解析： https://dune.com/contracts/new 值得注意的是，我们需要勾选一些高级选项：勾选该合约是一个工厂合约，并创建了许多实例这样我们就可以在Dune上直接查到解码后的Sushiswap avalanche_c交易记录表了sushiswap_v2_avalanche_c.Pair_evt_Swap如果没有解析这个表的话，如何能查到sushiswap在avalanche_c上面的交易记录呢？可以使用avalanche_c.logs原始数据表直接解析：{{ config( schema = 'sushiswap_avalanche_c' ,alias = 'trades' ,partition_by = ['block_date'] ,materialized = 'incremental' ,file_format = 'delta' ,incremental_strategy = 'merge' ,unique_key = ['block_date', 'blockchain', 'project', 'version', 'tx_hash', 'evt_index', 'trace_address'] ,post_hook='{{ expose_spells(\'["avalanche_c"]\', "project", "sushiswap", \'["hosuke", "zhongyiio"]\') }}' ) }} {% set project_start_date = '2022-01-07' %} WITH sushiswap_decodes AS ( SELECT call_block_time, call_trace_address, call_tx_hash, contract_address, `to` FROM {{ source('sushiswap_v2_avalanche_c', 'SushiSwapRouter_call_swapETHForExactTokens') }} WHERE call_success = true {% if is_incremental() %} AND call_block_time >= date_trunc("day", now() - interval '1 week') {% endif %} UNION ALL SELECT call_block_time, call_trace_address, call_tx_hash, contract_address, `to` FROM {{ source('sushiswap_v2_avalanche_c', 'SushiSwapRouter_call_swapExactETHForTokens') }} WHERE call_success = true {% if is_incremental() %} AND call_block_time >= date_trunc("day", now() - interval '1 week') {% endif %} UNION ALL SELECT call_block_time, call_trace_address, call_tx_hash, contract_address, `to` FROM {{ source('sushiswap_v2_avalanche_c', 'SushiSwapRouter_call_swapExactTokensForETH') }} WHERE call_success = true {% if is_incremental() %} AND call_block_time >= date_trunc("day", now() - interval '1 week') {% endif %} UNION ALL SELECT call_block_time, call_trace_address, call_tx_hash, contract_address, `to` FROM {{ source('sushiswap_v2_avalanche_c', 'SushiSwapRouter_call_swapExactTokensForTokens') }} WHERE call_success = true {% if is_incremental() %} AND call_block_time >= date_trunc("day", now() - interval '1 week') {% endif %} UNION ALL SELECT call_block_time, call_trace_address, call_tx_hash, contract_address, `to` FROM {{ source('sushiswap_v2_avalanche_c', 'SushiSwapRouter_call_swapTokensForExactETH') }} WHERE call_success = true {% if is_incremental() %} AND call_block_time >= date_trunc("day", now() - interval '1 week') {% endif %} UNION ALL SELECT call_block_time, call_trace_address, call_tx_hash, contract_address, `to` FROM {{ source('sushiswap_v2_avalanche_c', 'SushiSwapRouter_call_swapTokensForExactTokens') }} WHERE call_success = true {% if is_incremental() %} AND call_block_time >= date_trunc("day", now() - interval '1 week') {% endif %} UNION ALL SELECT call_block_time, call_trace_address, call_tx_hash, contract_address, `to` FROM {{ source('sushiswap_v2_avalanche_c', 'SushiSwapRouter_call_swapExactETHForTokensSupportingFeeOnTransferTokens') }} WHERE call_success = true {% if is_incremental() %} AND call_block_time >= date_trunc("day", now() - interval '1 week') {% endif %} UNION ALL SELECT call_block_time, call_trace_address, call_tx_hash, contract_address, `to` FROM {{ source('sushiswap_v2_avalanche_c', 'SushiSwapRouter_call_swapExactTokensForETHSupportingFeeOnTransferTokens') }} WHERE call_success = true {% if is_incremental() %} AND call_block_time >= date_trunc("day", now() - interval '1 week') {% endif %} UNION ALL SELECT call_block_time, call_trace_address, call_tx_hash, contract_address, `to` FROM {{ source('sushiswap_v2_avalanche_c', 'SushiSwapRouter_call_swapExactTokensForTokensSupportingFeeOnTransferTokens') }} WHERE call_success = true {% if is_incremental() %} AND call_block_time >= date_trunc("day", now() - interval '1 week') {% endif %} ), sushiswap_decodes_with_log AS ( SELECT bytea2numeric_v2(substr(l.data, 3, 64)) AS amount0In, bytea2numeric_v2(substr(l.data, 3 + 64, 64)) AS amount1In, bytea2numeric_v2(substr(l.data, 3 + 64 + 64, 64)) AS amount0Out, bytea2numeric_v2(substr(l.data, 3 + 64 + 64 + 64 + 1, 64)) AS amount1Out, l.index AS evt_index, p.token0 AS token0, p.token1 AS token1, call_block_time, call_trace_address, call_tx_hash, t.contract_address, call_trace_address, `to` FROM sushiswap_decodes t INNER JOIN {{ source('avalanche_c', 'logs') }} l ON t.call_tx_hash = l.tx_hash AND l.topic1 = "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822" {% if is_incremental() %} AND l.block_time >= date_trunc("day", now() - interval '1 week') {% else %} AND l.block_time >= '{{ project_start_date }}' {% endif %} INNER JOIN {{ source('sushiswap_v2_avalanche_c', 'SushiV2Factory_evt_PairCreated') }} p ON l.contract_address = p.pair ), sushiswap_dex AS ( SELECT call_block_time AS block_time, `to` AS taker, '' AS maker, CASE WHEN amount0Out = 0 THEN amount1Out ELSE amount0Out END AS token_bought_amount_raw, CASE WHEN amount0In = 0 THEN amount1In ELSE amount0In END AS token_sold_amount_raw, cast(NULL as double) AS amount_usd, CASE WHEN amount0Out = 0 THEN token1 ELSE token0 END AS token_bought_address, CASE WHEN amount0In = 0 THEN token1 ELSE token0 END AS token_sold_address, contract_address AS project_contract_address, call_tx_hash AS tx_hash, call_trace_address AS trace_address, evt_index FROM sushiswap_decodes_with_log ) SELECT 'avalanche_c' AS blockchain, 'sushiswap' AS project, '2' AS version, try_cast(date_trunc('DAY', sushiswap_dex.block_time) AS date) AS block_date, sushiswap_dex.block_time, erc20a.symbol AS token_bought_symbol, erc20b.symbol AS token_sold_symbol, case when lower(erc20a.symbol) > lower(erc20b.symbol) then concat(erc20b.symbol, '-', erc20a.symbol) else concat(erc20a.symbol, '-', erc20b.symbol) end AS token_pair, sushiswap_dex.token_bought_amount_raw / power(10, erc20a.decimals) AS token_bought_amount, sushiswap_dex.token_sold_amount_raw / power(10, erc20b.decimals) AS token_sold_amount, sushiswap_dex.token_bought_amount_raw, sushiswap_dex.token_sold_amount_raw, coalesce( sushiswap_dex.amount_usd , (sushiswap_dex.token_bought_amount_raw / power(10, p_bought.decimals)) * p_bought.price , (sushiswap_dex.token_sold_amount_raw / power(10, p_sold.decimals)) * p_sold.price ) AS amount_usd, sushiswap_dex.token_bought_address, sushiswap_dex.token_sold_address, coalesce(sushiswap_dex.taker, tx.from) AS taker, sushiswap_dex.maker, sushiswap_dex.project_contract_address, sushiswap_dex.tx_hash, tx.from AS tx_from, tx.to AS tx_to, sushiswap_dex.trace_address, sushiswap_dex.evt_index from sushiswap_dex inner join {{ source('avalanche_c', 'transactions') }} tx on sushiswap_dex.tx_hash = tx.hash {% if is_incremental() %} and tx.block_time >= date_trunc("day", now() - interval '1 week') {% else %} and tx.block_time >= '{{project_start_date}}' {% endif %} left join {{ ref('tokens_erc20') }} erc20a on erc20a.contract_address = sushiswap_dex.token_bought_address and erc20a.blockchain = 'avalanche_c' left join {{ ref('tokens_erc20') }} erc20b on erc20b.contract_address = sushiswap_dex.token_sold_address and erc20b.blockchain = 'avalanche_c' left join {{ source('prices', 'usd') }} p_bought on p_bought.minute = date_trunc('minute', sushiswap_dex.block_time) and p_bought.contract_address = sushiswap_dex.token_bought_address and p_bought.blockchain = 'avalanche_c' {% if is_incremental() %} and p_bought.minute >= date_trunc("day", now() - interval '1 week') {% else %} and p_bought.minute >= '{{project_start_date}}' {% endif %} left join {{ source('prices', 'usd') }} p_sold on p_sold.minute = date_trunc('minute', sushiswap_dex.block_time) and p_sold.contract_address = sushiswap_dex.token_sold_address and p_sold.blockchain = 'avalanche_c' {% if is_incremental() %} and p_sold.minute >= date_trunc("day", now() - interval '1 week') {% else %} and p_sold.minute >= '{{project_start_date}}' {% endif %} ;

## Publication Information

- [Geyang Huang](https://paragraph.com/@hosuke/): Publication homepage
- [All Posts](https://paragraph.com/@hosuke/): More posts from this publication
- [RSS Feed](https://api.paragraph.com/blogs/rss/@hosuke): Subscribe to updates
- [Twitter](https://twitter.com/GeyangH): Follow on Twitter