marque6y Sandwich - v1.3 - aggregated SOL
    Updated 2025-01-28
    -- forked from Vpe Sandwhich - v1.3 - aggregated SOL @ https://flipsidecrypto.xyz/studio/queries/ce89f2f8-b563-42b3-8874-20cbea411c97

    with

    jito_tip_txs as (

    select

    tx_id as jito_tx_id
    , (post_balances[f_accs.index] - pre_balances[f_accs.index]) / pow(10, 9) as tip_jito
    from solana.core.fact_events
    inner join solana.core.fact_transactions
    using(block_timestamp, tx_id, succeeded)
    inner join lateral flatten (input => account_keys) f_accs
    where succeeded
    and fact_events.program_id = 'E6YoRP3adE5XYneSseLee15wJshDxCsmyD2WtLvAmfLi'
    and substr(livequery.utils.udf_base58_to_hex(fact_events.instruction :data), 3, 16) = '9c23c76ff5fe2635'
    and fact_events.instruction :accounts[3] in ( -- Jito Tip Payment Addresses https://jito-foundation.gitbook.io/mev/mev-payment-and-distribution/on-chain-addresses#mainnet
    '96gYZGLnJYVFmbjzopPSU6QiEV5fGqZNyN9nmNhvrZU5'
    , 'HFqU5x63VTqvQss8hp11i4wVV8bD44PvwucfZ2bU7gRe'
    , 'Cw8CFyM9FkoMi7K7Crf6HNQqf4uEMzpKw6QNghXLvLkY'
    , 'ADaUMid9yfUytqMBgopwjb2DTLSokTSzL1zt6iGPaS49'
    , 'DfXygSm4jCyNCybVYYK6DwvWqjKee8pbDmJGcLWNDXjh'
    , 'ADuUkR4vqLUMWXxW9gh6D6L8pMSawimctcNZ5pGwDcEt'
    , 'DttWaMuVvTiduZRnguLF7jNxTgiMBZ1hyAumKUiL2KRL'
    , '3AVi9Tg9Uo68tJfuvoKvqKNWKkC5wPdSSdeBnizKZ6jT'
    )
    and fact_events.instruction :accounts[3] = f_accs.value :pubkey
    and block_timestamp > current_timestamp() - interval '30 days'

    union all

    select

    tx_id as jito_tx_id
    Last run: 26 days ago
    DATE
    SW_COUNT
    SW_COUNT_CUMUL
    TX_FEES
    TX_FEES_CUMUL
    TIPS_JITO
    TIPS_JITO_CUMUL
    EXTRACTED_VALUE_SOL
    EXTRACTED_VALUE_SOL_CUMUL
    PROFIT_SOL_NET
    PROFIT_SOL_NET_CUMUL
    1
    2025-01-28 08:00:00.000313386250.0000313.386250.0053000414471.9588583040.282122991232278.2918652320.276792951217792.946756928
    2
    2025-01-28 07:00:00.00069513386220.0069513.386224.58767611514471.95355826466.768670921232278.00974224162.174044806217792.669963977
    3
    2025-01-28 06:00:00.000190713379270.0190713.3792712.54951608714467.365882149143.874140341232211.24107132131.305554254217730.495919171
    4
    2025-01-28 05:00:00.000161013360200.016113.360218.61401416314454.816366062232.92421498232067.366930979214.294100817217599.190364917
    5
    2025-01-28 04:00:00.000198813344100.0198813.344137.56980039214436.202351899573.185648247231834.442715999535.595967855217384.8962641
    6
    2025-01-28 03:00:00.000189013324220.018913.3242223.33129544914398.632551507318.232123356231261.257067752294.881927907216849.300296245
    7
    2025-01-28 02:00:00.000198913305320.0198913.3053222.50693802414375.301256058266.045414301230943.024944396243.518586277216554.418368338
    8
    2025-01-28 01:00:00.000178013285430.017813.2854320.92177185614352.794318034274.498590861230676.979530095253.559019005216310.899782061
    9
    2025-01-28 00:00:00.000207613267630.0207613.2676327.35858552714331.872546178332.415293468230402.480939234305.035947941216057.340763056
    10
    2025-01-27 23:00:00.000183913246870.0183913.2468720.21422152514304.513960651234.399428971230070.065645766214.166817446215752.304815115
    11
    2025-01-27 22:00:00.000248413228480.0248413.2284832.92363711514284.299739126442.013875785229835.666216795409.06539867215538.137997669
    12
    2025-01-27 21:00:00.000279313203640.0279313.2036459.910618814251.376102011929.761166774229393.65234101869.822617974215129.072598999
    13
    2025-01-27 20:00:00.000237813175710.0237813.1757125.39612306314191.465483211311.303505039228463.891174236285.883601976214259.249981025
    14
    2025-01-27 19:00:00.000268613151930.0268613.1519335.81426889714166.069360148460.011220053228152.587669197424.170091156213973.366379049
    15
    2025-01-27 18:00:00.000222213125070.0222213.1250721.81802005414130.255091251255.046770892227692.576449144233.206530838213549.196287893
    16
    2025-01-27 17:00:00.000223013102850.022313.1028526.70013142114108.437071197373.846842429227437.529678252347.124411008213315.989757055
    17
    2025-01-27 16:00:00.000194113080550.0194113.0805519.51594685314081.736939776230.390562243227063.682835823210.85520539212968.865346047
    18
    2025-01-27 15:00:00.000206713061140.0206713.0611419.03710820214062.220992923247.368486891226833.29227358228.310708689212758.010140657
    19
    2025-01-27 14:00:00.000220913040470.0220913.0404722.03824256114043.183884721267.594662831226585.923786689245.53433027212529.699431968
    20
    2025-01-27 13:00:00.000160113018380.0160113.0183815.06079890314021.14564216188.395886019226318.329123858173.319077116212284.165101698
    ...
    490
    69KB
    896s