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 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

from (

SELECT

transactions.block_timestamp,

transactions.from_address AS tx_from,

transactions.tx_hash,

MIN_BY(project, substring(identifier, len(type)+2)) AS project,

MIN(substring(identifier, len(type)+2)) AS start_trace_address

FROM

ethereum.core.fact_transactions AS transactions

JOIN ethereum.core.fact_traces AS traces ON traces.tx_hash = transactions.tx_hash

JOIN routers ON routers.address = traces.from_address AND routers.blockchain = 'ethereum'

LEFT JOIN ethereum.core.ez_nft_transfers AS transfers ON transfers.tx_hash = transactions.tx_hash

WHERE

DATE_TRUNC('day', transactions.block_timestamp) > DATEADD('day', -32, CURRENT_TIMESTAMP())

AND DATE_TRUNC('day', transactions.block_timestamp) < DATE_TRUNC('day', CURRENT_TIMESTAMP())

AND DATE_TRUNC('day', traces.block_timestamp) > DATEADD('day', -32, CURRENT_TIMESTAMP())