search

duneanalytics / spellbook

SQL views for Dune
Other
1.14k stars 1.06k forks source link

[BUG] dex.trades missing >50 equalizer LPs #3200

Open ftm1337 opened 1 year ago

ftm1337 commented 1 year ago

Description

Missing Data in dex.trades: Around 50 Equalizer.exchange pools are missing.

Current behavior

Only around 146 pairs out of ~200 pairs are present in the dex.trades table for equalizer, including some of the biggest ones.

Expected behavior

Might be unrelated but dex.trades doesnt have names on prominent tokens like TAROT, EQUAL, MPX, MUMMY, etc. and other bigger ones like BIFI, DEUS, fBOMB dont have amount_usd values https://dune.com/queries/2385999/3912732

Impacted model(s)

dex.trades

Possible solution

jeff-dude commented 1 year ago

@ftm1337 thanks for raising. starting simple: have you seen the code to build affected data here? you can leverage that as a starting point to narrow down potentially why some pairs are missing

jeff-dude commented 1 year ago

as for token metadata, such as symbol/decimals, please use this file. once added, they will propagate through to dex.trades

ftm1337 commented 1 year ago

@jeff-dude is it possible that the issue is arising from the start date being set to 23-nov-2023 instead of the actual 03-nov-2023 ? i think pools that were created between 3-23 were skipped..

jeff-dude commented 1 year ago

yep, for sure. if the start date is late, then all preceding data will be missing. feel free to submit a PR with correct start date. the PR will run a gh action which generates a test table of the affected models in PR, which will be live on dune for ~24 hours for you to query and test

ftm1337 commented 1 year ago

@jeff-dude the missing pools still dont show up..

select distinct project_contract_address 
from dex.trades 
where project = 'equalizer_exchange'

does not contain all pools.. it has only about 60% of pools..

however, all of the pools are now visible here

select cast(_0 as decimal),evt_block_time,pair,stable,token0,token1
from equalizer_exchange_fantom.PairFactory_evt_PairCreated
ORDER BY 1

i'm not sure how to bring these under the dex.trades table..

jeff-dude commented 1 year ago

the first step i'd recommend is to take the latest code from main branch, run dbt compile to build sql code, find the model under the target directory and manually debug in the dune app. you can comment out code to start, keep it simple with the decoded table and filter on missing pools, ensure it returns as expected, then uncomment code in the spell until the result disappears. see if that helps find the bug

ftm1337 commented 11 months ago

I tried triaging this, and made a few notes for anyone who might be interested: (This issue still has an available bounty of 100 EQUAL tokens for anyone who solves this)

jeff-dude commented 10 months ago

@ftm1337 if you look at the spell that loads equalizer data into dex.trades https://github.com/duneanalytics/spellbook/blob/main/models/equalizer_exchange/fantom/equalizer_exchange_fantom_trades.sql

we can see the same decoded tables you're querying. however, it looks like the pools you're reporting as missing don't come up in swaps decoded table. here is example query that shows the pairs created, but when joined to swap, it drops out.

with
  dex as (
    SELECT
      *
    FROM
      dex."trades" AS t
    WHERE
      project = 'equalizer_exchange'
      AND project_contract_address = 0x7547d05dff1da6b4a2ebb3f0833afe3c62abd9a1
  ),
  pair_created as (
    select
      *
    FROM
      equalizer_exchange_fantom.PairFactory_evt_PairCreated f
    where
      f.pair = 0x7547d05dff1da6b4a2ebb3f0833afe3c62abd9a1
  ),
  spell_logic as (
    select
      f.*
    FROM
      equalizer_exchange_fantom.Pair_evt_Swap t
      inner join equalizer_exchange_fantom.PairFactory_evt_PairCreated f on f.pair = t.contract_address
    where
      t.contract_address = 0x7547d05dff1da6b4a2ebb3f0833afe3c62abd9a1 --this field populates the dex column
  )
SELECT
  'evt_PairCreated',
  *
from
  pair_created
union all
SELECT
  'evt_Swap',
  *
from
  spell_logic
LIMIT
  1

is it possible they have no swaps? if not, are you missing contracts to be decoded and added to the spell linked above?

from what i see, there is no issue with the code that loads dex.trades. the issue, if any, would be on missing contracts in decoded layer.

ftm1337 commented 7 months ago

Hey @jeff-dude! thanks for fixing this :D Please send me your public evm address (i am @i543 on Discord if you prefer that), as we had appointed a $100 bounty while opening this issue. Thank you once again <3

jeff-dude commented 7 months ago

Hey @jeff-dude! thanks for fixing this :D Please send me your public evm address (i am @i543 on Discord if you prefer that), as we had appointed a $100 bounty while opening this issue. Thank you once again <3

interesting! my public evm is jdude.eth

ftm1337 commented 7 months ago

hey jeff, looks like the issue still persists :sweat_smile: https://dune.com/queries/3070210

jeff-dude commented 7 months ago

hey jeff, looks like the issue still persists 😅 https://dune.com/queries/3070210

looks like equalizer project has been updated, naming standard wise:

WITH 
t1 AS (SELECT DISTINCT contract_address FROM equalizer_exchange_fantom.Pair_evt_Swap)
, t2 AS (SELECT DISTINCT project_contract_address FROM dex.trades WHERE project = 'equalizer')
, t3 AS (SELECT DISTINCT pair FROM equalizer_exchange_fantom.PairFactory_evt_PairCreated)
, t4 AS (SELECT DISTINCT contract_address FROM equalizer_exchange_fantom.Pair_evt_Initialized)
--, t5 AS (SELECT _0 AS id,pair AS pool_id,stable,(token0||token1) AS pair_id,token0,token1,1 AS one FROM equalizer_exchange_fantom.PairFactory_evt_PairCreated)
--, t6 AS (SELECT t5.id, t4.contract_address AS dune_init_pools,t5.pool_id AS all_pools FROM t5 LEFT OUTER JOIN t4 ON t5.pool_id = t4.contract_address)

SELECT 'Pair_evt_Swap' as tableName, COUNT(*) as numRows FROM t1
UNION ALL SELECT 'dex.trades_project=equalizer_exchange' as tableName, COUNT(*) as numRows FROM t2
UNION ALL SELECT 'PairFactory_evt_PairCreated' as tableName, COUNT(*) as numRows FROM t3
UNION ALL SELECT 'Pair_evt_Initialized' as tableName, COUNT(*) as numRows FROM t4
ftm1337 commented 7 months ago

i see.. yes, its now equalizer :+1: but still, the results are similar.. PairFactory_evt_PairCreated is logged 367 times by dune (emitted by the factory) and the Pair_evt_Initialized is logged just 157 times (emitted by the created pool right at the time of creation)

i think i found the solution.. im going to submit a few pool contracts to dune for parsing, im guessing there's a difference in bytecode that makes some pairs unavailable..

jeff-dude commented 7 months ago

i see.. yes, its now equalizer 👍 but still, the results are similar.. PairFactory_evt_PairCreated is logged 367 times by dune (emitted by the factory) and the Pair_evt_Initialized is logged just 157 times (emitted by the created pool right at the time of creation)

i think i found the solution.. im going to submit a few pool contracts to dune for parsing, im guessing there's a difference in bytecode that makes some pairs unavailable..

yep, that's what i was working towards in previous comments. looks like missing decoded contracts to add to the spell. let me know if you get those added and i can help merge 👍

ftm1337 commented 7 months ago

i think there is more to it.. :thinking: (referring to this same query, https://dune.com/queries/3070210/5113730?sidebar=none)

Consider this : As expected, if a contract isnt decoded, it's pain_intialized event isnt picked up image

But interestingly, some contracts have been decoded by dune but their corresponding Pair_Initialized event isnt picked up.. image