FlippppppaOTC KPI copy
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
›
⌄
-- forked from h4wk / OTC KPI @ https://flipsidecrypto.xyz/h4wk/q/otc-daily-6-months-E_Otbx
-- forked from OTC daily 6 months @ https://flipsidecrypto.xyz/edit/queries/a3b124cd-833c-41d4-af39-6155a61df25e
-- forked from OTC Base @ https://flipsidecrypto.xyz/edit/queries/3a26b1ed-d6e9-4159-a277-ce6c5584d70e
with daily_avg_price as (
select block_timestamp::date as price_date,
label as collection,
avg(sales_amount) as price
from solana.core.fact_nft_sales
join solana.core.dim_labels on mint = address
where price_date >= CURRENT_DATE - 180
and succeeded = True
group by 1,2
)
, base as (
select tx.block_timestamp,
tx_id,
signers[0] as signer,
case when signer = tx_to then 'receiver'
when tx_to = '98Ni7vVRR1tggtWWruPVcfFXHTH11bPbNryJZGkCGvaD' then 'fees_receiver'
else 'offeror' end as user_type,
tx_to as trader,
amount,
case when label = 'wrapped sol' then amount
else amount*price end as volume_sol,
mint,
label as collection,
case when label = 'wrapped sol' then 'SOL'
else 'NFT' end as trade_type
from solana.core.fact_transactions tx
join lateral flatten (input => log_messages) logs
join (select distinct block_timestamp, tx_id
Run a query to Download Data