SajjadiiiUntitled Query
    Updated 2023-02-22
    -- 2023-02-12 11:29:22.000
    WITH base1 AS (
    SELECT block_timestamp , tx_hash , from_address AS trader_address ,
    tokenflow_eth.hextoint (substr(input_data, 11 + (64 ) ,64)) AS pair_Index ,
    tokenflow_eth.hextoint (substr(input_data, 11 + (64 * 4) ,64))/pow(10,18) AS position_Size_Dai ,
    round(tokenflow_eth.hextoint (substr(input_data, 11 + (64 * 5) ,64))/pow(10,10) ,8) AS open_Price , -- becuz some tokens have low price usd like $SHIB
    iff(tokenflow_eth.hextoint (substr(input_data, 11 + (64 * 6) ,64)) = 1 ,'Long' ,'Short') as type_of_trade,
    tokenflow_eth.hextoint (substr(input_data, 11 + (64 * 7) ,64)) AS leverage ,
    (position_Size_Dai * leverage) * (0.08/100 ) AS open_fee_dai,
    (position_Size_Dai * leverage) - open_fee_dai AS Collateral ,
    ((open_Price * (Collateral * 0.9))/Collateral)/leverage AS Liquidation_Price_Distance,
    CASE WHEN type_of_trade = 'Long' THEN open_Price - Liquidation_Price_Distance
    WHEN type_of_trade = 'Short'THEN open_Price + Liquidation_Price_Distance
    END AS Liquidation_Price ,
    round(tokenflow_eth.hextoint (substr(input_data, 11 + (64 * 8) ,64))/pow(10,10) ,8) AS Target_point ,
    round(tokenflow_eth.hextoint (substr(input_data, 11 + (64 * 9) ,64))/pow(10,10) ,8) AS Stop_loss ,
    round(tokenflow_eth.hextoint (substr(input_data, 11 + (64 * 12) ,64))/pow(10,10) ,8) AS slippage_Percent
    FROM polygon.core.fact_transactions
    WHERE to_address = '0x65187fec6ecc4774c1f632c7503466d5b4353db1' -- Gains Network: Trading
    AND block_timestamp::date >= '2023-02-01'
    AND origin_function_signature = '0x9aa7c0e5' -- openTrade
    AND status = 'SUCCESS'
    ----------- I tried a lot to fix the data of these transactions in the tables,
    --------------- but these 10 transactions were not up to my programming knowledge,
    ------------------and if they were not deleted, the rest of the data would be corrupted.
    AND tx_hash NOT IN (
    '0x1502f956dc0847ce1dc01f5e07acbb8c1dc0fedc1013d81e6150ebeef8843b99',
    '0x8da904b03a1a2a05ae8d2dfa6099ca7d534ed466743da591feeca76eebd06fc4',
    '0x6ce8cae616aa6252ec98546001d63c902d6e82dbf29e07b58d4be53262e9ee30',
    '0xf145fc70f45517c6504c44792d5e5d8615cfc491bf4161645eb9f6c6b98199e9',
    '0x695decf55e3e2d5a095357db24ee7786bcc43b4f07f1dfea8822c6de4173b726',
    '0x7c9cf5e2781d3815df938f26490b4ae8763f7ce6189d1c5861e87543f10844c4',
    '0x88cd8d7d3ed77d98d27b0f170b7c0e2ecc88897050235b3670d9743f60445f09',
    '0x8aca08a79b10f7ce3be964c2dcbe73a6f3ef2351ad838a1f5dbc20bc3011ddea',
    '0xb213c35bf5f48923c4c5b0593cea6eca7dbf941ea9b2b52610c4d884e39a4e80',
    '0xfe31507fd2e61fadfcd89687c95eaf4a2d26731102a14af0cb2d412e91c54db3'
    Run a query to Download Data