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: 3 days ago
    SYMBOL
    TIMESTAMP
    ACTION
    ORDER_TYPE
    DEPOSIT_WITHDRAWAL
    PRICE
    SIZE_USD
    PNL
    FEE_USD
    LIQUIDATION_FEE_USD
    TX_ID
    1
    		sol2024-05-10 16:12:54.708Decrease Long
    Market
    		94361.301343147.855025276477.9513782653.81573279.1017280
    3oz7DMDUrkW3SRRGkDvopdSSceXyvVPcNaiacgj4dGUx7dJcYnBtLNL4UG7EvtxLghyQ8f6jjqPH17TLSd6biihL
    2
    		sol2024-05-10 16:02:04.675Increase Long
    Market
    		-92263.065293146.449309276477.9513780276.4779520
    3PgcuVqC7iGpU6c7f9Wmy4w9RnFxFjQDmXM7wrnTJ7NR1DjksncTDzxT3itUn6sANfyvfJ9asktjjPbYMxLfqzRS
    3
    		sol2024-05-08 09:09:23.126Decrease Long
    Market
    		92134.249116146.005296812.71946-6301.793918466.6284780
    5uScShHumRgTM7DYw985T4z4fgmUs5b9wfeBHTnYfDGBGYBpefieh1nEWTx7Ap4uDgEDyKkjJUUArLyemtqHp6Yp
    4
    		sol2024-05-08 00:19:57.756Increase Long
    Market
    		-99199.484232149.172156296812.719460296.812720
    5m3mGXuPQKtYhTRAeqfr6dAfgDSFTULBfDVoC3NYWTdbXoNdNQNsnzYLNvVRT42e3j5wS4gxn5dBh2cs82XgrVaB
    5
    		sol2024-05-06 15:08:52.002Decrease Long
    Market
    		101179.94076152.147607220984.17921229180.5920421699.8184840
    5uDeMeBCXMuXYmnGEh6ffrmAjnRgkm7ZvaFP6X1nYKTj2xrQL7FUMXs71T9XzDwkBHRn84kure1yGhiQhuULcKc4
    6
    		sol2024-05-02 00:03:20.980Increase Long
    Market
    		-73920.151382134.400275220984.1792120220.984180
    2zHgnuPivoUE9shmwvBTFJodMBZkgHV9yXKExDr8f8Xifis2aAjAKrVRGca2gS5meS2yy6VU7nQw2Y8kTXEQbK62
    7
    		sol2024-05-01 19:04:31.848Decrease Long
    Market
    		90147.911586133.524423214913.89178319139.852398501.1871480
    4Y85Q9MBMSq3yZnQCKT5GHHdsgRGBTV6CqiKFJgLs19q6fgUobkzuWQ53Dvb8PvVs4KMTztiDrT7UPUDy4GoPB68
    8
    		sol2024-04-30 19:50:32.866Increase Long
    Market
    		-71724.160228122.605402214913.8917830214.9138920
    23eaeHeaFhv1vSFLyQVpon6vtzYrfo3oJgL5jwy6yRy1JtUt8VJm3HSsWo5D2G7TTQt4TGSzhPj6PnPFfPZgFmpP
    9
    		sol2024-03-15 03:16:53.492Decrease Long
    Market
    		878701.142069180.0745441478492.209581144587.032082556.6028160
    51jDiECZqTJTEeiudAiC2CjXAjWvN4XWGpUeEWfhp2a9ZJL7ZLpbUckh3quwaSvDPDKgHrYiDXKdowdKSrA69cUC
    10
    		sol2024-03-14 18:58:46.894Increase Long
    Market
    		-738149.205015164.0331561478492.20958101478.492210
    3s4K8vq2pdeMp6jdV796QcVLTRTF8GayvZ7FCNRVUoq7oMTefgFdpqzn4bgXCX2Xr7ohb6pppTH2nGNaRM9s31CX
    11
    		sol2024-03-13 13:13:43.696Decrease Long
    Market
    		894695.221637153.0682771487637.765269159494.3502269842.2664990
    2ggjqL3osFSG5FJLu5g7jvzHHkg2nGZdv3V36WPRe3xewxcMPhUH7sz3cm4M9m3tzQZ4mqWRxDHSe7xHiVQvpdQs
    12
    		sol2024-03-11 01:41:50.069Increase Long
    Market
    		-746530.775676138.2464391487637.76526901487.6377660
    GybgLERM2E3GDJkxbdtQ76gS1YqTBJJPAyvdaiuy71unqUfPcZsKVXsLnFmzN3u5cH1NhDmk1BSKRzjHrcZzyzt
    13
    		sol2024-03-10 23:24:19.759Decrease Long
    Market
    		844694.872655143.700361440456.378244140071.48361314244.5895790
    5fuH2deLwLXu6eaVn9csf723XKkpugPgz2r5WGs7Fr4EMxv9ZEdsYU5LTdCsAMyjRGZ1FwSr5AXibYTm1ReDJuhZ
    14
    		sol2024-03-06 19:59:26.450Increase Long
    Market
    		-720308.435130.965171440456.37824401440.4563790
    dA5cA3E4t4KV9r42FYkrcU7czykPsmTk5477oXHeksgWaKpAYzUViSFTfqfcuuXRVdNqHAqYLWJbjyfjdxZgUWt
    15
    		sol2024-03-06 14:04:17.431Decrease Long
    Market
    		584671.477528128.8271089374.78343342830.8978922268.5205860
    28MZuM8SdiogA34JfXnCYkqncVokUMhbMnhekatFMr7QUdwquN3PXSkvD1ifCd8rL2ozicFbPZmtpszB61UHMAFX
    16
    		sol2024-03-06 00:37:37.766Increase Long
    Market
    		-264813.082338126.101467528918.6796410726.2760120
    3yGfK73E4RHSBcRDU4jEbweGpZV6YYsbFcfCb58srPFzLK89pCyek1Xqe2BBCcBiP9KkU4uPsmYy6ZMDTbxZovM3
    17
    		sol2024-03-05 20:11:13.019Increase Long
    Market
    		-280582.75121.9925560456.1037920560.4561040
    3dfvsdUdgLmbMratKk6LXVncchivgu6fjjTRPvLiDeYLmXjuMtfjy8jytLHyuuuXsMi22Cwv598yK9HLY3F9nKKi
    18
    		sol2024-03-04 00:08:09.413Decrease Long
    Market
    		757303.668888131.1599991488530.75648717030.8682313877.5199130
    2uwiKSo1hHHd8QxpD6M3GdHrPNgsXSbTatpBH15f9qadrdVdkEBfybeCMJdY4T2NGR7toyR3Er3B9g4EZvEMoBSi
    19
    		sol2024-03-03 05:26:29.624Increase Long
    Market
    		-745638.851327129.6763211488530.75648701488.5307570
    52ika4w4NnW5jXmuoNeunkgRk26wL4efSE1JxupiFBN63T3jWzsHLPB4zCMxP5NEcNqW6aP7zBDjjzPecKYb1CXp
    20
    		sol2024-03-01 19:01:36.767Decrease Long
    Market
    		573269.793553133.2826921042261.454391106266.2403194281.9258610
    3G1qZKHVmPudaL6qTjpZCWpMrhwKEQwViU6oFpeh2Yy2G81FbAogNVKPQ6rqgvCg6kXPdaMowvARGWyciyCzfLsm
    39
    8KB
    9s