-- 按时间排序，找出该合约最近的10笔交易
with lastest_trades as (
select *
from nft.trades
where nft_contract_address = 0xed5af388653567af2f388e6224dc7c4b3241c544 -- azuki NFT的合约地址
-- and block_time > now() - interval '24' hour --你也可以按时间排序
order by block_time desc
limit 10
)
select min(amount_original) as floor_price --直接获取最小值
-- percentile_cont(.05) within GROUP (order by amount_original) as floor_price --这么做是取最低和最高价之间5%分位数，防止一些过低的价格交易影响
from lastest_trades
where currency_symbol IN ('ETH', 'WETH')
and cast(number_of_items as integer) = 1 -- 这里可以按不同的链，不同的交易token进行过滤
参考链接：https://dune.com/queries/1660139
成交量、总成交额度、总交易笔数等、24小时/7天/1月成交额度
with total_volume as(
SELECT
sum(amount_original) as "Total Trade Volume(ETH)", --总成交量ETH
sum(amount_usd) as "Total Trade Volume(USD)", --总成交量USD
count(amount_original) as "Total Trade Tx" --总交易笔数
FROM nft.trades
WHERE nft_contract_address = 0xed5af388653567af2f388e6224dc7c4b3241c544
-- AND currency_symbol IN ('ETH', 'WETH')
),
total_fee as (
select
sum(royalty_fee_amount) as "Total Royalty Fee(ETH)", --总版权税ETH
sum(royalty_fee_amount_usd) as "Total Royalty Fee(USD)", --总版权税USD
sum(platform_fee_amount) as "Total Platform Fee(ETH)", --总平台抽成ETH
sum(platform_fee_amount_usd) as "Total Platform Fee(USD)" --总平台抽成USD
from nft.fees
WHERE nft_contract_address = 0xed5af388653567af2f388e6224dc7c4b3241c544
-- AND royalty_fee_currency_symbol IN ('ETH', 'WETH')
)
select * from total_volume, total_fee
参考链接：https://dune.com/queries/1660292
每日/每月/每周成交量
with hourly_trade_summary as (
select date_trunc('day', block_time) as block_date,
sum(number_of_items) as items_traded,
sum(amount_raw) / 1e18 as amount_raw_traded,
sum(amount_usd) as amount_usd_traded
from opensea.trades
where nft_contract_address = 0xed5af388653567af2f388e6224dc7c4b3241c544
-- and block_time > now() - interval '90' day
group by 1
order by 1
)
select block_date,
items_traded,
amount_raw_traded,
amount_usd_traded,
sum(items_traded) over (order by block_date asc) as accumulate_items_traded,
sum(amount_raw_traded) over (order by block_date asc) as accumulate_amount_raw_traded,
sum(amount_usd_traded) over (order by block_date asc) as accumulate_amount_usd_traded
from hourly_trade_summary
order by block_date
参考链接：https://dune.com/queries/1664420
当前持有人数，总token数量，holder的分布等
with nft_trade_details as ( --获取交易的买入卖出方详细信息表，卖出方是负数，买入方是
select seller as trader,
-1 * cast(number_of_items as integer) as hold_item_count
from nft.trades
where nft_contract_address = 0xed5af388653567af2f388e6224dc7c4b3241c544
union all
select buyer as trader,
cast(number_of_items as integer) as hold_item_count
from nft.trades
where nft_contract_address = 0xed5af388653567af2f388e6224dc7c4b3241c544
),
nft_traders as (
select trader,
sum(hold_item_count) as hold_item_count
from nft_trade_details
group by trader
having sum(hold_item_count) > 0
order by 2 desc
),
nft_traders_summary as (
select (case when hold_item_count >= 100 then 'Hold >= 100 NFT'
when hold_item_count >= 20 and hold_item_count < 100 then 'Hold 20 - 100'
when hold_item_count >= 10 and hold_item_count < 20 then 'Hold 10 - 20'
when hold_item_count >= 3 and hold_item_count < 10 then 'Hold 3 - 10'
else 'Hold 1 or 2 NFT'
end) as hold_count_type,
count(*) as holders_count
from nft_traders
group by 1
order by 2 desc
),
total_traders_count as (
select count(*) as total_holders_count,
max(hold_item_count) as max_hold_item_count
from nft_traders
),
total_summary as (
select
0 as total_nft_count,
count(*) as transaction_count,
sum(number_of_items) as number_of_items_traded,
sum(amount_raw) / 1e18 as eth_amount_traded,
sum(amount_usd) as usd_amount_traded
from opensea.trades
where nft_contract_address = 0xed5af388653567af2f388e6224dc7c4b3241c544
)
select *
from nft_traders_summary
join total_traders_count on true
join total_summary on true
