SEASON | CYBER_FRAGMENT | RAINBOW_CRYSTAL | PROMETHEAN_RELIC | CHARMZ_CLAIMED | CYBER_FRAGMENTB | RAINBOW_CRYSTALB | PROMETHEAN_RELICB | CHARMZ_BURNT | |
---|---|---|---|---|---|---|---|---|---|
1 | 65 | 111 | 31 | 4 | 146 | 23 | 4 | 2 | 29 |
2 | 64 | 1539 | 552 | 11 | 2102 | 200 | 105 | 1 | 306 |
3 | 63 | 1221 | 402 | 14 | 1637 | 214 | 103 | 2 | 319 |
4 | 62 | 1073 | 325 | 9 | 1407 | 177 | 91 | 0 | 268 |
5 | 61 | 1026 | 331 | 19 | 1376 | 181 | 56 | 1 | 238 |
6 | 60 | 652 | 215 | 15 | 882 | 173 | 74 | 0 | 247 |
7 | 59 | 1054 | 336 | 15 | 1405 | 211 | 127 | 1 | 339 |
8 | 58 | 1428 | 450 | 15 | 1893 | 215 | 113 | 13 | 341 |
9 | 57 | 469 | 168 | 5 | 642 | 135 | 59 | 3 | 197 |
10 | 56 | 600 | 189 | 8 | 797 | 180 | 63 | 1 | 244 |
11 | 55 | 540 | 149 | 10 | 699 | 238 | 45 | 0 | 283 |
12 | 54 | 500 | 155 | 10 | 665 | 205 | 55 | 0 | 260 |
13 | 53 | 509 | 151 | 8 | 668 | 234 | 104 | 1 | 339 |
14 | 52 | 495 | 142 | 5 | 642 | 334 | 149 | 8 | 491 |
15 | 51 | 869 | 274 | 9 | 1152 | 441 | 317 | 7 | 765 |
16 | 50 | 1283 | 416 | 15 | 1714 | 790 | 316 | 13 | 1119 |
17 | 49 | 1373 | 423 | 19 | 1815 | 661 | 311 | 10 | 982 |
18 | 48 | 3226 | 1017 | 44 | 4287 | 766 | 295 | 10 | 1071 |
19 | 47 | 1012 | 329 | 17 | 1358 | 998 | 323 | 11 | 1332 |
20 | 46 | 996 | 345 | 17 | 1358 | 795 | 248 | 10 | 1053 |
banterlyticscharmz
Updated 2024-02-18
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
›
⌄
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 BETWEEN '2022-03-14 16:40:20.000' and '2024-01-06 06:52:00.000'
and contract_address = '0x70c575588b98c1f46b1382c706adaf398a874e3e'
and topics[0]::string = '0x9b492ca62dd845faf66276dbd14ed82e0d819d8b172cb51433ffe4268ab3dd9e')
SELECT boundaries.season,
COALESCE(sum(case when topics[2]= lower('0x0000000000000000000000000000000000000000000000000000000000000000') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 1 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Cyber_Fragment,
COALESCE(sum(case when topics[2]= lower('0x0000000000000000000000000000000000000000000000000000000000000000') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 2 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Rainbow_Crystal,
COALESCE(sum(case when topics[2]= lower('0x0000000000000000000000000000000000000000000000000000000000000000') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 3 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Promethean_Relic,
CYBER_FRAGMENT+RAINBOW_CRYSTAL+PROMETHEAN_RELIC as charmz_claimed,
COALESCE(sum(case when topics[3]= lower('0x000000000000000000000000000000000000000000000000000000000000dead') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 1 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Cyber_Fragmentb,
COALESCE(sum(case when topics[3]= lower('0x000000000000000000000000000000000000000000000000000000000000dead') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 2 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Rainbow_Crystalb,
COALESCE(sum(case when topics[3]= lower('0x000000000000000000000000000000000000000000000000000000000000dead') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 3 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Promethean_Relicb,
CYBER_FRAGMENTB+RAINBOW_CRYSTALB+PROMETHEAN_RELICB as charmz_burnt
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 BETWEEN '2022-03-14 16:40:20.000' and '2024-01-06 06:52:00.000'
and contract_address= lower('0x7cbccc4a1576d7a05eb6f6286206596bcbee14ac')
and season is not null
group by 1
order by 1 desc
Last run: 12 months ago
65
2KB
176s