duneanalytics / spellbook Goto Github PK

add trades from DEX Sake Swap to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

probably because weth9 tokens also withdraw and deposit, instead of only transfer.

fix could consist of using a table similar to the one below, to replace the weth9 entries in erc20."ERC20_evt_Transfer":

SELECT
    contract_address,
    dst,
    evt_block_number,
    evt_block_time,
    evt_index,
    evt_tx_hash,
    src,
    wad
FROM zeroex."WETH9_evt_Transfer"

UNION ALL

SELECT
    contract_address,
    dst,
    evt_block_number,
    evt_block_time,
    evt_index,
    evt_tx_hash,
    NULL AS src,
    wad
FROM zeroex."WETH9_evt_Deposit"

UNION ALL

SELECT
    contract_address,
    NULL AS dst,
    evt_block_number,
    evt_block_time,
    evt_index,
    evt_tx_hash,
    src,
    wad
FROM zeroex."WETH9_evt_Withdrawal"

In this Transfer event query, the value is 1000000000000000000. But in etherscan, it's 999999999999999999.

Add more INDEXES to 'hex' dataset tables.
like on stakeId and for table: HEX_evt_Transfer columns: to/from

Hi
Just spotted a few of Aave's aTokens are missing from the ERC20 list.

If these can be added:
\xfc1e690f61efd961294b3e1ce3313fbd8aa4f85d​ | aDAI | 18
\x6ee0f7bb50a54ab5253da0667b0dc2ee526c30a8​ | aBUSD | 18
\x712db54daa836b53ef1ecbb9c6ba3b9efb073f40​ | aENJ | 18
\x69948cc03f478b95283f7dbf1ce764d0fc7ec54c​ | aREN | 18
\x71010a9d003445ac60c4e6a7017c1e89a477b438​ | aREP | 18
\x6fb0855c404e09c47c3fbca25f08d4e41f9f062f​ | aWBTC | 8
\x6fb0855c404e09c47c3fbca25f08d4e41f9f062f​ | aZRX | 18

https://etherscan.io/token/0x3b484b82567a09e2588a13d54d032153f0c0aee0

Similarly, ethereum.contracts is missing this address as well. It's both the SOS token and the OpenDAO contract.

There's a few tokens in prices.usd that have inconsistent symbols for a brief window of time, specifically from 2021-08-30 14:16 to 2021-08-30 14:20 the symbol appears to be capitalized. It's not a big deal, but it's enough to mess up my queries that assume that the symbols are consistent, and I need to convert the symbols to all caps or something which makes the symbols ugly. You can use some variant of these queries to check for yourself:
https://dune.xyz/queries/594719

select contract_address
    , count(distinct symbol)
from prices.usd
where 1=1
-- and contract_address = '\xae7ab96520de3a18e5e111b5eaab095312d7fe84'
-- and symbol = 'MONA'
and contract_address in ('\xae7ab96520de3a18e5e111b5eaab095312d7fe84' --stETH/STETH?
    , '\x275f5ad03be0fa221b4c6649b8aee09a42d9412a'  -- mona/MONA?
    , '\x221657776846890989a759ba2973e427dff5c9bb' -- REPv2/REPV2 ?
    , '\xeb4c2781e4eba804ce9a9803c67d0893436bb27d' -- renBTC/RENBTC ?
    )
and minute::date = '2021-08-30'
group by 1
;

-- check raw data of a specific offender
select *
from prices.usd
where symbol = 'STETH'
and minute::date = '2021-08-30'

Broader query to see how big the problem is for that day:

select contract_address
    , minute::date as day
    , count(distinct symbol) as num_symbols
from prices.usd
group by 1,2
having count(distinct symbol) > 1
/*
results:
\x221657776846890989a759ba2973e427dff5c9bb | 2021-08-30 | 2
\x275f5ad03be0fa221b4c6649b8aee09a42d9412a | 2021-08-30 | 2
\x286bda1413a2df81731d4930ce2f862a35a609fe | 2021-08-30 | 2
\x2c537e5624e4af88a7ae4060c022609376c8d0eb | 2021-08-30 | 2
\x2c537e5624e4af88a7ae4060c022609376c8d0eb | 2021-11-29 | 2
\x809826cceab68c387726af962713b64cb5cb3cca | 2021-08-30 | 2
\xae7ab96520de3a18e5e111b5eaab095312d7fe84 | 2021-08-30 | 2
\xeb4c2781e4eba804ce9a9803c67d0893436bb27d | 2021-08-30 | 2
*/

Can this be fixed by just rerunning some insert script over that period of time?

sorry for the mess (:
couldn't figure out the format for adding a token to the list (spaces vs tabs didn't worked... ):
I just want to add
\xca1207647Ff814039530D7d35df0e1Dd2e91Fa84 DHT 18

add trades from DEX MStable to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

add trades from DEX Indexed Finance to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

data in these views have not updated since the views were established in PR

https://github.com/duneanalytics/abstractions/blob/master/polygon/QiDao/view_balances_by_day.sql
https://github.com/duneanalytics/abstractions/blob/master/polygon/QiDao/view_all_owners.sql

I checked the queries individually and they work fine, so something wrong with chron scheduling

Why does WETH9_evt_Transfer table contain transfers from the contract https://etherscan.io/address/0x14fbca95be7e99c15cc2996c6c9d841e54b79425#code

It does not seem related, is this a bug?

As we see the L2 Arbitrum is developing very fast. From the Discord channel discussion, Arbitrum may airdrop to the early users, So I think support the Aribtrum is necessary.

Hey Guys,
thanks for the fast update and solving Issue #117 😎 👍

This issue is for the new schema : rarible_v1.ERC1155Sale_v2_evt_Buy

I tried to parse a specific token on the Column "tokenId" but i get the same Error anytime...
This is my Query to test it :

SELECT tokenId
FROM rarible_v1."ERC721Sale_v1_evt_Buy"
GROUP BY tokenId

The Error message i get is :

Error running query: column "tokenid" does not exist LINE 1: ...ery Hash: 088b80fbe2b501ac75abf8cdbe67ec38 */ SELECT tokenId ^ HINT: Perhaps you meant to reference the column "ERC721Sale_v1_evt_Buy.tokenId".

It looks like the script try to gather Info from "tokenid" instead of "tokenId" but im not sure🤷‍♂️

Greetings
Jon

merge table is using a wrong address of a contract that had a Bug so it was renamed Omnomnom.

Old contract with Bug:
0x27d270b7d58d15d455c85c02286413075f3c8a31

The correct New contract is:
0xc3f8a0f5841abff777d3eefa5047e8d413a1c9ab

We should add merge or call it merge_new (which I think is less preferable as no one should be using tables over the old collection as it's not tradable anymore)

add trades from DEX Unifi to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

add trades from DEX Smoothy Finance to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

ticker: GIV
contract address: 0xf6537fe0df7f0cc0985cf00792cc98249e73efa0
decimals: 8

ticker: FUSE
contract address: 0x970B9bB2C0444F5E81e9d0eFb84C8ccdcdcAf84d
decimals: 18

ticker: ALN
contract address: 0x1b7b723e66a0dd33e8f5e8ec039b51acafcd66a5
decimals: 18

Query to produce the results: https://dune.xyz/queries/434904

add trades from DEX Convergence X to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

To add Uniswap trades to the dex."trades" table on polygon similar to the 1inch request in #606 and committed in #625.

https://github.com/duneanalytics/abstractions/tree/master/polygon/dex/trades

Here is a query corresponding to this transaction demonstrating that a "single" token swap is being counted 6x in the dex.trades table.

1. There is double accounting for the same aggregator (0x and Matcha)
2. Each of the 3 "hops" of the trade is being counted on both aggregators.

Realistically, it makes sense to count each of the DEX trades, but for an aggregator protocol, this should only be counted a single trade for the user 0xd3f8f55b6b244afd86f2d0fb7f2de445cc0c10a8 who swapped ICE for USDC.

Or, at the very least only counted as a single aggregator (not two).

just check the trades amount of this new nft project
'\xa5Bb28eecC6134F89745E34ec6aB5d5Bcb16dAD7'
the daily trade amount doesn't match the amount in opensea.
Its probably because of joining different tables using hash, a lot of scenarios where one person buys multiple nft in one transaction.
the result will multiply. if a person does so.

my solution is getting transaction event from the eth.log, therefore get the hashes , then using the hashes to get the event that topic0 = opensea orders match
though it's not perfect(if someone bought more than one kind of nft in one transaction, it would be counted in our amount, but less probably), its better than the pervious one

my code
-- Bilibili NFT
WITH transfer_hash_all
AS(
SELECT DISTINCT(tx_hash)
FROM ethereum."logs"
WHERE contract_address = '\xa5Bb28eecC6134F89745E34ec6aB5d5Bcb16dAD7'
AND topic1 = '\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
AND topic2 != '\x0000000000000000000000000000000000000000000000000000000000000000'
),
sell_hash
AS (
SELECT *
FROM ethereum."logs" log
WHERE log.tx_hash IN (SELECT * FROM transfer_hash_all)
AND topic1 = '\xc4109843e0b7d514e4c093114b863f8e7d8d9a458c372cd51bfe526b588006c9'
)

SELECT date_trunc('day',block_time) as time ,COUNT(*) as trades_count FROM sell_hash
GROUP BY 1
ORDER BY 1 DESC

Popular assets to list:

UST
ENS
OHM
LTO
RUNE
LUNA
ILV
OGN
SOS
GAS (GAS DAO)

add trades from DEX Saddle Finance to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

Hi, @masquot

According to my investigation, multiple swaps within one transaction may bring duplicated rows in nft.trades.

Take this tx(0xbbed44a3f6ba1bdfb...) as an example:

It conducts 52 erc721 exchanges as a proxy for OpeaSean.

Using tx_hash as join key, the left join SQL may produce a Cartesian product, thus generating duplicated rows. Theoretically, there will be 2704(52x52) rows about this tx in nft.trades.

I notice nft.trades table has some unique index constraints, but there are still 1494 rows being inserted according to my Dune query: https://dune.xyz/queries/495980.

An overview of this problem

I aggregate the OpenSea trades of 2022-01-01.

863 transactions(with multiple erc721 tansfers inside) produce 29741 records (the expected number is 4329).

The vast majority of cases are caused by GenieSwap 0x0a267cf51ef038fc00e71801f5a524aec06e4f07 and only few other contracts.

I also wrote a more detailed survey to analyze the problem and be able to reproduce the problem on my dataset.

Let me know what you think!

This query here shows ~140 active erc20 tokens which are missing form the token list.

https://dune.xyz/queries/432914

I have not been able to determine how this list is populated or maintained from within this repo so posting as an issue. In the mean time, I am using this token list extension user generated view (here is where the data for the tokens can be found).

https://dune.xyz/queries/432843

add trades from DEX Lua Swap to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

add trades from DEX Hashflow to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

check out this query, might be helpful
https://dune.xyz/queries/283658

How to find the all tables in 'ethereum' ,which now do not have all table ,for example uniswap_v2??

add trades from DEX Firebird (former Value Liquid) to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

The table view "insert_weth_balance_changes" is querying the table zeroex."WETH9_evt_Withdrawal" but is unclear how this table is constructed?

add trades from DEX xSigma to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

When building this table, we have come across nearly 100 erc20 tokens that aren't listed in https://github.com/duneanalytics/abstractions/blob/master/ethereum/erc20/tokens.sql

I have manually prepared a list of missing tokens so far and, before alphabetizing them to fit the query standard, wanted to know if you would accept a PR of this flavour:

missing_tokens.txt

Similarly, we have the same issue with the prices."usd" which may not be as simple to amend.

add trades from DEX DFX Finance to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

add trades from DEX Shell to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

add trades from DEX Mini Swap to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

Hi Guys,
the rarible_v1 wont work after the 03/09 because the owner released a new contract.

rarible_v1."ERC721Sale_v1_evt_Buy" & rarible_v1."ERC1155Sale_v1_evt_Buy" are affected.

old contracts : 0x8c530a698b6e83d562db09079bc458d4dad4e6c5 & 0xA5aF48b105Ddf2fa73cBaaC61d420eA31b3c2a07
new contracts : 0x93f2a75d771628856f37f256da95e99ea28aafbe & 0x131aebbfe55bca0c9eaad4ea24d386c5c082dd58

I hope this is enough info to fix it 😎

Greetings ✌️

add trades from DEX DeFi Plaza to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

add trades from DEX Swapr to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

Since the merge of #1012, the added token doesn't seem to be appearing in the prices table.
Here is a sample query showing its lack of presence:

https://dune.com/queries/670396

select * 
from prices.usd 
where contract_address = '\xdef1ca1fb7fbcdc777520aa7f396b4e015f497ab'
limit 1

Originally posted by @bh2smith in #1012 (comment)

add trades from DEX POWERINDEX to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

https://dune.xyz/queries/301879

Transaction with hash \x7184a4960605d477471f7fddf2dec18f95ed737ad6eec2f7c75568829aeaf1d9 appears twice in ethereum.transactions.

Hi, would we be able to temporarily truncate the erc20.daily_token_balances in the Optimism2 database and pause the cron job?

There are some issues with it that I need to resolve, so it's showing incorrect data right now.

add trades from DEX ShibaSwap to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

add trades from sFinance to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

When a token is not known from inception of a dex pool, erc20.decimals is not known and dex.trades.token_a_amount (or token_b_amount) cannot be calculated, resulting in NULL.

Problem is that when the token decimals are added at a later date, due to using INSERT, token_a_amount is never recalculated for the older rows.

https://github.com/duneanalytics/abstractions/blob/e2b1a66bcc5d021d9ac807085aaa79d8c669a9a6/ethereum/dex/trades/insert_sushi.sql#L6

This in turn influences dex.view_token_prices since it depends and filters on this column
https://github.com/duneanalytics/abstractions/blob/e2b1a66bcc5d021d9ac807085aaa79d8c669a9a6/ethereum/dex/view_token_prices.sql#L19

Due to this, a lot of price history is not present in dex.view_token_prices even though it can be calculated quite easily.

Is there any way to rebuild some of the price tables from scratch, using the now known decimals?

An easy use case is oBTC. Compare for example

SELECT *
FROM dex.trades
WHERE token_a_address = '\x8064d9Ae6cDf087b1bcd5BDf3531bD5d8C537a68'
ORDER BY 1 DESC

with

SELECT *
FROM dex.view_token_prices 
WHERE contract_address = '\x8064d9Ae6cDf087b1bcd5BDf3531bD5d8C537a68'

Redefining token_a_symbol and token_a_amount would be all that's needed.

add trades from DEX DeFi Swap to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/

To create a dex.trade table for Polygon (on Dune Analytics) similar to the one on Ethereum. Make sure all 1inch trades are/ will be added

https://dune.xyz/queries/268631

https://docs.dune.xyz/

https://github.com/duneanalytics/abstractions/tree/master/polygon/dex/trades

add trades from DEX CoFix to Ethereum dex.trades table on Dune Analytics

see an example: https://github.com/duneanalytics/abstractions/blob/f935c24521ae260f3ada576daf5d66fc87e5bb2f/ethereum/dex/trades/insert_1inch_lp.sql

https://docs.dune.xyz/