primo_datasolana_aurory
Updated 2022-07-21
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
›
⌄
--Aurory is one of the most highly anticipated games to come out on any chain. Create a dashboard highlighting wallet trends around both the tokens and NFTs.
-- 1. Does the price of Aurory token has positive correlation with volume of nft sales?
with md as (
select distinct mint, project_name project
from solana.core.dim_nft_metadata
where project_name = 'Aurory'
),
sales as (
select date(s.block_timestamp) as dt
,count(distinct s.purchaser) as total_purchasers_cts
,count(distinct s.seller) as total_seller_cts
,count(distinct s.tx_id) as total_sale_txns
,sum(s.sales_amount) as total_sale_volume_sol
,sum(s.sales_amount * cast(0.05 as float)) as total_commission_volume_sol
,median(s.sales_amount) as median_sale_price_sol
,sum(total_commission_volume_sol) over (order by dt asc) as cum_comission_txns
from solana.core.fact_nft_sales s
inner join md
on s.mint = md.mint
group by 1
),
sol_price as (
select date(hour) dt, avg(price) avg_sol_usd FROM ethereum.core.fact_hourly_token_prices -- symbol
where TOKEN_ADDRESS = LOWER('0xD31a59c85aE9D8edEFeC411D448f90841571b89c') -- SOL
group by 1
),
aury_price as (
select date(block_timestamp) dt
, avg(swap_to_amount/ swap_from_amount) avg_aury_usd
from solana.core.fact_swaps
where swap_from_mint = 'AURYydfxJib1ZkTir1Jn1J9ECYUtjb6rKQVmtYaixWPP' -- AURY
and swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' -- USDC
and date(block_timestamp) > date('2021-12-01')
and swap_from_amount > 1
group by 1
Run a query to Download Data