from solana.core.fact_token_prices_hourly

where RECORDED_HOUR::date >= '2022-10-01'

and symbol = 'SOL'

group by 1,2)

,

sales as ( select date(block_timestamp) as date, case when block_timestamp::date <= '2023-01-01' then 'Before New Year'

else 'After New Year' end as date_type, tx_id, purchaser,seller, a.mint, sales_amount, sales_amount*avg_price as volume, avg_price

from solana.core.fact_nft_sales a left outer join sol_price b on a.block_timestamp::date = b.date

join solana.core.dim_nft_metadata c on a.mint = c.mint

where block_timestamp::date >= '2022-10-01'

and project_name = '{{Collection}}'

and SUCCEEDED = 'TRUE')

,

before_buy as ( select purchaser , count(DISTINCT(mint)) as total_buy

from sales

where date_type = 'Before New Year'

group by 1)

,

befor_sell as ( select seller , count(DISTINCT(mint)) as total_sell

from sales

where date_type = 'Before New Year'

group by 1)

,

gods_top_ as (select purchaser as buyer_address, total_buy-total_sell as current_balance

from before_buy a join befor_sell b on a.purchaser = b.seller

order by 2 desc

limit 5)

,

after_buy as ( select purchaser , count(DISTINCT(mint)) as total_buy

from sales

where date_type = 'After New Year'

group by 1)

,

after_sell as ( select seller , count(DISTINCT(mint)) as total_sell

from sales