How to filter after group by in this Dune Analytics example?

Question

This dune analytics script is trying to 1) obtain the list of the most traded NFT projects in the last 30 days and 2) add a row of a single collection ("Wicked Ape Bone Club" - contract address = 0xbe6e3669464E7dB1e1528212F0BfF5039461CB82).

While it does add the specific collection to the top 30 table, it doesn't preserve the correct ranking. The ranking shouldn't be 1, and should be a lot lower. Apparently, the HAVING after GROUP BY happens before the aggregation. How can the second filtering (obtaining only the row with the single specified collection) be done after the aggregation?

Code below

(SELECT 
                ROW_NUMBER() OVER (ORDER BY SUM(original_amount) DESC) AS "Rank",
                CASE WHEN nftp."nft_collection" IS NOT NULL THEN CONCAT('<a href="',nftp.url,'" target="_blank">',nftp.nft_collection,'</a>') 
                ELSE CONCAT('<a href="https://etherscan.io/token/0',SUBSTRING("nft_contract_address"::text, 2),'">',CONCAT('0',SUBSTRING("nft_contract_address"::text, 2)),'</a>') END AS "OPENSEA",
                COUNT(tx_hash) AS "Sales",
                SUM(original_amount) AS "Volume ETH", 
                percentile_cont(.1) within GROUP (ORDER BY original_amount) AS "Floor Price",
                percentile_cont(.5) within GROUP (ORDER BY original_amount) AS "Median",
                nft_contract_address
            FROM nft.trades n 
                LEFT JOIN dune_user_generated."cryptuschrist_nft_projects2" nftp on nftp."nft_contract" = n.nft_contract_address
                    WHERE  (block_time > now() - interval  '30 day')  
                        AND number_of_items = 1 
                        AND original_currency in ('ETH','WETH')  
                        AND nft_contract_address NOT IN ('\xc36cf0cfcb5d905b8b513860db0cfe63f6cf9f5c', '\x4e1f41613c9084fdb9e34e11fae9412427480e56', '\x7bd29408f11d2bfc23c34f18275bbf23bb716bc7', '\xce25e60a89f200b1fa40f6c313047ffe386992c3', '\xfb3765e0e7ac73e736566af913fa58c3cfd686b7','\x495f947276749ce646f68ac8c248420045cb7b5e','\xc99f70bfd82fb7c8f8191fdfbfb735606b15e5c5','\xb932a70a57673d89f4acffbe830e8ed7f75fb9e0', '\x3b3ee1931dc30c1957379fac9aba94d1c48a5405')
                        GROUP BY nft_contract_address , nftp.nft_collection, nftp.url
                        HAVING nft_contract_address IN ('\xbe6e3669464E7dB1e1528212F0BfF5039461CB82')
                        )
                        
UNION
    (SELECT
            ROW_NUMBER() OVER (ORDER BY SUM(original_amount) DESC) AS "Rank",
            CASE WHEN nftp."nft_collection" IS NOT NULL THEN CONCAT('<a href="',nftp.url,'" target="_blank">',nftp.nft_collection,'</a>') 
            ELSE CONCAT('<a href="https://etherscan.io/token/0',SUBSTRING("nft_contract_address"::text, 2),'">',CONCAT('0',SUBSTRING("nft_contract_address"::text, 2)),'</a>') END AS "OPENSEA",
            COUNT(tx_hash) AS "Sales",
            SUM(original_amount) AS "Volume ETH", 
            percentile_cont(.1) within GROUP (ORDER BY original_amount) AS "Floor Price",
            percentile_cont(.5) within GROUP (ORDER BY original_amount) AS "Median",
            nft_contract_address
        FROM nft.trades n 
            LEFT JOIN dune_user_generated."cryptuschrist_nft_projects2" nftp on nftp."nft_contract" = n.nft_contract_address
                WHERE  (block_time > now() - interval  '30 day')  
                    AND number_of_items = 1 
                    AND original_currency in ('ETH','WETH')  
                    AND nft_contract_address NOT IN ('\xc36cf0cfcb5d905b8b513860db0cfe63f6cf9f5c', '\x4e1f41613c9084fdb9e34e11fae9412427480e56', '\x7bd29408f11d2bfc23c34f18275bbf23bb716bc7', '\xce25e60a89f200b1fa40f6c313047ffe386992c3', '\xfb3765e0e7ac73e736566af913fa58c3cfd686b7','\x495f947276749ce646f68ac8c248420045cb7b5e','\xc99f70bfd82fb7c8f8191fdfbfb735606b15e5c5','\xb932a70a57673d89f4acffbe830e8ed7f75fb9e0', '\x3b3ee1931dc30c1957379fac9aba94d1c48a5405')
                        GROUP BY nft_contract_address , nftp.nft_collection, nftp.url
                        ORDER BY 4 DESC
                        LIMIT '30'
                        ) 
ORDER BY "Rank" ASC

How to filter after group by in this Dune Analytics example?

0 Answers0