flipside

    ship_prices
    Aephiaship_prices
    Updated 3 hours ago
    WITH atlas_price AS (
    SELECT
    hour::DATE AS DATE,
    AVG(price) AS atlas_price
    FROM
    SOLANA.price.ez_prices_hourly
    WHERE
    hour::DATE > current_date-120 --'2022-07-22'
    AND token_address = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx'
    GROUP BY ALL
    --order by 1 desc
    ),

    polis_price AS (
    SELECT
    hour::DATE AS DATE,
    AVG(price) AS polis_price
    FROM
    SOLANA.price.ez_prices_hourly
    WHERE
    hour::DATE > current_date-120 --'2022-07-22'
    AND token_address = 'poLisWXnNRwC6oBu1vHiuKQzFjGL4XDSu4g9qjz9qVk'
    GROUP BY ALL
    --order by 1 desc
    ),


    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 3 hours agoAuto-refreshes every 6 hours
    DATE
    OPALJ
    PX5
    VZUSOP
    CALCH
    OGKATP
    PX4
    OPALJJ
    FBLBPL
    CALG
    PF4
    FBLEGR
    OM
    PR8
    VZUSAM
    PC9
    PC11
    CHI
    OGKAJA
    CALEV
    TUFAFE
    FBLBEA
    PX6
    FBLEBO
    FBLETR
    FBLAIR
    OGKAMK
    FBLEUN
    ARC
    FBLLOW
    THRILL
    VZUSBA
    CALMED
    OPALBB
    CALATS
    PR6
    HEART
    OGKATU
    FBLMAM
    VZUSSO
    FBLBTA
    OGKASP
    PD9
    STAND
    SUPER
    TITAN
    CALSCD
    CALMAX
    OGKANR
    IMP_TIP
    IMP_TAP
    IMP
    FBLMEX
    FBLBBU
    OPALRF
    FBLSLE
    OGKARU
    PHI
    CALSHIP
    FBLBRA
    ATLAS
    POLIS
    ROW_NUMBER
    1
    		2025-06-13 00:00:00.0001.7717562523.92396875861.75303.187516903.03099750712.011971642725335.91251137.5641.851891.512585.735756400016362.589.98256.52535874.95732198179.478.43225342930001.83123809580.1218756.807513.312054901351567.5383.51875190566.912510423.03852849.0559.31277515.2838.3237.58499574.0425221.184258333192538710577.8751.69210.65950.1866666670.0010001641670.056106583331
    2
    		2025-06-12 00:00:00.0001.7717562523.92396875861.75303.187516903.915412512.948429554725335.91251137.5641.851891.512585.735756600016362.589.98256.52535874.95732198179.478.43225342930001.83123809580.1218756.807513.312054901351567.5383.51875190566.912510423.03852849.0559.31277518.1297562538.3254.7541658499574.0425218.802775192538710577.8751.69210.65950.1866666670.0010659379170.05866752
    3
    		2025-06-11 00:00:00.0001.7717562519.414155861.75303.187516903.915412512.948429554725335.91251137.5641.851891.512585.735756600016362.589.98256.52535874.95732198179.477.605530001.76453533380.1218756.807513.312054901351567.5383.51875278.5566.912510423.03852849.0559.31277517.89438.3260861.759574.0425210.7875192538710577.8751.69210.65950.1866666670.001122216250.060826833333
    4
    		2025-06-10 00:00:00.0001.84878951125861.75303.187516903.32513.15725335.91251137.5641.851891.512585.735756600016362.589.98256.52535874.95732198179.475.67363636430001.83802580.1218756.19513.6512054901351567.5383.51875278.5566.912510422.99532849.0565.157518.8940260861.759574.0425210.7875192538710577.8751.69210.65950.1866666670.0011118858330.059869416674
    5
    		2025-06-09 00:00:00.0001.84878951125861.75303.187516903.513.14724.5335.91251137.5641.851891.512585.735756600016362.584.5413758256.52535874.95732198179.475.67363636430001.84433369180.1218756.2314.2512054901351567.5383.51875278.5566.912510423.992849.0565.157517.907540256.838055861.759574.0425210.7875192538710577.8753.391424176210.65950.1866666670.0010868120830.058540333335
    6
    		2025-06-08 00:00:00.0001.79912526.5892.05303.187516903.513.14724.5375.1978751137.5641.851891.512589.8600016362.584.5413758256.52535874.95732198179.475.6736363646311.5251.9280.1218756.2314.97512054901351567.5383.51875278.5566.912510423.992849.0565.157517.838540256.838055755.259574.0425210.7875192536510577.8751.69210.65960.1066442310.001091056250.057891458336
    7
    		2025-06-07 00:00:00.0001.79912525.820416667892.05303.187516903.562512.590582174724.5375.1978751453.8875641.851891.512589.8600016362.584.5413758256.52536474.95732198181.259978476.6336311.5251.9280.1218755.6538236791512054901351567.5383.51875278.5566.912510423.992849.0565.157515.93885258640256.83805511959574.0425235.81251969.531010577.8751.699648649210.65864.9980961540.0010871283330.0582933757
    8
    		2025-06-06 00:00:00.0001.8424.812247728892.05303.187516903.562513.17724.5375.1978751453.8875641.851891.512589.8899016362.584.5413758256.52536466.581.259978478.86311.5251.9280.1218755.6538236791512054901351567.5383.51875278.5566.912510423.992849.0565.157515.79714257140246.0511959574.0425240.406251969.531010577.8751.699648649210.65915.0458333330.00108163250.057594258
    9
    		2025-06-05 00:00:00.0001.85303485423.746892.05303.187516903.32725002312.679258333655.5375.1978751453.8875641.851891.512585899016362.5918256.52536466.581.2599784816311.5259.622480.1218755.65382367914.19312054901351567.5383.51875278.5566.912510423.992849.0565.157516.08229166799257.9311959574.0425257.840451969.5394.4262510577.8751.7210.65939.1031481480.001073933750.056391708339
    10
    		2025-06-04 00:00:00.0001.85303485423.7461010303.187516903.32725002312.9650375.1978751453.8875641.851891.512585899016362.5918256.52536466.581.2599784816311.52510.13792336880.1218755.65382367914.76353888912054901351567.5383.51875278.5566.912510423.992849.0565.157517.999238.7511959574.0425257.840451969.5358.852510577.8751.7299.995942.7750543480.0010935829170.05590562510
    11
    		2025-06-03 00:00:00.0001.85303485423.746101038316903.43512.699296269650375.1978751453.8875641.85142585899016362.5918256.52536466.581.2599784816311.5251.91332584.4136.2413.912054901351567.5383.51875278.5566.912510423.992849.0565.157517.5166666679926611959574.0425263.31251969.5313.9810577.8751.7299.995954.863750.001110711250.05678611
    12
    		2025-06-02 00:00:00.0001.50758082823.746970.2427.916903.24912.699296269650375.1978751453.8875641.851579.87585899016362.5918256.52536466.581.259978478.77256311.5251.91332584.4136.241412054901351567.5536278.5566.912510423.992849.0565.157517.51666666799270.195137511959574.0425263.31251969.5313.9810577.8751.7299.995954.863750.0010927533330.0543898333312
    13
    		2025-06-01 00:00:00.0001.50758082823.746947.925427.916903.5312188113.260135903650375.1978751453.88756751579.87585899016362.592.158256.525320.76257781.259978478.77256311.5252.10807938184.4135.7414.34437512054901351567.5536278.5566.912510423.992849.0565.157518.15150599270.195137511959574.0425263.31251969.5313.9810577.8751.7299.995954.863750.0011111450.0554014166713
    14
    		2025-05-31 00:00:00.0001.50758082823.746947.925427.916903.40833333312.861771673650375.1978751453.88756751579.87585899016362.592.158256.525320.76257781.2599784806311.5252.10807938184.4135.7414.79512054901351567.5536278.5566.912510423.992849.0565.157518.15150599270.195137511959574.0425254.2550916671969.5313.9810577.8751.60075299.995954.863750.0010933154170.0550232514
    15
    		2025-05-30 00:00:00.0001.57958835823.746947.925427.916903.40833333313.713678648650375.1978751453.88756751579.87585899016362.592.158256.525320.762584.97581.2599784806311.5252.10807938184.4135.7414.812054901351567.5536278.5566.912510423.992849.0565.157518.0967599270.195137511959574.0425269.991969.5313.9810577.8751.60075299.995954.863750.0011424216670.0575643333315
    16
    		2025-05-29 00:00:00.0002.06199996724.215251154427.916903.40833333315.76650375.1978751453.88756751579.87585899016362.597.33248256.525320.762584.97581.2599784806311.5252.10807938184.4135.7416.7612054901351567.5536278.5566.912510423.992849.0565.157518.6149535719928011959574.0425284.991969.5313.9810577.8751.92555299.995972.50.0012205466670.0609014166716
    17
    		2025-05-28 00:00:00.0002.06199996728.0441666671154427.916903.85173980914.194558706650375.1978751453.88756751579.87591.9899016362.597.33248256.525320.762584.97581.259978479.956311.5252.00479166799.4456.516.7612054901351567.5536278.5566.912510423.992849.0565.157519.01237599308.785487511959574.0425286.6717583331969.5313.9810577.8751.92555299.995972.50.0012506595830.0619261666717
    18
    		2025-05-27 00:00:00.0002.06199996728.0441666671154427.916903.85173980914.937877016504001453.88756751750.586.856666667899016362.595.8258256.525320.762584.97581.2599784113.7160224176311.5252.00479166799.4456.516.7612054901351567.5536278.5566.912510423.992849.0565.157519.411199308.785487511959574.0425286.6717583331969.5313.9810577.8751.92555299.995972.50.0012663254170.0639395833318
    19
    		2025-05-26 00:00:00.0002.06199996728.044166667115450916903.46514.0031413646504001453.88756751750.586.856666667899016362.595.8258256.525320.762584.97581.259978481.592756311.5252.26551350899.4456.516.7512054901351567.5536278.5566.912510423.992849.0565.157519.411199308.785487511959574.0425286.6717583331969.5313.9810577.8751.92555299.995972.50.0012734758330.0658982916719
    20
    		2025-05-25 00:00:00.0001.76899486928.044166667115450916903.16062513.9816373986503891453.88756751750.586.856666667899016362.594.2166983338256.525320.762580.84666666781.259978475.26311.5252.14807692399.4456.516.139512054901351567.5536278.5566.912510423.992849.0565.157519.8815405599308.785487511959574.0425286.6717583331969.5313.9810577.8751.66492299.995972.50.00126938250.06485187520
    90
    42KB
    55s