SajjadiiiUntitled Query
Updated 2023-02-22Copy Reference Fork
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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