Cipher009-CpUJ5kGnosis | Activity Spike copy
    -- forked from KowalskiDeFi / Gnosis | Activity Spike @ https://flipsidecrypto.xyz/KowalskiDeFi/q/ahl3M9tFc790/gnosis-activity-spike
    -- Written by Konstantinos or Konstan-🌲-os
    --Used by Cipher009

    SELECT
    main.*,
    transaction_fees*prices.price_usd as transaction_fees_usd,
    transaction_fees_usd/main.unique_active_users as fees_per_user
    FROM
    (
    SELECT
    DATE_TRUNC('{{period}}',block_timestamp) as date,
    COUNT(DISTINCT TX_HASH) as number_transactions,
    COUNT(DISTINCT from_address) as unique_active_users,
    COUNT(DISTINCT TX_HASH)/COUNT(DISTINCT from_address) as avg_txn_per_user,
    SUM(TX_FEE) as transaction_fees
    FROM gnosis.core.fact_transactions as transactions
    LEFT JOIN (SELECT address, address_name FROM gnosis.core.dim_labels) as labels
    ON transactions.to_address=labels.address
    WHERE block_timestamp::date > '2022-09-15' -- right before identified spike
    AND block_timestamp::date < CURRENT_DATE -- today's date is never complete. Please stop showing it people :)
    GROUP BY date
    ) as main
    INNER JOIN
    (
    SELECT
    DATE_TRUNC('{{period}}',hour) as date,
    AVG(PRICE) as price_usd
    FROM ethereum.core.fact_hourly_token_prices
    WHERE TOKEN_ADDRESS='0x6b175474e89094c44da98b954eedeac495271d0f'
    GROUP BY date
    ) as prices
    ON main.date=prices.date
    ORDER BY date DESC


    Run a query to Download Data