Cipher009-CpUJ5kGnosis | Most Interacted Contracts/Wallets copy
    -- forked from KowalskiDeFi / Gnosis | Most Interacted Contracts/Wallets @ https://flipsidecrypto.xyz/KowalskiDeFi/q/fYm1Zs2JuHeN/gnosis-most-interacted-contracts-wallets

    -- Written by Konstantinos or Konstan-🌲-os
    -- The idea here is to get the 5-10 most interacted with contracts/wallets approximately when the spike in active users took place.
    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 from_address)/COUNT(DISTINCT TX_HASH) as avg_users_per_txn_to_addr, -- the closer to 1 the more "fake" the spike in transaction is likely to be. (i.e. they did only 1 transfer/transaction to that contract/wallet in that period of time)
    CASE WHEN labels.address_name IS NULL THEN to_address ELSE labels.address_name END as to_address
    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 > '2023-06-10' -- right before identified spike
    AND block_timestamp::date < CURRENT_DATE -- today's date is never complete. Please stop showing it people :)
    GROUP BY date, to_address,labels.address_name
    QUALIFY DENSE_RANK() OVER (ORDER BY unique_active_users DESC) <= {{top_n_to_addresses}} -- Gotta love qualify.
    ORDER BY date DESC, unique_active_users DESC