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-25 17:54:08.484Decrease Long
    Liquidation
    0168.86199833220.573341-673.7439726.21754564.773829
    3AUuM3kJCJZ3zg8gd1PJ5S2fh4YT9uYeAs3apHbRsFRY4u6zFJCsKvttiubcfZHsmdTGYy1nxJ7nGrEjQc7sWsds
    2
    sol2024-10-25 16:11:57.367Increase Long
    Market
    -693.635112172.54604726651.834096018.6736190
    5TNpa5J5WR9BT7nMsWi1LUz5AFbHofnniUs5NujvueE7kjc1nzBMctauWcxXRHPSehynagqCbdVT5nUFDSoYfNnA
    3
    sol2024-10-25 16:08:51.143Increase Long
    Market
    -94.371969171.5970456568.73924504.5981180
    3i9kP68dRS7byujXDtc8oFvuBwZxDMNn1CSEp59mNm81jj94AAgcfi5KhtLFxWMBGgn1L6GnGkVxuGSWD6DVuDD6
    3
    610B
    8s