banterlyticskongium-com-recent
Updated 2024-03-07
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
›
⌄
-- forked from kongium-com @ https://flipsidecrypto.xyz/edit/queries/781a7240-f43b-4498-bc75-ecf7b16903f7
with boundaries as
(select
ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0])::integer as season,
ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1])/((pow(10,18))) as prize,
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 > '2023-12-15'
and contract_address = '0x70c575588b98c1f46b1382c706adaf398a874e3e'
and topics[0]::string = '0x9b492ca62dd845faf66276dbd14ed82e0d819d8b172cb51433ffe4268ab3dd9e')
SELECT boundaries.season,
date_trunc('hour', ftt.block_timestamp) as day1,
COALESCE(sum(case when topics[0]::string = lower('0xf5f311a0329198906d6b8a4b641bb2ae4cfcfa61c2a5d4caf9f9bce29711fd9b') then (ethereum.public.udf_hex_to_int(DATA::string)) end),0) as kongium_com
FROM polygon.core.fact_event_logs ftt
LEFT OUTER JOIN boundaries ON
ftt.block_timestamp >= boundaries.block_timestamp AND
ftt.block_timestamp < boundaries.next_timestamp
WHERE ftt.block_timestamp::date > '2023-12-15'
and contract_address in (lower('0x1421b4337ce370a389cf4e45a6b870487574006e'),lower('0x70c575588b98c1f46b1382c706adaf398a874e3e'))
and season is not null
group by 1,2
order by 1 asc, 2 asc
QueryRunArchived: QueryRun has been archived