Last active
April 6, 2022 20:51
-
-
Save ameensol/52d81acdbf554d179b6cb3450aa77173 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
SUM (fees) | |
FROM ( | |
SELECT | |
*, | |
eth * eth_price / 1e18 AS trade_volume, | |
eth * eth_price * 0.003 / 1e18 AS fees | |
FROM ( | |
SELECT distinct | |
a.hash, | |
a.block_number AS spank_block_number, | |
first_value(b.block_number) over w AS dai_block_number, | |
first_value(b.eth_price) over w AS eth_price, | |
first_value(eth) over w as eth | |
FROM ( | |
SELECT | |
trades.hash, | |
trades.block_number, | |
trades.spank, | |
CASE trades.eth_sold WHEN 0 THEN trades.eth_bought | |
ELSE trades.eth_sold | |
END AS eth | |
FROM ( | |
SELECT | |
transactions.hash, | |
transactions.value AS eth_sold, | |
traces.value AS eth_bought, | |
traces.input, | |
transactions.block_number, | |
spank_tx.value AS spank | |
FROM `bigquery-public-data.ethereum_blockchain.transactions` AS transactions | |
JOIN `bigquery-public-data.ethereum_blockchain.token_transfers` AS spank_tx ON (transactions.hash = spank_tx.transaction_hash) | |
JOIN `bigquery-public-data.ethereum_blockchain.traces` AS traces ON (transactions.hash = traces.transaction_hash) | |
WHERE transactions.to_address = '0x4e395304655f0796bc3bc63709db72173b9ddf98' | |
AND spank_tx.token_address = '0x42d6622dece394b54999fbd73d108123806f6a18' | |
AND traces.value > 0 | |
AND traces.from_address = '0x4e395304655f0796bc3bc63709db72173b9ddf98' | |
AND transactions.block_timestamp >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 60 day)) | |
ORDER BY transactions.block_number ASC | |
) AS trades | |
WHERE trades.input NOT LIKE '0x422f1043%' | |
AND trades.input NOT LIKE '0xf88bf15a%' | |
) AS a | |
LEFT JOIN ( | |
SELECT | |
transactions.block_number, | |
CAST(dai_tx.value AS numeric) / transactions.value AS eth_price | |
FROM `bigquery-public-data.ethereum_blockchain.transactions` AS transactions | |
JOIN `bigquery-public-data.ethereum_blockchain.token_transfers` AS dai_tx ON (transactions.hash = dai_tx.transaction_hash) | |
WHERE transactions.to_address = '0x09cabec1ead1c0ba254b09efb3ee13841712be14' | |
AND transactions.value > 0 | |
AND dai_tx.token_address = '0x89d24a6b4ccb1b6faa2625fe562bdd9a23260359' | |
) AS b | |
ON TRUE | |
WHERE a.block_number >= b.block_number | |
window w as ( | |
partition by a.hash, a.block_number | |
order by b.block_number desc | |
) | |
ORDER BY spank_block_number ASC, dai_block_number DESC | |
) AS report | |
) AS report2 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
https://console.cloud.google.com/bigquery