flipside

    Price Indices compare copy
    winnie-fsPrice Indices compare copy
    Updated 2024-02-22
    -- forked from Aephia / Price Indices compare @ https://flipsidecrypto.xyz/Aephia/q/dmR8V8hm-7pD/price-indices-compare

    WITH atlas_price AS (
    select
    --*
    DATE(block_timestamp) AS date,
    sum(case when (swap_from_mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v')
    then swap_from_amount
    when (swap_to_mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v')
    then swap_to_amount end) as ATLAS,
    sum(case when (swap_from_mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v')
    then swap_to_amount
    when (swap_to_mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v')
    then swap_from_amount end) as USDC,
    USDC / ATLAS as atlas_price
    from solana.defi.fact_swaps
    WHERE succeeded = 'true'
    AND program_id IN (
    'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4',
    'JUP5cHjnnCx2DppVsufsLrXs8EBZeEZzGtEK9Gdz6ow',
    'JUP4Fb2cqiRUcaTHdrPC8h2gNsA2ETXiPDD33WcGuJB',
    'JUP3c2Uh3WA4Ng34tw6kPd2G4C5BB21Xo36Je1s32Ph',
    'JUP2jxvXaqu7NQY1GmNF4m1vodw12LVXYxbFL2uJvfo')
    AND block_timestamp > '2022-07-22' --AND block_timestamp < current_date
    group by 1
    ),


    assets AS (
    select
    --*
    date_trunc('day', block_timestamp::date) as date,
    ---SHIPS--------------------------------------------
    sum(case when inner_instruction:instructions[0]:parsed:info:mint = 'Ev3xUhc1Leqi4qR2E5VoG9pcxCvHHmnAaSRVPg485xAT' AND inner_instruction:instructions[0]:parsed:info:authority != 'MRKT9mCmNU2R4KnZt9BV5uh9MESj7Phxws4AR7fUhRc' AND inner_instruction:instructions[1]:parsed:info:mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' then inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount when inner_instruction:instructions[2]:parsed:info:mint = 'Ev3xUhc1Leqi4qR2E5VoG9pcxCvHHmnAaSRVPg485xAT' AND inner_instruction:instructions[2]:parsed:info:authority != 'MRKT9mCmNU2R4KnZt9BV5uh9MESj7Phxws4AR7fUhRc' AND inner_instruction:instructions[0]:parsed:info:mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' then inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount when inner_instruction:instructions[4]:parsed:info:mint = 'Ev3xUhc1Leqi4qR2E5VoG9pcxCvHHmnAaSRVPg485xAT' AND inner_instruction:instructions[4]:parsed:info:authority != 'MRKT9mCmNU2R4KnZt9BV5uh9MESj7Phxws4AR7fUhRc' AND inner_instruction:instructions[3]:parsed:info:mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' then inner_instruction:instructions[1]:parsed:info:amount / POW(10,8) + inner_instruction:instructions[3]:parsed:info:tokenAmount:uiAmount end) AS OPALJ_atlas,
    sum(case when inner_instruction:instructions[0]:parsed:info:mint = 'Ev3xUhc1Leqi4qR2E5VoG9pcxCvHHmnAaSRVPg485xAT' AND inner_instruction:instructions[0]:parsed:info:authority != 'MRKT9mCmNU2R4KnZt9BV5uh9MESj7Phxws4AR7fUhRc' AND inner_instruction:instructions[1]:parsed:info:mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' then zeroifnull(inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) + zeroifnull(inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount) when inner_instruction:instructions[2]:parsed:info:mint = 'Ev3xUhc1Leqi4qR2E5VoG9pcxCvHHmnAaSRVPg485xAT' AND inner_instruction:instructions[2]:parsed:info:authority != 'MRKT9mCmNU2R4KnZt9BV5uh9MESj7Phxws4AR7fUhRc' AND inner_instruction:instructions[0]:parsed:info:mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' then inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount when inner_instruction:instructions[4]:parsed:info:mint = 'Ev3xUhc1Leqi4qR2E5VoG9pcxCvHHmnAaSRVPg485xAT' AND inner_instruction:instructions[4]:parsed:info:authority != 'MRKT9mCmNU2R4KnZt9BV5uh9MESj7Phxws4AR7fUhRc' AND inner_instruction:instructions[3]:parsed:info:mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' then inner_instruction:instructions[1]:parsed:info:amount / POW(10,6) + inner_instruction:instructions[3]:parsed:info:tokenAmount:uiAmount end) AS OPALJ_usdc,
    Last run: about 1 year ago
    DATE
    SHIP_PRICE_INDEX
    CLAIM_STAKE_PRICE_INDEX
    1
    		2024-02-21 00:00:00.000110.690558471133.438260056
    2
    		2024-02-20 00:00:00.000110.293415598122.973969098
    3
    		2024-02-19 00:00:00.000109.386891453141.667222595
    4
    		2024-02-18 00:00:00.000110.748680511131.369636194
    5
    		2024-02-17 00:00:00.000110.819568274126.788100037
    6
    		2024-02-16 00:00:00.000111.066597435126.822129986
    7
    		2024-02-15 00:00:00.000111.700352518123.987321904
    8
    		2024-02-14 00:00:00.000110.651570761125.833450294
    9
    		2024-02-13 00:00:00.000111.207252342127.215032376
    10
    		2024-02-12 00:00:00.000110.527596529126.816826424
    11
    		2024-02-11 00:00:00.000106.990812712123.761652697
    12
    		2024-02-10 00:00:00.000107.171618729120.068753118
    13
    		2024-02-09 00:00:00.000108.89866327122.541701463
    14
    		2024-02-08 00:00:00.000107.363252893126.730254481
    15
    		2024-02-07 00:00:00.000107.436019989128.297347881
    16
    		2024-02-06 00:00:00.000108.444353096132.078092757
    17
    		2024-02-05 00:00:00.000109.328781868132.044771168
    18
    		2024-02-04 00:00:00.000109.597592782127.512217695
    19
    		2024-02-03 00:00:00.000109.324126606129.76925071
    20
    		2024-02-02 00:00:00.000107.764405886130.615590232
    ...
    417
    22KB
    410s