prices_optimism AS ( SELECT HOUR, TOKEN_ADDRESS, PRICE FROM ( SELECT HOUR, TOKEN_ADDRESS, price, ROW_NUMBER() OVER ( PARTITION BY TOKEN_ADDRESS ORDER BY HOUR DESC ) as rn FROM optimism.core.fact_hourly_token_prices ) as prices WHERE rn = 1 ),

prices_arbitrum AS ( SELECT HOUR, TOKEN_ADDRESS, PRICE FROM ( SELECT HOUR, TOKEN_ADDRESS, price, ROW_NUMBER() OVER ( PARTITION BY TOKEN_ADDRESS ORDER BY HOUR DESC ) as rn FROM arbitrum.core.fact_hourly_token_prices ) as prices WHERE rn = 1 ),

prices_ethereum AS ( SELECT HOUR, TOKEN_ADDRESS, PRICE FROM ( SELECT HOUR, TOKEN_ADDRESS, price, ROW_NUMBER() OVER ( PARTITION BY TOKEN_ADDRESS ORDER BY HOUR DESC ) as rn FROM ethereum.core.fact_hourly_token_prices ) as prices WHERE rn = 1 ),

prices_polygon AS ( SELECT HOUR, TOKEN_ADDRESS, PRICE FROM ( SELECT HOUR, TOKEN_ADDRESS, price, ROW_NUMBER() OVER ( PARTITION BY TOKEN_ADDRESS ORDER BY HOUR DESC ) as rn FROM polygon.core.fact_hourly_token_prices ) as prices WHERE rn = 1 )

SELECT contract_name, day, SUM(token_in_usd), SUM(dust_usd), SUM(dust_usd) / SUM(token_in_usd) * 100 as dust_percentage FROM (

SELECT contract_name, DATE_TRUNC('day', BLOCK_TIMESTAMP) as day, TX_HASH, chain, token_in_amt, token_in_usd, dust_amt, dust_usd, dust_percentage FROM (SELECT 'WidoZapperUniswapV2.sol' as contract_name,

events.BLOCK_TIMESTAMP,

events.TX_HASH,

'ethereum' as chain,

transfers.TOKEN_ADDRESS as dust_token,

transfer_in.INPUT / pow(10, contract_token_in.decimals) as token_in_amt,

ifnull(price_token_in.price * token_in_amt, 0) as token_in_usd,

dust / pow(10, contract_dust_token.decimals) as dust_amt,

ifnull(price_dust.price * dust_amt, 0) as dust_usd,

dust_usd / token_in_usd * 100 as dust_percentage

FROM ethereum.core.fact_decoded_event_logs as events

JOIN (SELECT CONTRACT_ADDRESS as TOKEN_ADDRESS, TX_HASH, RAW_AMOUNT as DUST FROM ethereum.core.fact_token_transfers) as transfers ON events.TX_HASH = transfers.TX_HASH

JOIN (SELECT CONTRACT_ADDRESS as TOKEN_ADDRESS, TX_HASH, RAW_AMOUNT as INPUT, ORIGIN_TO_ADDRESS, ORIGIN_FROM_ADDRESS, FROM_ADDRESS FROM ethereum.core.fact_token_transfers WHERE ORIGIN_FROM_ADDRESS = FROM_ADDRESS) as transfer_in ON events.TX_HASH = transfer_in.TX_HASH

JOIN (SELECT price, token_address FROM prices_ethereum) as price_dust ON transfers.TOKEN_ADDRESS = price_dust.token_address

JOIN (SELECT price, token_address FROM prices_ethereum) as price_token_in ON transfer_in.TOKEN_ADDRESS = price_token_in.token_address

JOIN (SELECT address, decimals FROM ethereum.core.dim_contracts) as contract_dust_token ON transfers.TOKEN_ADDRESS = contract_dust_token.address

JOIN (SELECT address, decimals FROM ethereum.core.dim_contracts) as contract_token_in ON transfer_in.TOKEN_ADDRESS = contract_token_in.address

WHERE events.CONTRACT_ADDRESS = '0x279d474ca9725ec88c8731860867b0a276148d4d'

AND events.EVENT_NAME = 'DustSent'

AND events.DECODED_LOG:amount::FLOAT = transfers.DUST UNION ALL SELECT 'WidoZapperUniswapV2.sol' as contract_name,

events.BLOCK_TIMESTAMP,

events.TX_HASH,

'polygon' as chain,

transfers.TOKEN_ADDRESS as dust_token,

transfer_in.INPUT / pow(10, contract_token_in.decimals) as token_in_amt,

ifnull(price_token_in.price * token_in_amt, 0) as token_in_usd,

dust / pow(10, contract_dust_token.decimals) as dust_amt,

ifnull(price_dust.price * dust_amt, 0) as dust_usd,

dust_usd / token_in_usd * 100 as dust_percentage