CREATE OR REPLACE view dune_user_generated.uniswap_v3 as
erc20a.symbol AS token_a_symbol,
erc20b.symbol AS token_b_symbol,
token_a_amount_raw / 10 ^ erc20a.decimals AS token_a_amount,
token_b_amount_raw / 10 ^ erc20b.decimals AS token_b_amount,
coalesce(trader_a, tx."from") as trader_a, -- subqueries rely on this COALESCE to avoid redundant joins with the transactions table
token_a_amount_raw / 10 ^ erc20a.decimals * pa.price,
token_b_amount_raw / 10 ^ erc20b.decimals * pb.price
exchange_contract_address,
row_number() OVER (PARTITION BY tx_hash, evt_index, trace_address) AS trade_id
t.evt_block_time AS block_time,
t."recipient" AS trader_a,
abs(amount0) AS token_a_amount_raw,
abs(amount1) AS token_b_amount_raw,
NULL::numeric AS usd_amount,
f.token0 AS token_a_address,
f.token1 AS token_b_address,
t.contract_address as exchange_contract_address,
t.evt_tx_hash AS tx_hash,
NULL::integer[] AS trace_address,
uniswap_v3."Pair_evt_Swap" t
INNER JOIN uniswap_v3."Factory_evt_PoolCreated" f ON f.pool = t.contract_address
INNER JOIN ethereum.transactions tx
ON dexs.tx_hash = tx.hash
LEFT JOIN erc20.tokens erc20a ON erc20a.contract_address = dexs.token_a_address
LEFT JOIN erc20.tokens erc20b ON erc20b.contract_address = dexs.token_b_address
LEFT JOIN prices.usd pa ON pa.minute = date_trunc('minute', dexs.block_time)
AND pa.contract_address = dexs.token_a_address
LEFT JOIN prices.usd pb ON pb.minute = date_trunc('minute', dexs.block_time)
AND pb.contract_address = dexs.token_b_address