-- forked from ROUTERS SHARE - ETH (daily) test @ https://flipsidecrypto.xyz/edit/queries/a82fb0a1-8216-401e-a836-ad4c20170509

WITH routers AS (

SELECT *

FROM VALUES

--update 2023-02-24

('gnosis', '1inch', 'AggregationRouterV4', 'Aggregator', '0x1111111254fb6c44bac0bed2854e76f90643097d'),

('gnosis', '1inch', 'AggregationRouterV5', 'Aggregator', '0x1111111254eeb25477b68fb85ed929f73a960582'),

('gnosis', 'HoneySwap', 'UniswapV2Router02', 'Router', '0x1c232f01118cb8b424793ae03f870aa7d0ac7f77'),

('gnosis', 'SushiSwap', '', 'Router', '0x1b02da8cb0d097eb8d57a175b88c7d8b47997506'),

('gnosis', 'CoW Swap', 'CoW Protocol: Settlement', 'Aggregator of Aggregators', '0x9008d19f58aabd9ed0d60971565aa8510560ab41'),

('gnosis', 'Levinswap', 'UniswapV2Router02', 'Router', '0xb18d4f69627f8320619a696202ad2c430cef7c53'),

('gnosis', 'Swapr', 'DXswapRouter', 'Router', '0xe43e60736b1cb4a75ad25240e2f9a62bff65c0c0'),

('gnosis', 'Baoswap', 'UniswapV2Router02', 'Router', '0x6093aebac87d62b1a5a4ceec91204e35020e38be'),

('gnosis', 'Socket', 'Registry', 'Aggregator of Aggregators', '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0'),

('gnosis', 'LiFi', 'LiFiDiamond_v2', 'Aggregator', '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae')

AS data(blockchain, project, router_name, contract_type, address)

),

incorrect_tokens AS (

SELECT *

FROM VALUES

('')

AS date(token_address)

SELECT

DATE_TRUNC('hour', block_timestamp) AS time,

project,

SUM(usd_amount) AS volume,

COUNT(*) AS txs,

COUNT(DISTINCT tx_from) AS wallets

FROM (

SELECT