amelia-leeUntitled Query
Updated 2022-08-01
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
›
⌄
with c as(with a as(SELECT BLOCK_TIMESTAMP,TX_JSON:receipt:logs[0]:decoded:inputs:to::string as users,TX_JSON:receipt:logs[0]:address::string as contract,
TX_JSON:receipt:logs[0]:decoded:inputs:value::float as amount from polygon.core.fact_transactions
where block_timestamp>=CURRENT_DATE - 30 and STATUS='SUCCESS' and TX_JSON:receipt:logs[0]:decoded:inputs:from::string=lower('0x0000000000000000000000000000000000000000')
and FROM_ADDRESS='0x0000000000000000000000000000000000000000' and to_ADDRESS='0x0000000000000000000000000000000000000000'),
b as(SELECT tx.origin_from_address,min(tx.block_timestamp) as first_event_time FROM polygon.core.fact_event_logs tx
JOIN a ON tx.origin_from_address = a.users AND date_trunc('hour', tx.block_timestamp) = date_trunc('hour', a.block_timestamp)
WHERE tx.block_timestamp > a.BLOCK_TIMESTAMP GROUP BY 1)
SELECT ORIGIN_TO_ADDRESS,count(ORIGIN_TO_ADDRESS) as usage
FROM polygon.core.fact_event_logs tx JOIN b
ON tx.origin_from_address = b.origin_from_address AND tx.block_timestamp = b.first_event_time GROUP BY 1),
d as(SELECT ADDRESS,PROJECT_NAME from polygon.core.dim_labels)
SELECT distinct PROJECT_NAME,usage from c join d on c.ORIGIN_TO_ADDRESS=d.ADDRESS where PROJECT_NAME!='hop protocol'
order by 2 desc
--neyshaa
Run a query to Download Data