princefarzamThe daily amount of bets in USD which partitioned by sports in the last two weeks
    Updated 2022-08-22
    WITH txns as (SELECT
    regexp_substr_all( SUBSTR(DATA, 3, len(DATA)), '.{64}' ) AS segmented_data,
    CASE
    WHEN concat('0x',SUBSTR(segmented_data[2], 25, 40))='0x0000000000000000000000000000000000000000' THEN 'HOME'
    WHEN concat('0x',SUBSTR(segmented_data[2], 25, 40))='0x0000000000000000000000000000000000000001' THEN 'AWAY'
    WHEN concat('0x',SUBSTR(segmented_data[2], 25, 40))='0x0000000000000000000000000000000000000002' THEN 'DRAW'
    END AS Position,
    concat('0x',SUBSTR(segmented_data[1], 25, 40)) AS market,
    ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[3], 25, 40)))/pow(10,18) AS Token_Amount,
    ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[4], 25, 40)))/pow(10,18) AS Expected_Payout,
    concat('0x',SUBSTR(segmented_data[5], 25, 40)) AS Stable_coin,
    concat('0x',SUBSTR(segmented_data[6], 25, 40)) AS Ticket,
    *
    FROM optimism.core.fact_event_logs
    WHERE CONTRACT_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'
    AND ORIGIN_TO_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'
    AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE - 14
    AND TX_STATUS='SUCCESS'
    AND EVENT_NAME IS NULL),

    Market_Tags AS (
    SELECT
    regexp_substr_all( SUBSTR(DATA, 3, len(DATA)), '.{64}' ) AS segmented_data,
    concat('0x',SUBSTR(segmented_data[0], 25, 40)) AS market,
    ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[17], 25, 40))) AS Tags1,
    ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[18], 25, 40))) AS Tags2,
    ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[19], 25, 40))) AS Tags3,
    CASE
    WHEN Tags1 >= 9000 and Tags1 <=9020 THEN Tags1
    WHEN Tags2 >= 9000 and Tags2 <=9020 THEN Tags2
    WHEN Tags3 >= 9000 and Tags3 <=9020 THEN Tags3
    END AS TAG,
    *
    FROM optimism.core.fact_event_logs
    WHERE ORIGIN_FROM_ADDRESS='0x646f0733614cfcaffc72f6147228c72bc782d452'
    AND ORIGIN_TO_ADDRESS='0x2b91c14ce9aa828ed124d12541452a017d8a2148'