headitmanagerGiven prizes of each NFT category
Updated 2022-06-22
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with mint_price_daily as (select avg(mint_price_usd) as avg_price,nft_address,block_timestamp::date as date from ethereum.core.ez_nft_mints
where mint_price_usd>0 and nft_address in (lower('0x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
,lower('0x3545192b340F50d77403DC0A64cf2b32F03d00A9'),
lower('0x90B3832e2F2aDe2FE382a911805B6933C056D6ed'))
group by nft_address,block_timestamp::date)
, prize_count as (select count(*) as pz,
case
when nft_address=lower('0x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523') then 'Judge'
when nft_address=lower('0x3545192b340F50d77403DC0A64cf2b32F03d00A9') then 'Lawyer'
when nft_address=lower('0x90B3832e2F2aDe2FE382a911805B6933C056D6ed') then 'Supporter'
end as nft_name
from ethereum.core.ez_nft_mints
where
nft_address in (lower('0x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
,lower('0x3545192b340F50d77403DC0A64cf2b32F03d00A9'),
lower('0x90B3832e2F2aDe2FE382a911805B6933C056D6ed'))
and mint_price_eth =0 and block_timestamp::date > getdate() - interval '60 days'
group by nft_name)
, prize_usd_value as (
select sum(avg_price) ,
case
when mint_price_daily.nft_address=lower('0x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523') then 'Judge'
when mint_price_daily.nft_address=lower('0x3545192b340F50d77403DC0A64cf2b32F03d00A9') then 'Lawyer'
when mint_price_daily.nft_address=lower('0x90B3832e2F2aDe2FE382a911805B6933C056D6ed') then 'Supporter'
end as nft_name
from ethereum.core.ez_nft_mints inner join mint_price_daily
on mint_price_daily.date=ethereum.core.ez_nft_mints.block_timestamp::date
and mint_price_daily.nft_address=ethereum.core.ez_nft_mints.nft_address
where
ethereum.core.ez_nft_mints.nft_address in (lower('0x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
,lower('0x3545192b340F50d77403DC0A64cf2b32F03d00A9'),
lower('0x90B3832e2F2aDe2FE382a911805B6933C056D6ed'))
and mint_price_eth =0 and block_timestamp::date > getdate() - interval '60 days'
group by nft_name
)
Run a query to Download Data