WITH
evm_dex_trades AS (
SELECT
trades.block_timestamp,
trades.chain,
trades.project,
trades.transaction_fees_usd,
IFF(
trades.transaction_fees = 0,
0,
trades.transaction_fees_usd / trades.transaction_fees
) AS gas_usd_price,
trades.transaction_fees,
CASE
WHEN chain = 'bsc' THEN transaction_fees * gas_usd_price
ELSE fee_details['receipt_gas_used'] * fee_details['base_fee_per_gas'] / 1e18 * gas_usd_price
END AS base_fees_usd,
CASE
WHEN chain = 'bsc' THEN 0
ELSE fee_details['receipt_gas_used'] * (
fee_details['receipt_effective_gas_price'] - fee_details['base_fee_per_gas']
) / 1e18 * gas_usd_price
END AS priority_fees_usd,
CASE
WHEN project = 'uniswap'
AND protocol = 'uniswap_v2' THEN 'UniswapV2'
WHEN project <> 'uniswap'
AND protocol = 'uniswap_v2' THEN 'V2 Forks'
WHEN project = 'uniswap'
AND protocol = 'uniswap_v3' THEN 'UniswapV3'
WHEN project <> 'uniswap'
AND protocol = 'uniswap_v3' THEN 'V3 Forks'
WHEN project = 'pancakeswap'
AND protocol = 'pancakeswap_v3' THEN 'V3 Forks'
ELSE 'Others'
END AS dex,
INITCAP(REPLACE(trades.project, '_', '')) AS dex_project,
trades.transaction_hash,
trades.transaction_index,
trades.transaction_from_address AS sender,
trades.usd_amount
FROM
crosschain.dex.trades_evm AS trades
WHERE
block_timestamp >= '2024-08-01'
AND block_timestamp >= CURRENT_TIMESTAMP() - INTERVAL '1 month'
),
roll_up_dex_trades AS (
SELECT
block_timestamp,
chain,
sender ,
transaction_hash,
transaction_index,
ARRAY_AGG(DISTINCT dex) AS dexes,
ARRAY_AGG(DISTINCT dex_project) AS dex_project,
COUNT(DISTINCT dex) AS dex_count,
MAX(transaction_fees_usd) AS transaction_fees_usd,
MAX(base_fees_usd) AS base_fees_usd,
MAX(priority_fees_usd) AS priority_fees_usd,
SUM(usd_amount) AS volume_usd
FROM
evm_dex_trades
GROUP BY
ALL
),
uniswap_fork_metrics_latest AS (
SELECT
DATE_TRUNC('month', block_timestamp) AS date,
INITCAP(chain) AS blockchain,
CASE
WHEN ARRAY_CONTAINS(CAST('UniswapV2' AS VARIANT), dexes)
AND dex_count = 1 THEN 'UniswapV2'
WHEN ARRAY_CONTAINS(CAST('UniswapV3' AS VARIANT), dexes)
AND dex_count = 1 THEN 'UniswapV3'
WHEN ARRAY_CONTAINS(CAST('UniswapV3' AS VARIANT), dexes)
AND ARRAY_CONTAINS(CAST('UniswapV2' AS VARIANT), dexes)
AND dex_count = 2 THEN 'UniswapV2&V3'
WHEN ARRAY_CONTAINS(CAST('V2 Forks' AS VARIANT), dexes)
AND dex_count = 1 THEN 'V2 Forks'
WHEN ARRAY_CONTAINS(CAST('V3 Forks' AS VARIANT), dexes)
AND dex_count = 1 THEN 'V3 Forks'
ELSE 'Other DEXs'
END AS label,
SUM(transaction_fees_usd) AS total_transaction_fees_usd,
SUM(priority_fees_usd) AS total_priority_fees_usd,
SUM(base_fees_usd) AS total_base_fees_usd,
SUM(volume_usd) AS total_volume_usd
FROM
roll_up_dex_trades
GROUP BY
ALL
ORDER BY
1 DESC
),
final_cte AS (
SELECT
DATE_TRUNC('month', date) AS date,
blockchain,
label,
SUM(total_transaction_fees_usd) AS total_transaction_fees_usd,
SUM(total_priority_fees_usd) AS total_priority_fees_usd,
SUM(total_base_fees_usd) AS total_base_fees_usd,
SUM(total_volume_usd) AS total_volume_usd
FROM
crosschain.dex.seed_uniswap_fork_metrics
WHERE
date < '2024-08-01'
GROUP BY
ALL
UNION ALL
SELECT
*
FROM
uniswap_fork_metrics_latest
)
SELECT
*
FROM
final_cte
ORDER BY
date DESC
LIMIT
500000
2024-10-01T00:00:00
Ethereum
2024-10-01T00:00:00
Ethereum
