piperSolana Candlestick Tutorial copy
    Updated 2025-04-19
    -- forked from Sebly / Solana Candlestick Tutorial @ https://flipsidecrypto.xyz/Sebly/q/EWTQtoGQZS2T/solana-candlestick-tutorial


    -- api call data
    with gecko_call_data as (
    select live.udf_api('https://api.coingecko.com/api/v3/coins/solana/ohlc?vs_currency=usd&days=30&precision=3')
    as response
    )


    -- flatten data into rows
    , gecko_flattened_data as (
    select * from
    gecko_call_data, lateral flatten(input => parse_json(response:data))
    )

    /*
    (used to comment out blocks of code,
    pro tip - highlight a block of tect and hit ctrl+/)

    ------- quick note on arrays-------------
    data is returned in an array ex. [0, 1, 2, 3, 10]
    in the example above the array value[0] = a numerical value of 0, value[1] = 1... value[4] = 10
    ---------------------------------------
    */


    , gecko_column_data as (
    -- extract all flattened data into columns.
    select
    -- timestamp retuned in UNIX, convert to UTC. Value must be passed as 'text'
    -- if you're familiar with strings. Varchar is an efficient way to manage 'string' types, could also cast as char, string, etc.
    TO_TIMESTAMP(cast(value[0] as varchar)) as time
    , value[0] as timestamp
    , value[1] as open
    , value[2] as high
    Last run: 3 months ago
    RESPONSE
    SEQ
    KEY
    PATH
    INDEX
    VALUE
    THIS
    1
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[0]0
    [1742472000000,134.286,134.439,130.58,130.868]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    2
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[1]1
    [1742486400000,130.627,131.801,128.57,128.57]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    3
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[2]2
    [1742500800000,128.709,128.947,126.977,127.873]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    4
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[3]3
    [1742515200000,127.839,128.289,127.295,127.676]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    5
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[4]4
    [1742529600000,127.819,128.84,127.526,127.891]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    6
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[5]5
    [1742544000000,128.023,128.649,127.508,128.06]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    7
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[6]6
    [1742558400000,127.973,128.826,125.93,126.417]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    8
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[7]7
    [1742572800000,126.34,127.468,125.484,125.919]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    9
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[8]8
    [1742587200000,125.903,129.04,125.344,128.115]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    10
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[9]9
    [1742601600000,128.104,129.693,127.775,128.24]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    11
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[10]10
    [1742616000000,128.322,129.069,128.008,128.975]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    12
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[11]11
    [1742630400000,128.852,129.515,128.789,129.045]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    13
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[12]12
    [1742644800000,128.855,131.455,128.853,130.295]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    14
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[13]13
    [1742659200000,130.33,130.417,128.706,129.027]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    15
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[14]14
    [1742673600000,129.146,130.038,129.015,129.905]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    16
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[15]15
    [1742688000000,129.955,129.955,128.208,128.392]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    17
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[16]16
    [1742702400000,128.412,130.4,128.398,130.4]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    18
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[17]17
    [1742716800000,130.536,131.161,129.864,131.161]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    19
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[18]18
    [1742731200000,131.16,133.68,131.033,132.923]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    20
    {"bytes":8551,"data":[[1742472000000,134.286,134.439,130.58,130.868],[17...
    1[19]19
    [1742745600000,132.967,133.643,132.445,132.656]
    [[1742472000000,134.286,134.439,130.58,130.868],[1742486400000,130.627,1...
    ...
    180
    3MB
    4s