flipside

    User Perps Trading History copy
    daphmelUser Perps Trading History copy
    Updated 2025-01-30
    -- forked from flyingfish / User Perps Trading History @ https://flipsidecrypto.xyz/flyingfish/q/J38q8EPtF1c2/user-perps-trading-history

    with get_data as (
    select
    --live.udf_api('https://perp-api.jup.ag/trpc/trades?batch=1&input=%7B%220%22%3A%7B%22json%22%3A%7B%22wallet%22%3A%22{{address}}%22%2C%22limit%22%3A{__{trades_count}}%2C%22offset%22%3A0%7D%7D%7D') as resp
    livequery.live.udf_api('https://perp-api.jup.ag/trpc/trades?batch=1&input=%7B%220%22%3A%7B%22json%22%3A%7B%22wallet%22%3A%22{{address}}%22%2C%22limit%22%3A10000%2C%22offset%22%3A0%7D%7D%7D') as resp
    )

    , date_filtered as (
    select
    iff('{{full_trade_history}}' = 'Yes', '2023-07-18', '{{start_date}}') as start_date_filter
    , iff('{{full_trade_history}}' = 'Yes', current_date, '{{end_date}}') as end_date_filter

    )

    , parsed_data as (

    select
    value:updatedAt::timestamp as timestamp
    , value:side as side
    , value:requestType as order_type
    , value:requestChange as request_change
    , value:custody as custody
    , value:mint as market
    , value:collateralMint as collateral_mint
    , value:collateralUsd / 1e6 as collateral_usd
    , zeroifnull(value:sizeUsd) / 1e6 as size_usd
    , zeroifnull(value:feeUsd) / 1e6 as fee_usd
    , zeroifnull(value:liquidationFeeUsd) / 1e6 as liquidation_fee_usd
    , value:price / 1e6 as price
    , value:priceSlippage as price_slippage
    , zeroifnull(value:pnlUsd) / 1e6 as pnl
    , value:positionKey as position_key
    , value:txId as tx_id
    from get_data
    , lateral flatten (input => resp:data[0]:result:data:json:trades)
    Last run: about 1 month ago
    SYMBOL
    TIMESTAMP
    ACTION
    ORDER_TYPE
    DEPOSIT_WITHDRAWAL
    PRICE
    SIZE_USD
    PNL
    FEE_USD
    LIQUIDATION_FEE_USD
    TX_ID
    1
    		sol2024-10-30 02:41:36.496Decrease Long
    Market
    		190944.245673179.769652494364.4589683092.6739987453.5725750
    3WJWz2CVJQXRuMDuFtBidCJvXiBefjMMiM8G4UnB7VY9b1mwVsCxAebnZT5LscBWDrj4bRhxJGKeCa8JgC9JCpFt
    2
    		sol2024-10-29 19:26:50.009Increase Long
    Market
    		-54685.865909179.7576291073688.15197801611.249670
    2nNgU492UR15LdZ8sTKXjWxfnURNVyNwKqDJsvrz8mffKGkEw4x77sjdF5AvF5WmmEcQyTE6MfwQrcYrKvDZeUfS
    3
    		sol2024-10-29 19:26:16.548Increase Long
    Market
    		-35926179.63355694.5423370321.1549270
    5SXk2Z4aqHg7v7GeDQd6i9NMcHvDu5bDnbvMfLD4KapQvtWcoPawQ68YmKX9x3pa7q8P8EzdvoJJVwEdGrd2vVa8
    4
    		sol2024-10-29 19:24:59.824Increase Long
    Market
    		-35876179.38355150.0814850319.8749980
    5D81QcAHtEZWZNrPT8ADKYh9jT9CKqNM1saAGbmFwLt5QcBxibyYWibPd12e5wgrf39Lv1UBUnhcJUC1Y1AyTDMZ
    5
    		sol2024-10-29 19:24:36.633Increase Long
    Market
    		-35897.553564179.487767355346.5346530319.9589940
    5WGTZQsD9mSufWdJvdJLfHnBAB6iQwF6QpiLPXJvinXjeJvxHgqDpzDHqd7HtHHgo13fi6uNSYcuknuCfa58J6Zx
    6
    		sol2024-10-29 19:24:03.841Increase Long
    Market
    		-35811179.055354485.1485150319.0366340
    4QTsDLVfbUwbej1fmQEbKiYQEU14pgLC15RQzqVf5QYg5YxxxriitHqaZ3ttep3AxvxFq8yWpwkjGSSVHtgLvUtP
    7
    		sol2024-10-29 17:06:15.822Decrease Long
    Trigger
    		32597.086429182.8288566.3388113988.826436284.0338280
    vtDWksHcmNPQgfaPM6KKaDErnVcRm928PWzXVGavEEzuk29c9PYRvugYnxLhZeyjF7szfNm9PcTyumXrRfAauHY
    8
    		sol2024-10-29 15:18:17.957Increase Long
    Market
    		-17954.0185179.540185177619.3688710198.40530
    5wvRNLVtSMXWmvtk7teX78tKm4b5cTMSCU62CZTwHhyh9q5VDTnpUVV6XoaSnaKtjZVMumHaDUc3kY4yPEJ4MEZ
    9
    		sol2024-10-29 02:41:23.584Increase Long
    Market
    		-11214.3435181.55110946.96994077.6628790
    2m1TCN2cDyPiM7tuxR1DTQymSpKUMWKabKshWmFAkDboKfdb6BD1cPNyLnE7ZKc8MrXxSDkkWGh8cdQexKuSRt1D
    10
    		sol2024-10-25 23:23:10.340Decrease Long
    Liquidation
    		0160.2952497579.860407-181506.7599828219.6152453262.107556
    5wDQA8A4b8XwzvRs6g8Ddr5MBbX6qgPAz99M5eyLPUH46s2EQ7dLWefAPzjq7tPfNRXiooRuv6EW3PQHHqvdaCzJ
    11
    		sol2024-10-25 10:15:58.354Increase Long
    Market
    		-156.9022172.420018.0849760
    22bPjtBf7QDwuYNasxA8QTkmoDy7LPJg2wDxN1sFqfj7y4EAfyNNfB9wdz8zLPavEgZuA4s8T68w1muWCkRpwvuq
    12
    		sol2024-10-25 10:06:49.877Increase Long
    Market
    		-92645.746071172.827194002.3127590
    xRxqfRcz4npkJPvWYHVA3A3orVrWK9ZkQJGTjL1ESfWJ4GVjiAzLgCMTWDVTvTqExmfg95t7Kv6JSopecLaD6dV
    13
    		sol2024-10-25 10:05:40.454Increase Long
    Market
    		-34566.092172.83046483493.7065040483.918680
    5rvWVZJgaRVVVDpnb7Rte8Qrnkv3FTAT2GofaZ24mmSkjsSpNs2uqTacBjenSjeM7qqiaGAQE99kBYz3B3BPzW9S
    14
    		sol2024-10-25 10:05:24.905Increase Long
    Market
    		-34567.374474172.8368721006980.58252401510.6571890
    KqHDXYBBwWWUdL3QMvwzuR18e9qEsgWEPFTRmA2qnroiPNMCBteTNDTVRNjeMtLjxY6YM8ioj7vK3Ud9ZCmRzfF
    15
    		sol2024-10-25 10:05:10.328Increase Long
    Market
    		-34578172.891007105.57137901510.6583580
    5gK17jm76sUqg2gXxh1ddaEvmGrvqh4gSjss2bpLU3uyg6fWsBG7g9by5J1iyviYQSMpdQuSp7wyGSrHpfQ83CBY
    15
    3KB
    6s