WITH routers AS (

SELECT *

FROM VALUES

--update 2023-02-24

('ethereum', '0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45', 'UniSwap', 'Router'),

('ethereum', '0x7a250d5630b4cf539739df2c5dacb4c659f2488d', 'UniSwap', 'Router'),

('ethereum', '0xe592427a0aece92de3edee1f18e0157c05861564', 'UniSwap', 'Router'),

('ethereum', '0xf164fc0ec4e93095b804a4795bbe1e041497b92a', 'UniSwap', 'Router'),

('ethereum', '0x1111111254fb6c44bac0bed2854e76f90643097d', '1inch', 'Aggregator'),

('ethereum', '0x1111111254eeb25477b68fb85ed929f73a960582', '1inch', 'Aggregator'),

('ethereum', '0x11111112542d85b3ef69ae05771c2dccff4faa26', '1inch', 'Aggregator')

AS data(blockchain, address, project, contract_type)

select date_trunc('day', block_timestamp) as time

, project_agg as project

, sum(usd_amount) as volume

, count(*) as txs

, count(distinct tx_from) as wallets

from (

select r.*

, case

when (sum(usd_amount) over (partition by project) / sum(usd_amount) over()) > 0.01

or (count(*) over (partition by project) / count(*) over()) > 0.01

then project

else 'Other'

end as project_agg

from (

select calls.block_timestamp

, calls.tx_hash

, calls.tx_from

, project

, MAX(CASE WHEN contract_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN amount * price

ELSE amount / pow(10, decimals) * price END) as usd_amount