SalehBadger Network Index copy
999
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
32
33
34
35
36
›
⌄
-- forked from danner / Badger Network Index @ https://flipsidecrypto.xyz/danner/q/badge-index-95qVz0
---------------------------------------------------------------------------------------------------------------------------
-- get all possible badge pairs
WITH networks AS (
SELECT
tx_hash,
CONCAT('0x', RIGHT(topics [1] :: STRING, 40)) AS network_address
FROM
polygon.core.fact_event_logs
WHERE
block_timestamp :: DATE > '2022-10-16' :: DATE
AND contract_Address = LOWER('0x218b3c623ffb9c5e4dbb9142e6ca6f6559f1c2d6')
AND origin_function_signature = '0x7b366213'
),
badge_creation as (
select
tx_hash,
contract_address as network_address,
ethereum.public.udf_hex_to_int(topics[1]::string)::int as badge_id
from polygon.core.fact_event_logs
where contract_address in (select network_address from networks)
and block_timestamp > '2022-10-15'
and origin_function_signature = '0x78677a8d'
and contract_address != '0x0000000000000000000000000000000000001010'
qualify rank() over (partition by network_address, badge_id order by block_number, event_index desc) = 1
),
---------------------------------------------------------------------------------------------------------------------------
-- get badge pair transfers for users
transfers as (
SELECT
block_timestamp,
nft_address,
project_name,
tokenid,
erc1155_value,
Run a query to Download Data