FlippppppaMoving this!
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 Structure @ https://flipsidecrypto.xyz/h4wk/q/otc-kpi-6Aum0n
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
from solana.core.fact_events where succeeded = True
and program_id = '8guzmt92HbM7yQ69UJg564hRRX6N4nCdxWE5L6ENrA8P') events
using(tx_id, block_timestamp)
Run a query to Download Data