azerbaijanCopy of Untitled Query
Updated 2022-12-07Copy Reference Fork
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
›
⌄
with mint_date as (
select
coalesce(min(block_timestamp),
(select min(block_timestamp) from solana.core.fact_nft_mints)) as first_mint
from solana.core.fact_nft_mints a join solana.core.dim_labels b on a.mint = b.address
where label_type = 'nft'
and succeeded = 'TRUE'
and label ='claynosaurz'),
sol_price as (
select
date_trunc('hour',block_timestamp) as date_hour,
case when swap_to_mint = 'So11111111111111111111111111111111111111112' then swap_to_mint else swap_from_mint end as token_address,
sum(case when swap_to_mint = 'So11111111111111111111111111111111111111112' then swap_from_amount else swap_to_amount end) as stable_amount,
sum(case when swap_to_mint = 'So11111111111111111111111111111111111111112' then swap_to_amount else swap_from_amount end) as token_amount,
stable_amount / token_amount as price
from solana.core.fact_swaps swaps
where succeeded = 'TRUE'
and block_timestamp >= (select * from mint_date)
and datediff('month',block_timestamp,current_date()) <= 90
and (swap_from_mint = 'So11111111111111111111111111111111111111112'
or swap_to_mint = 'So11111111111111111111111111111111111111112')
and (swap_from_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB')
or swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB'))
group by 1,2),
nft_sales as (
select
block_timestamp,
labels.address as token,
mint,
seller,
purchaser,
sales_amount as amount_sol,
sales_amount * price as amount_usd
from solana.core.fact_nft_sales sales join solana.core.dim_labels labels on sales.mint = labels.address and labels.label_type = 'nft' and labels.label ='claynosaurz'
Run a query to Download Data