Sandeshsharp-red
Updated 2024-12-02Copy Reference Fork
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
32
33
34
35
36
›
⌄
⌄
/*
The main goal is to classify transactions involving a specific NFT contract, focusing on identifying users who have exited their holdings or partially sold their assets.
*/
WITH date_range AS (
-- Extract a list of distinct transaction dates within the specified range
SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) AS transaction_date
FROM ethereum.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2021-12-12 00:00:00.000'
AND BLOCK_TIMESTAMP <= CURRENT_DATE
GROUP BY 1
),
nft_transactions AS (
-- Capture buy transactions, assigning a positive transaction amount
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS transaction_date,
tx_hash,
block_timestamp,
block_number,
nft_to_address AS wallet_address,
1 AS transaction_amount
FROM ethereum.nft.ez_nft_transfers
WHERE block_timestamp >= '2021-12-12 00:00:00.000'
AND nft_address = LOWER('0x49cf6f5d44e70224e2e23fdcdd2c053f30ada28b')
UNION ALL
-- Capture sell transactions, assigning a negative transaction amount
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS transaction_date,
tx_hash,
block_timestamp,
block_number,
nft_from_address AS wallet_address,
QueryRunArchived: QueryRun has been archived