banterlyticstest 111 copy
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
›
⌄
with boundaries as
(select
ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0])::integer as season,
block_timestamp,
COALESCE(LEAD(block_timestamp) OVER (ORDER BY block_timestamp),to_timestamp(100000000000)) as next_timestamp
from polygon.core.fact_event_logs
where block_timestamp::date > '2022-03-01'
and contract_address = '0x70c575588b98c1f46b1382c706adaf398a874e3e'
and topics[0]::string = '0x9b492ca62dd845faf66276dbd14ed82e0d819d8b172cb51433ffe4268ab3dd9e')
SELECT boundaries.season,
date_trunc('hour', ftt.block_timestamp) as day1,
--count(case when SUBSTR(INPUT, 1, 10) = lower('0x11237ba0') then TX_HASH end) as srun,
--coalesce(sum(case when SUBSTR(INPUT, 1, 10) = lower('0xd422b242') then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(INPUT, 11, len(INPUT)), '.{64}')[2]) end),0) as brun,
coalesce(count(case when SUBSTR(INPUT, 1, 10) = lower('0x11237ba0') then TX_HASH end),0)+coalesce(coalesce(sum(case when SUBSTR(INPUT, 1, 10) = lower('0xd422b242') then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(INPUT, 11, len(INPUT)), '.{64}')[2]) end),0),0) as tot_run
FROM polygon.core.fact_traces ftt
LEFT OUTER JOIN boundaries ON
ftt.block_timestamp >= boundaries.block_timestamp AND
ftt.block_timestamp < boundaries.next_timestamp
WHERE to_address = lower('0x70c575588b98c1f46b1382c706adaf398a874e3e')
and tx_status = 'SUCCESS'
AND ftt.block_timestamp::date > '2022-03-01'
and SUBSTR(INPUT, 1, 10) in (lower('0x11237ba0'),lower('0xd422b242'))
and season = {{season}}
group by 1,2
order by 1 asc;
Run a query to Download Data