1with daily_contracts as (
2select 'polygon' as chain, year,month,day,count(*) as nr_contracts, count(distinct bytecode) as nr_distinct_bytecodes
3from @blt.polygon_contracts.contracts
4where year=2025
5group by all
6union all
7select 'ethereum' as chain, year,month,day,count(*) as nr_contracts, count(distinct bytecode) as nr_distinct_bytecodes
8from @blt.ethereum_contracts.contracts
9where year=2025
10group by all
11union all
12select 'avalanche' as chain, year,month,day,count(*) as nr_contracts, count(distinct bytecode) as nr_distinct_bytecodes
13from @blt.avalanche_contracts.contracts
14where year=2025
15group by all
16union all
17select 'arbitrum' as chain, year,month,day,count(*) as nr_contracts, count(distinct bytecode) as nr_distinct_bytecodes
18from @blt.arbitrum_contracts.contracts
19where year=2025
20group by all
21union all
22select 'arbitrum_nova' as chain, year,month,day,count(*) as nr_contracts, count(distinct bytecode) as nr_distinct_bytecodes
23from @blt.arbitrum_nova_contracts.contracts
24where year=2025
25group by all
26union all
27select 'base' as chain, year,month,day,count(*) as nr_contracts, count(distinct bytecode) as nr_distinct_bytecodes
28from @blt.base_contracts.contracts
29where year=2025
30group by all
31union all
32select 'bnb' as chain, year,month,day,count(*) as nr_contracts, count(distinct bytecode) as nr_distinct_bytecodes
33from @blt.bnb_contracts.contracts
34where year=2025
35group by all
36union all
37select 'fantom' as chain, year,month,day,count(*) as nr_contracts, count(distinct bytecode) as nr_distinct_bytecodes
38from @blt.fantom_contracts.contracts
39where year=2025
40group by all
41union all
42select 'gnosis' as chain, year,month,day,count(*) as nr_contracts, count(distinct bytecode) as nr_distinct_bytecodes
43from @blt.gnosis_contracts.contracts
44where year=2025
45group by all
46union all
47select 'optimism' as chain, year,month,day,count(*) as nr_contracts, count(distinct bytecode) as nr_distinct_bytecodes
48from @blt.optimism_contracts.contracts
49where year=2025
50group by all
51)
52select chain
53       , ROUND(avg(nr_contracts),0) as avg_daily_contracts
54       , ROUND(avg(nr_distinct_bytecodes),0) as avg_daily_distinct_bytecodes
55       , max(nr_contracts) as max_daily_contracts
56       , max(nr_distinct_bytecodes) as max_daily_distinct_bytecodes
57from daily_contracts
58group by 1
59order by 2 desc