denze-e7j2NU{{nft_contract_address}} % of Purchase Volume by Non-Holders
Updated 2023-05-30
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
›
⌄
⌄
/*
holder: wallet held a Captainz at the start of the day, and still holds at least 1 as of current date
*/
WITH
input_contracts AS (
SELECT
trim(F.value) AS nft_contract_address
FROM (
SELECT
SPLIT(data.nft_contract_address, ';') AS input
FROM VALUES
(lower('{{nft_contract_address}}')) -- Captainz
AS data(nft_contract_address)
) i
, Table(Flatten(i.input)) AS F
WHERE trim(F.value) regexp '^0x[0-9a-fA-F]{40}$' -- check address is a valid format, i.e. starts with 0x and has 42 characters total
)
-- get all nft transfers for the given collection(s)
, nft_transfers AS (
SELECT
t.nft_address AS nft_contract_address
, t.tokenid AS token_id
, t.block_timestamp AS block_time
, date_trunc('day', t.block_timestamp) AS day
, t.nft_from_address AS from_address
, t.nft_to_address AS to_address
, COALESCE(erc1155_value, 1) AS amount
, CASE WHEN erc1155_value is not null THEN 'true' ELSE 'false' END AS is_erc1155
, ROW_NUMBER() OVER (PARTITION BY t.nft_address, t.tokenid ORDER BY block_number DESC, event_index DESC) AS rank_desc
FROM ethereum.core.ez_nft_transfers t
INNER JOIN input_contracts c ON t.nft_address = c.nft_contract_address
)
Run a query to Download Data