kaibladeDaily Fees (Ethereum)
    Updated 2022-09-17
    WITH fees_tx AS
    (
    SELECT block_timestamp, tx_hash, buyer_address,project_name, platform_fee,price, currency_symbol
    FROM ethereum.core.ez_nft_sales
    WHERE block_timestamp::date >= CURRENT_DATE - INTERVAL '2 month'

    ),

    available_symbols AS
    (SELECT DISTINCT (currency_symbol) AS symbol
    FROM fees_tx
    ),
    prices_symbols AS
    (SELECT hour,
    (CASE
    WHEN symbol='WETH' THEN 'ETH'
    ELSE symbol
    END) AS symbol,
    price
    FROM ethereum.core.fact_hourly_token_prices
    WHERE symbol IN (SELECT symbol FROM available_symbols)),

    refined_prices AS
    (SELECT ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY hour DESC) AS row_number,
    symbol,
    price
    FROM prices_symbols),

    unknown_currencies AS
    (SELECT block_timestamp, tx_hash, buyer_address,project_name,
    (CASE
    WHEN currency_address ='0x7cf1febee00cab5b130436bd1b91b5c013b83641' THEN platform_fee*0.134
    WHEN currency_address ='0xddf1d10f199c19a7266e79b879e6fafade729586' THEN platform_fee*47.2
    WHEN currency_address =LOWER('0x0E050B2B7aDb2cAe5E8593e280ed5582953f9ad2') THEN platform_fee*142.84
    Run a query to Download Data