duneanalytics / spellbook Goto Github PK
View Code? Open in Web Editor NEWSQL views for Dune
License: Other
SQL views for Dune
License: Other
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
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).
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 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
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
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.
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!
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 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
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
In this Transfer
event query, the value
is 1000000000000000000
. But in etherscan, it's 999999999999999999
.
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
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
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
check out this query, might be helpful
https://dune.xyz/queries/283658
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
How to find the all tables in 'ethereum' ,which now do not have all table ,for example uniswap_v2??
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
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://github.com/duneanalytics/abstractions/tree/master/polygon/dex/trades
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
Here is a query corresponding to this transaction demonstrating that a "single" token swap is being counted 6x in the dex.trades
table.
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).
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
Query to produce the results: https://dune.xyz/queries/434904
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)
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:
Similarly, we have the same issue with the prices."usd" which may not be as simple to amend.
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
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
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
Popular assets to list:
UST
ENS
OHM
LTO
RUNE
LUNA
ILV
OGN
SOS
GAS (GAS DAO)
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
Add more INDEXES to 'hex' dataset tables.
like on stakeId and for table: HEX_evt_Transfer columns: to/from
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
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?
https://dune.xyz/queries/301879
Transaction with hash \x7184a4960605d477471f7fddf2dec18f95ed737ad6eec2f7c75568829aeaf1d9
appears twice in ethereum.transactions.
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
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.
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.
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
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
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.
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
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 ✌️
The table view "insert_weth_balance_changes" is querying the table zeroex."WETH9_evt_Withdrawal" but is unclear how this table is constructed?
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.
https://etherscan.io/token/0x3b484b82567a09e2588a13d54d032153f0c0aee0
Similarly, ethereum.contracts
is missing this address as well. It's both the SOS token and the OpenDAO contract.
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
There's a few tokens in prices.usd
that have inconsistent symbol
s 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?
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
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
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
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
ticker: GIV
contract address: 0xf6537fe0df7f0cc0985cf00792cc98249e73efa0
decimals: 8
ticker: FUSE
contract address: 0x970B9bB2C0444F5E81e9d0eFb84C8ccdcdcAf84d
decimals: 18
ticker: ALN
contract address: 0x1b7b723e66a0dd33e8f5e8ec039b51acafcd66a5
decimals: 18
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"
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.