danwoods-mainheatmap attempt 2 copy
Updated 2024-10-07
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 Sandesh / heatmap attempt 2 @ https://flipsidecrypto.xyz/Sandesh/q/N-pxuJf1KrqS/heatmap-attempt-2
/* caveat on how it has been calulate d
Let's say an user buys in Jan 2024 and sells in Mar 2024
and then
buys again in July 2024 and sells in Nov 2024.
We now face a dilemna on whether we choose to say he joined in jan 2024 or in July 2024 and simlarly did he leave in mar 2024 or Nov 2024. Saying he joined in Jan and left in Nov, would be techniaclly wrong as he was not part of the community in
April, May, June 2024.
On the otherhand, we could consider both as geniune record. That is show that he joined in jan 2024 and left in March 2024, and another record for joining in July 2024 and exiting in Nov 2024. However, this too is wrong, cause this being an user retention chart,
we will end up double counting the user.
So we settle at a mid point, We define the user's time in community as
1. Now, if he currently holds the NFT. The join date will be when he bought it.
2. If he doesnt hold the NFT, then the time he has held on to the NFT longest is considered as his involvement
*/
with changes as
(
with dates as
(
select date_trunc('day',BLOCK_TIMESTAMP) as date from ethereum.core.fact_transactions
where date>='2023-04-06 00:00:00.000'
and date <= current_date
group by 1
),
buy_sell_table as
((
select
date_trunc('day',BLOCK_TIMESTAMP) as date,
nft_to_address as address ,
1 as amount
from ethereum.nft.ez_nft_transfers
where block_number>='16985982'
and nft_address=lower('0x1B41d54B3F8de13d58102c50D7431Fd6Aa1a2c48')
QueryRunArchived: QueryRun has been archived