Highest Polygon NFT Daily Sales
Leverage various abstraction layer tables for NFTs - apply this logic to any chain to discover trends for the currently trading NFT collections.
Which Polygon NFTs have the biggest sales volume each day?
Step 1: Select the table(s)
To answer this question, we need to join the data from the following tables: nft.polygon_nft_categories
, dex.polygon_dex_pools_v3
, nft.polygon_nft_washtrades
and nft.polygon_trades
.
Step 2: Find the answer
The SQL query should look like this:
with
polygon_nft_categories as (
select coalesce(address, pair_address) address,
coalesce(c.category, dex.category) category
from nft.polygon_nft_categories c
full join (
select distinct pair_address, 'DeFi' category
from dex.polygon_dex_pools_v3
) dex on c.address=dex.pair_address
)
,nft_washtrades as (
SELECT * FROM nft.polygon_nft_washtrades
WHERE is_wash_trade = true
)
select
*,
ROW_NUMBER() OVER (partition by tx_hash, washtrade ORDER BY amount_usd DESC) as rn
FROM
(
SELECT
DISTINCT
txfer.data_creation_date,
txfer.tx_hash,
txfer.nft_contract_address as collection_address,
CASE WHEN wt.transaction_hash IS NULL THEN false ELSE true END as washtrade
,amount_usd
,amount_original
,currency_symbol,
txfer.token_id
FROM nft.polygon_trades txfer
LEFT JOIN nft_washtrades wt
ON txfer.data_creation_date = wt.data_creation_date
AND txfer.tx_hash = wt.transaction_hash
LEFT JOIN polygon_nft_categories c ON txfer.nft_contract_address = c.address
WHERE txfer.data_creation_date BETWEEN current_date - interval '1' day and current_date
AND wt.data_creation_date BETWEEN current_date - interval '1' day and current_date
AND c.category is null or lower(c.category) != 'defi'
)
WHERE data_creation_date BETWEEN current_date - interval '1' day and current_date
AND collection_address NOT IN ('0x2953399124f0cbb46d2cbacd8a89cf0599974963')
ORDER BY amount_usd DESC,data_creation_date DESC
LIMIT 100
Step 3: Explore the results
The query will return the top 100 highest Polygon NFT sales from the past 24 hours.
The results will include the date and time of the sale, the transaction hash, the collection address, whether the sale was a wash trade or not, the amount in USD, the amount in the paid currency, and the token ID.
Step 4: Create an API to access query results
As the query returns the daily top sales, we want the API to refresh every 12 hours, in order not to miss out on any data.
And that’s it! Creating the API takes one click, and it’s ready!
Updated 6 months ago