flipside

    ship_prices
    Aephiaship_prices
    Updated 4 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 4 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-05-30 00:00:00.0002.06199996724.21525947.925427.916903.40833333313.8862071936506495375.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.995954.863750.00116237750.058182166671
    2
    		2025-05-29 00:00:00.0002.06199996724.215251154427.916903.40833333315.766506495375.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.060901416672
    3
    		2025-05-28 00:00:00.0002.06199996728.0441666671154427.916903.85173980914.1945587066506495375.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.061926166673
    4
    		2025-05-27 00:00:00.0002.06199996728.0441666671154427.916903.85173980914.9378770165064954001453.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.063939583334
    5
    		2025-05-26 00:00:00.0002.06199996728.044166667115450916903.46514.00314136465064954001453.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.065898291675
    6
    		2025-05-25 00:00:00.0001.76899486928.044166667115450916903.16062513.98163739865064953891453.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.0648518756
    7
    		2025-05-24 00:00:00.0001.828.79115450916903.384313.98163739865064953991453.88756751750.591.98899016362.594.2166983338256.525320.762584.9584.76981.81256311.5252.27581997899.4456.516.643312512054901351567.5536278.5566.912510423.992849.0565.157520.3252599308.785487511959574.0425237.51969.5313.9810577.8752.31686515299.995972.50.0013181833330.067019958337
    8
    		2025-05-23 00:00:00.0001.852527.3505115450916903.384315.734999565064953991453.8875651.11750.591.98899016362.5958256.525320.762584.9584.76981.81256311.5252.05333333399.4456.516.48387512054901351567.5536278.5566.912510423.8802752849.0565.157519.866387599308.785487511959574.0425245.7528846151969.5313.9810577.8752.031889718299.995972.50.0014004433330.071562416678
    9
    		2025-05-22 00:00:00.0002.17689188327.865134950916903.421658415.73499956506495416.5751453.8875651.11750.591.98899016362.5958256.525320.762584.9584.76981.81256311.5252.07248958399.4456.517.812054901351567.5536278.5566.912510423.8413752849.0565.157520.9599308.78548751179.3333333339574.0425245.7528846151969.5313.9810577.8752.031889718299.995972.50.0013956683330.071342291679
    10
    		2025-05-21 00:00:00.0002.17689188328.5134950916903.421658415.73499956506495416.5751453.8875651.11750.591.98899016362.583.5227483758256.525320.762584.9584.79806311.5252.07248958399.4456.517.26187512054901351567.5536278.5566.912510423.8413752849.0547.520.7899308.78548751179.3333333339574.0425259.368751969.5313.9810577.8751.953865299.995972.50.00133718750.0697139583310
    11
    		2025-05-20 00:00:00.0002.17689188328.5134950916903.421658414.881921256506495416.5751453.8875651.11750.591.98899016362.583.5227483758256.525320.762584.9584.79806311.5252.3581525199.4456.518.16999966712054901351567.5536278.5566.912510423.8413752849.0547.519.96666666799308.78548751179.3333333339574.0425261.28751969.5313.9810577.8751.953865299.995972.50.0013262950.0686413333311
    12
    		2025-05-19 00:00:00.0002.17689188328.9134950916903.421658415.766506495416.5751453.8875651.1174591.98899083.5227483758256.525320.762584.9584.79806311.5251.99885354499.4456.518.181512054901351567.5536278.5566.912510423.8413752849.0547.518.0599308.78548751179.3333333339574.0425269.36251969.5313.9810577.8752.029999299.995972.50.001332101250.0692386666712
    13
    		2025-05-18 00:00:00.0002.17689188328.91349467.52516903.421658414.966506495416.5751453.8875651.11647.591.98899097.958256.525320.762584.9584.7993.97529002.02677162999.4456.518.181512054901351567.5536278.5566.912510423.8413752849.0547.520.45982599308.78548751179.3333333339574.0425288.0809251969.5313.9810577.8751.961532299.995972.50.001374160.0722694583313
    14
    		2025-05-17 00:00:00.0002.17689188327.75581251349467.52516903.734414.9027185696506495365.1251453.8875651.11647.595.233333333899098.998256.525320.762594.18777766784.7778.99333333329001.92741351780.63956.518.181512054901351567.5536278.5566.912510423.60448752849.0547.519.712599307.266141159.229192287.445451969.5313.9810577.8751.827895815214.89375972.50.0013661541670.0722087916714
    15
    		2025-05-16 00:00:00.0001.922637528.49276251349467.52516903.9415.82627.1756495365.1251410.125651.11647.595.2333333337770.27598.998256.525320.762594.18777766784.7778.99333333329001.93527575.096456.52012054901351567.5536278.547910423.2227252849.0547.518.54640789599307.266141060.0259192287.445451969.5313.9810577.8751.85152145214.89375972.50.001434690.0756779166715
    16
    		2025-05-15 00:00:00.0001.98857142928.400891349467.52516903.9415.82627.1756495365.1251410.125651.11647.595.2333333337770.27586.6566666678256.525320.762594.18777766784.7778.99333333329001.878251875.096456.519.112054901351567.5536278.547910423.2172849.0547.519.17171019499307.266141060.0259192287.445451969.5313.9810577.8751.926627125214.89375972.50.001457866250.0770620833316
    17
    		2025-05-14 00:00:00.0001.8584587828.3333333331349467.52516903.9415.30775627.1756495324.706251410.125651.11647.595.2333333337770.27586.6566666678256.525320.762594.18777766784.7780.49382529001.84752575.096456.520.5862512054901351567.5536278.547910423.1832849.0547.518.012647599317.511060.0259192287.445451969.5313.9810577.8751.926627125214.89375972.50.00153327250.08157637517
    18
    		2025-05-13 00:00:00.0001.77526954528.333333333921.5467.52516903.35592391315.14178645627.17564953791410.125651.11647.595.2333333337770.275928256.525320.762594.18777766784.7780.95879583329001.77073113575.096456.4521.61205490135536278.547910423.1289651722849.0547.520.51002599296.6220083331060.0259192287.445451969.5313.9810577.8751.829351347214.89375972.50.001517021250.0804758333318
    19
    		2025-05-12 00:00:00.0001.737600228.0785625921.5467.52516903.32914.93926916763064953791410.125651.12.39267062595.2333333337770.27595.595758256.525320.762594.18777766780.385180.95879583329001.7135473375.096456.421.61205490135536278.547910423.132849.0547.520.645812599268.7451060.0259192295.104151969.5313.9810577.8751.704705111214.89375972.50.0015943233330.0834834166719
    20
    		2025-05-11 00:00:00.0001.8474412928.739991667921.5467.52516903.39412516.763064953791410.125651.12.392670625927770.27595.595758256.525320.762594.18777766779.51693280.95879583329001.76817939982.0794.9919.03463751205490135536278.547910423.0439252849.0547.522.599268.745971.52759192310.3918694441969.5313.9810577.8751.450100693214.89375972.50.001595113750.0834601666720
    90
    42KB
    26s