rain_syndicaCEX Users Collections Bought October
Updated 2023-12-14
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 CEX Users Collections Bought November @ https://flipsidecrypto.xyz/edit/queries/361244b8-b026-4e3f-ba42-e02d0b326148
with
cex_users as (
select
t.tx_to::string AS user,
min(t.block_timestamp::datetime) AS bridge_date
from solana.core.fact_transfers t
inner join crosschain.core.dim_labels l on t.tx_from = l.address
-- left join solana.core.dim_tokens d on t.mint = d.token_address
-- left join solana.price.fact_token_prices_hourly b
-- on d.coin_market_cap_id = b.id
-- and date_trunc('hour', t.block_timestamp) = b.recorded_hour
where l.blockchain = 'solana'
and l.label_type = 'cex'
and t.amount > 0
and t.block_timestamp >= '2023-10-01'
and t.block_timestamp < '2023-11-01'
group by 1
),
base_sol_to as (
select distinct tx_id, index
from solana.core.fact_events
where block_timestamp >= '2023-10-01' and block_timestamp < '2023-11-01'
and program_id = 'dst5MGcFPoBeREFAA5E3tU5ij8m5uVYwkzkSAbsLbNo'
and signers[0] = '7FfB2zQRYUQwpPzkRxAeg2mCBGeCRKp4PCEeULJA9xTo'
),
to_solana as (
select block_timestamp::date as date,
tx_to as user_address,
t.tx_id
from solana.core.fact_transfers t
join base_sol_to b on (t.tx_id = b.tx_id)
Run a query to Download Data