SandeshNFT retention heatmap
Updated 2024-10-09
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
›
⌄
⌄
-- User-defined variables for easy configuration
-- NFT_CONTRACT_ADDRESS: The address of the NFT contract being analyzed
-- ANALYSIS_START_DATE: The start date of the analysis period
-- ANALYSIS_END_DATE: The end date of the analysis period
-- INITIAL_BLOCK_NUMBER: The initial block number for the analysis
/*
This query analyzes user retention for NFT holders, focusing on the NFT contract defined by NFT_CONTRACT_ADDRESS.
It tracks buying, selling, and holding patterns, categorizing users based on their interactions.
Key features:
1. Tracks daily balance changes for each address
2. Categorizes user actions (new, returning, existing, exited, etc.)
3. Calculates retention periods in months
4. Handles intraday exchanges separately
5. Generates a comprehensive retention matrix
Note on retention calculation:
For users with multiple buy-sell cycles, we consider their longest holding period
as their primary involvement to avoid double-counting in the retention chart.
This approach allows us to capture the most significant engagement period for each user,
providing a more accurate representation of user retention over time.
*/
-- CTE to track daily balance changes and user actions
WITH changes AS (
-- Subquery to get all relevant dates
-- This ensures we have a continuous date range for our analysis
WITH dates AS (
SELECT date_trunc('day', BLOCK_TIMESTAMP) AS date
FROM ethereum.core.fact_transactions
WHERE date >= '2023-04-06'::DATE -- ANALYSIS_START_DATE
AND date <= CURRENT_DATE() -- ANALYSIS_END_DATE
GROUP BY 1
),
QueryRunArchived: QueryRun has been archived