-- the number of swaps and USDC volume in the USDC-WETH SushiSwap pool over the past 7 days
-- get details for relevant pool
with pools as
(
select pool_name
, pool_address
, token0
, token1
from ethereum.core.dim_dex_liquidity_pools
where pool_address = lower('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0') -- USDC-WETH Pool (https://etherscan.io/address/0x397FF1542f962076d0BFE58eA045FfA2d347ACa0#tokentxns)
),
-- get details for tokens in relevant pool
decimals as
(
select address
, symbol
, decimals
from ethereum.core.dim_contracts
where address =
(
select lower(token1)
from pools
)
or address =
(
select lower(token0)
from pools
)
),
-- aggregate pool and token details
pool_token_details as
(
select pool_name
, pool_address
, token0
, token1