seansheppard-4C2oMPopensee6
Updated 2022-08-01
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
WITH base AS
(SELECT TX_ID, BLOCK_TIMESTAMP, CONTRACT_ADDRESS, CONTRACT_NAME,
EVENT_INPUTS:to RECEIVER, EVENT_INPUTS:from SENDER, EVENT_INPUTS:value AMOUNT,
CASE
WHEN CONTRACT_ADDRESS = '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' THEN 'WETH'
WHEN CONTRACT_ADDRESS = '0x8f3cf7ad23cd3cadbd9735aff958023239c6a063' THEN 'DAI'
WHEN CONTRACT_ADDRESS = '0x70c006878a5a50ed185ac4c87d837633923de296' THEN 'REVV'
WHEN CONTRACT_ADDRESS = '0x2791bca1f2de4661ed88a30c99a7a9449aa84174' THEN 'USDC'
ELSE 'UNKNOWN' END AS SYMBOL
FROM flipside_prod_db.polygon.events_emitted
WHERE 1 = 1
AND EVENT_NAME = 'Transfer'
AND EVENT_INPUTS:from = LOWER('0xF715bEb51EC8F63317d66f491E37e7BB048fCc2d')
AND RECEIVER IN ('0x8de9c5a032463c561423387a9648c5c7bcc5bc90', '0x5b3256965e7c3cf26e11fcaf296dfc8807c01073')),
base_usd AS
(SELECT b.*, (b.AMOUNT/POWER(10,p.DECIMALS))*p.PRICE USD_AMOUNT, p.PRICE
FROM base b
LEFT JOIN ethereum.core.fact_hourly_token_prices p
ON p.HOUR = DATE_TRUNC('hour', b.BLOCK_TIMESTAMP) AND p.SYMBOL = b.SYMBOL),
usd_nft AS
(SELECT bu.*, ee.CONTRACT_ADDRESS NFT_ADDRESS
FROM base_usd bu
LEFT JOIN flipside_prod_db.polygon.events_emitted ee
ON bu.TX_ID = ee.TX_ID
WHERE ee.EVENT_INPUTS:tokenId IS NOT NULL
AND ee.EVENT_NAME = 'Transfer'),
top_100 AS
(SELECT NFT_ADDRESS, ROUND(SUM(USD_AMOUNT),2) USD_AMOUNT
FROM usd_nft
GROUP BY 1
ORDER BY 2 DESC
LIMIT 100)
SELECT NFT_ADDRESS, PROJECT_NAME, USD_AMOUNT::DECIMAL USD_AMOUNT
FROM
(SELECT n.*, l.PROJECT_NAME