mucrypto2023-03-06 02:01 PM
    Updated 2023-03-06
    with wallets as (select
    date_trunc('day', block_timestamp) as day,
    from_address as sending,
    to_address as receiving,
    count(tx_hash) as interactions
    from ethereum.core.fact_transactions
    where block_timestamp::date between '2022-05-04' and '2022-05-15'
    and to_address is not null
    group by 1,2,3
    qualify row_number() over (partition by day order by interactions desc) <= 5),

    labels_from as (select
    address as sending_address,
    address_name as sending_name
    from ethereum.core.dim_labels
    where address in (select sending from wallets)
    group by 1,2),

    labels_to as (select
    address as receiving_address,
    address_name as receiving_name
    from ethereum.core.dim_labels
    where receiving_address in (select receiving from wallets)
    group by 1,2)

    select wallets.day,
    wallets.interactions,
    labels_from.sending_name,
    labels_from.sending_address,
    labels_to.receiving_name,
    wallets.sending,
    wallets.receiving,
    case
    when labels_from.sending_name = 'coinbase 5' and labels_to.receiving_name = 'hop protocol: usdt l1canonicaltoken' then 'Coinbase with USDT'
    when wallets.sending = ('0x4d9ff50ef4da947364bb9650892b2554e7be5e2b') and labels_to.receiving_address = ('0x0b95993a39a363d99280ac950f5e4536ab5c5566') then 'Binance with Binance'
    when labels_from.sending_name = 'coinbase 11' and labels_to.receiving_name = 'terra: luna token' then 'Coinbase with LUNA'
    Run a query to Download Data