NFT Volume

This query returns the STX volume of transactions with Megapont Apes transfers.

with nft as (
        select split_part(split_part(asset_identifier,'.',2),'::',1)as name,
          sender_address,
          split_part(asset_identifier,'::',1) as identifier,
          block_time,
          tx_id
          from nft_events 
          join transactions t using(tx_id)
          where asset_identifier  like '%megapont-ape-club%'
          and asset_event_type_id=1
          and t.block_time > now()- interval '120 days' 
          and t.status=1
          group by 1, 2, 3, 4, 5
        )
        select  block_time::date as period,
            sum(amount)/10^6 as volume
            from stx_events ea 
              join nft on ea.tx_id=nft.tx_id 
            where ea.asset_event_type_id=1
              group by 1
              order by 1