L AS ( select * from (VALUES

( 'BNB','Pandra King','0xf250bf5953b601e42e93226f7a9e4e8b9e7435af'),

( 'Polygon','Pandra King Polygon','0x216c6bcffa9481af69f8c2f5a6d1095c1f075e92')

) as l1(chain, nft_name, address))

select nft_to_address as "NFT Holder",

sum(nft_cnt) as "Number of NFT Holding"

(select nft_to_address,

sum(nft_count) as nft_cnt

from

(select

NFT_TO_ADDRESS,

count(distinct TOKENID) as nft_count

--max(BLOCK_TIMESTAMP) over (partition by TOKENID) as last_day_transfer

from bsc.nft.ez_nft_transfers

where NFT_ADDRESS in (select address from L)

and NFT_TO_ADDRESS != '0x0000000000000000000000000000000000000000'

group by 1

union all

select

NFT_FROM_ADDRESS,

-count(distinct TOKENID) as nft_count

--max(BLOCK_TIMESTAMP) over (partition by TOKENID) as last_day_transfer

from bsc.nft.ez_nft_transfers

where NFT_ADDRESS in (select address from L)

and NFT_FROM_ADDRESS != '0x0000000000000000000000000000000000000000'

group by 1

)