Add table dex_message_event_tick_update_of_price_movement with witer

[bro-n-bro-0]

table definition

CREATE TABLE spacebox.dex_message_event_tick_update_of_price_movement
(
    `timestamp` DateTime,
    `height` Int64,
    `TokenZero` String,
    `TokenOne` String,
    `TickIndex` Int64,
    `price` Float64
)
ENGINE = MergeTree
ORDER BY (timestamp,
 height,
 TokenZero,
 TokenOne,
 TickIndex,
 price)
SETTINGS index_granularity = 8192

writer definition

CREATE MATERIALIZED VIEW spacebox.dex_message_event_tick_update_writer TO spacebox.dex_message_event_tick_update
(
    `timestamp` DateTime,
    `height` Int64,
    `txhash` String,
    `attributes` String,
    `signer` String,
    `TokenZero` String,
    `TokenOne` String,
    `TokenIn` String,
    `TickIndex` Int32,
    `TrancheKey` String,
    `Fee` UInt16,
    `Reserves` UInt256
) AS
WITH
    raw_tx AS
    (
        SELECT
            timestamp,
            height,
            txhash,
            events,
            signer
        FROM spacebox.raw_transaction
        WHERE code = 0
    ),
    raw_tx_event AS
    (
        SELECT
            timestamp,
            height,
            txhash,
            type,
            attributes,
            signer
        FROM raw_tx
        ARRAY JOIN
            arrayMap(x -> JSONExtractString(x,
 'type'),
 JSONExtractArrayRaw(events)) AS type,
            arrayMap(x -> JSONExtractString(x,
 'attributes'),
 JSONExtractArrayRaw(events)) AS attributes
    ),
    dex_message_event AS
    (
        SELECT
            timestamp,
            height,
            txhash,
            type,
            attributes,
            JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'action'),
 JSONExtractArrayRaw(attributes))[1],
 'value') AS action,
            signer
        FROM raw_tx_event
        WHERE JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'module'),
 JSONExtractArrayRaw(attributes))[1],
 'value') = 'dex'
    )
SELECT
    timestamp,
    height,
    txhash,
    attributes,
    signer,
    JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'TokenZero'),
 JSONExtractArrayRaw(attributes))[1],
 'value') AS TokenZero,
    JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'TokenOne'),
 JSONExtractArrayRaw(attributes))[1],
 'value') AS TokenOne,
    JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'TokenIn'),
 JSONExtractArrayRaw(attributes))[1],
 'value') AS TokenIn,
    toInt32(JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'TickIndex'),
 JSONExtractArrayRaw(attributes))[1],
 'value')) AS TickIndex,
    JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'TrancheKey'),
 JSONExtractArrayRaw(attributes))[1],
 'value') AS TrancheKey,
    if(empty(TrancheKey) = 1,
 toUInt16OrZero(JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'Fee'),
 JSONExtractArrayRaw(attributes))[1],
 'value')),
 0) AS Fee,
    toUInt256(JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'Reserves'),
 JSONExtractArrayRaw(attributes))[1],
 'value')) AS Reserves
FROM dex_message_event
WHERE action = 'TickUpdate';

[iljagrabar14]

don't see price in writer

[iljagrabar14]

looks like you put writer for another table by accident

[bro-n-bro-0]

-- spacebox.dex_message_event_tick_update_of_price_movement_writer source

CREATE MATERIALIZED VIEW spacebox.dex_message_event_tick_update_of_price_movement_writer TO spacebox.dex_message_event_tick_update_of_price_movement
(

    `timestamp` DateTime,

    `height` Int64,

    `TokenZero` String,

    `TokenOne` String,

    `TickIndex` Int64,

    `price` Float64
) AS
WITH
    raw_message_with_index AS
    (
        SELECT
            timestamp,

            height,

            txhash,

            msg,

            msg_index,

            msg_type,

            events,

            signer
        FROM spacebox.raw_transaction
        ARRAY JOIN
            JSONExtractArrayRaw(tx,
 'body',
 'messages') AS msg,

            arrayMap(x -> JSONExtractString(x,
 '@type'),
 JSONExtractArrayRaw(tx,
 'body',
 'messages')) AS msg_type,

            arrayEnumerate(JSONExtractArrayRaw(tx,
 'body',
 'messages')) AS msg_index
        WHERE code = 0
    ),

    raw_message_event_with_index AS
    (
        SELECT
            timestamp,

            height,

            txhash,

            msg,

            msg_index,

            msg_type,

            type,

            attributes,

            event_index,

            signer
        FROM raw_message_with_index
        ARRAY JOIN
            arrayMap(x -> JSONExtractString(x,
 'type'),
 arrayFilter(x1 -> ((toUInt32OrNull(JSONExtractString(arrayFirst(x2 -> (JSONExtractString(x2,
 'key') = 'msg_index'),
 JSONExtractArrayRaw(x1,
 'attributes')),
 'value')) + 1) = msg_index),
 JSONExtractArrayRaw(events))) AS type,

            arrayMap(x -> JSONExtractString(x,
 'attributes'),
 arrayFilter(x1 -> ((toUInt32OrNull(JSONExtractString(arrayFirst(x2 -> (JSONExtractString(x2,
 'key') = 'msg_index'),
 JSONExtractArrayRaw(x1,
 'attributes')),
 'value')) + 1) = msg_index),
 JSONExtractArrayRaw(events))) AS attributes,

            arrayEnumerate(arrayFilter(x1 -> ((toUInt32OrNull(JSONExtractString(arrayFirst(x2 -> (JSONExtractString(x2,
 'key') = 'msg_index'),
 JSONExtractArrayRaw(x1,
 'attributes')),
 'value')) + 1) = msg_index),
 JSONExtractArrayRaw(events))) AS event_index
    ),

    dex_message_event AS
    (
        SELECT
            timestamp,

            height,

            txhash,

            msg,

            msg_index,

            msg_type,

            type,

            attributes,

            event_index,

            JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'action'),
 JSONExtractArrayRaw(attributes))[1],
 'value') AS action,

            signer
        FROM raw_message_event_with_index
        WHERE JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'module'),
 JSONExtractArrayRaw(attributes))[1],
 'value') = 'dex'
    ),

    dex_message_event_tick_update AS
    (
        SELECT *
        FROM dex_message_event
        WHERE action = 'TickUpdate'
    )
SELECT
    timestamp,

    height,

    JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'TokenZero'),
 JSONExtractArrayRaw(attributes))[1],
 'value') AS TokenZero,

    JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'TokenOne'),
 JSONExtractArrayRaw(attributes))[1],
 'value') AS TokenOne,

    toInt64(JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'TickIndex'),
 JSONExtractArrayRaw(attributes))[1],
 'value')) AS TickIndex,

    pow(1.0001,
 TickIndex) AS price
FROM dex_message_event_tick_update
WHERE (msg_type = '/neutron.dex.MsgPlaceLimitOrder') AND (JSONExtractString(msg,
 'tokenOut') = JSONExtractString(arrayFilter(x -> (JSONExtractString(x,
 'key') = 'TokenIn'),
 JSONExtractArrayRaw(attributes))[1],
 'value'))