seansheppard-4C2oMPopensee7
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
›
⌄
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)
SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) DAY, ROUND(AVG(USD_AMOUNT),2) AVG_USD, COUNT(DISTINCT TX_ID) TX, ROUND(SUM(USD_AMOUNT),2) SUM_USD
FROM base_usd
GROUP BY 1
Run a query to Download Data