DISTINCT TX_HASH AS SUSHI_TX_ID

FROM ethereum.core.ez_dex_swaps

WHERE platform = 'sushiswap'

AND block_timestamp >=DATEADD (DAY,-30,GETDATE())

),

SENDER_ADDRESS AS ( -- select sender's address from events_emitted

SELECT

DISTINCT TX_HASH AS TX_ID_SENDER,

ORIGIN_FROM_ADDRESS AS SENDER_ADDRESS

FROM ethereum.core.fact_event_logs

WHERE block_timestamp >=DATEADD (DAY,-30,GETDATE())

),

ETH_FEES AS ( -- obtain eth fees

SELECT

DISTINCT TX_HASH as tx_id_fees,

TX_FEE as fee_ethereum

FROM ethereum.core.fact_transactions

WHERE block_timestamp >=DATEADD (DAY,-30,GETDATE())

),

LONG_FORMAT AS (

SELECT

SUSHI_TX_ID,

SENDER_ADDRESS,

fee_ethereum

FROM TX_ID_SUSHISWAP

LEFT JOIN SENDER_ADDRESS on SUSHI_TX_ID = TX_ID_SENDER

LEFT JOIN ETH_FEES on SUSHI_TX_ID = tx_id_fees

SELECT

COUNT(DISTINCT SUSHI_TX_ID) as NO_OF_TX_ETH,

COUNT(DISTINCT SENDER_ADDRESS) as NO_OF_ADDRESS_ETH,

SUM(fee_ethereum) AS TOTAL_FEES_ETH,

TOTAL_FEES_ETH/NO_OF_ADDRESS_ETH AS FEES_PER_ADDRESS_ETH,

TOTAL_FEES_ETH/NO_OF_TX_ETH AS FEES_PER_TX_ETH