flipside

    eclipse; tx_usd
    elovianooeclipse; tx_usd
    Updated 2025-01-16
    with base as (
    select
    block_timestamp
    , tx_id , signers[0] as user
    , pre_balances[0] / 1e9 as pre_balance , post_balances[0] / 1e9 as post_balance
    , fee / 1e9 as fee
    , ABS((post_balances[0] / 1e9) - (pre_balances[0] / 1e9) - (fee / 1e9)) as transaction_amount
    , p.price as eth_price
    , ABS((post_balances[0] / 1e9) - (pre_balances[0] / 1e9) - (fee / 1e9)) * p.price as transaction_usd
    from eclipse.core.fact_transactions t
    -- where signers[0] = 'GiLNjb2X37CzL36oDudGxN7RHN4Cye1WPQLFCqpz7nsL'
    join crosschain.price.ez_prices_hourly p
    on p.hour = date_trunc('hour' , t.block_timestamp)
    and p.blockchain = 'ethereum' and p.symbol = 'WETH'
    )

    select date_trunc('week' , block_timestamp) as week , user
    , sum(transaction_usd) as total_transaction_volume
    from base
    where date_trunc('week' , block_timestamp) = cast ('{{week}}' as date)
    group by 1 , 2
    having total_transaction_volume > 10
    order by 1, 3 desc

    Last run: 3 months ago
    WEEK
    USER
    TOTAL_TRANSACTION_VOLUME
    1
    		2025-01-06 00:00:00.000
    CrfbABN2sSvmoZLu9eDDfXpaC2nHg42R7AXbHs9eg4S9
    		7747030.86091208
    2
    		2025-01-06 00:00:00.000
    97Qhv1jG8G5M47FvkvaZCu9y4oVfgdTPHFqtpVjRZ2k2
    		6435475.97258013
    3
    		2025-01-06 00:00:00.000
    F7p3dFrjRTbtRp8FRF6qHLomXbKRBzpvBLjtQcfcgmNe
    		3645247.869178
    4
    		2025-01-06 00:00:00.000
    5X3To2fubqEmyDjbVzaSjYRsnP8Vs93hscBUSFbHwWdR
    		3487636.22664949
    5
    		2025-01-06 00:00:00.000
    BtGH2WkM1oNyPVgzYT51xV2gJqHhVQ4QiGwWirBUW5xN
    		2999371.68380761
    6
    		2025-01-06 00:00:00.000
    JBV96j6wNrnGemnZEgdHazSnPUWzdQQmLaYiX1ZSCR4c
    		2877048.48974657
    7
    		2025-01-06 00:00:00.000
    ChLquVMBH45j4tg8NdcsgzD4G2Zxi3Z2zzTGCJoHBrY2
    		1880299.35071548
    8
    		2025-01-06 00:00:00.000
    5pVSCmSkMCqv56aoRvvsUr5doo5d4BJdoxMv6NkCjqJg
    		1860122.5856296
    9
    		2025-01-06 00:00:00.000
    3AmqJ9UMxHPMWVTg7TUUH3SktMamebkUFEv53sfJECZY
    		1456618.24332239
    10
    		2025-01-06 00:00:00.000
    93gkfH9SAei1Ne68DGSGj3SmtaNnRpWxzrjYpCKK6VDV
    		1194620.14673126
    11
    		2025-01-06 00:00:00.000
    DkRTsnouHvAMBrpAvKvTmZc4cFPGMmsK7uLRjxULh5fs
    		976598.714306471
    12
    		2025-01-06 00:00:00.000
    E7kW4A8AfakERvFvTbNVJ2SHET6empGk4SUppF2hgNqv
    		947142.420253764
    13
    		2025-01-06 00:00:00.000
    C1BhGYt7zqsNtqxRHwX46979rbUSDPcBWnRaxRVCTmQR
    		894220.995522866
    14
    		2025-01-06 00:00:00.000
    45UXt6Y3zcHZUXFYWj8GCxe1wDBrQyVAJS3AfxiVjznZ
    		879225.63165911
    15
    		2025-01-06 00:00:00.000
    3Ce2MsdqzeksjSfqiouNZCfNZFXdF63B9bTcr2QS2TEh
    		875181.840915296
    16
    		2025-01-06 00:00:00.000
    DLgSkb2Vj63vFnxfmyyp3qaYBKwDRme9HpxrkJbmsc6B
    		842375.136687721
    17
    		2025-01-06 00:00:00.000
    4nw5QJtnhS8MbDd72udsg6joVU9gdZ6Jk2o2QiwfhV7D
    		836844.315604919
    18
    		2025-01-06 00:00:00.000
    9gJ9U9qRNruQaRpMJ1q5Qf5kG81wHeXn6St5iU1q3ZVS
    		624358.708361494
    19
    		2025-01-06 00:00:00.000
    GHbeKgTooFUqjp1ya48WpTkqxc58j5M53nu9nFCiNBai
    		521055.263549964
    20
    		2025-01-06 00:00:00.000
    8ZqNNHvT3bmKRqqrHBTe9tKJYoPu2N48DrrX46J6ftMu
    		518783.800918372
    ...
    85697
    7MB
    34s