flipside

    Close 2 - >= '2024-11-25' and < '2025-01-05'
    ArioClose 2 - >= '2024-11-25' and < '2025-01-05'
    Updated 2025-04-10
    -- forked from Adrena Close Position Short & Long - '2024-09-25' and '2024-11-25' @ https://flipsidecrypto.xyz/studio/queries/3dcfb5f6-00e3-4156-8aa2-277559c5f9b3

    -- forked from flyingfish / Decoding Drift Funding Rate Log Data @ https://flipsidecrypto.xyz/flyingfish/q/MnRm9AGcvBSu/decoding-drift-funding-rate-log-data

    with raw_data as (
    select
    block_timestamp
    , tx_id
    , TO_VARCHAR(BASE64_DECODE_BINARY(replace(value, 'Program data: ', ''))) AS hex_data
    from solana.core.fact_transactions join solana.core.ez_events_decoded using(tx_id, block_timestamp, block_id, succeeded)
    , lateral flatten (input => log_messages)
    where 1=1
    and program_id = '13gDzEXCdocbj8iAiqrScGo47NiSuYENGsRqi3SEAwet'
    and EVENT_TYPE in ('closePositionShort', 'closePositionLong')
    and value ilike 'Program data: xtlzX794jo%'
    and succeeded
    and block_timestamp >= '2024-11-25' and block_timestamp < '2025-01-05'
    )
    select
    block_timestamp
    , tx_id
    -- publicKey
    , utils.udf_hex_to_base58('0x' || SUBSTR(hex_data, 1 + 16, 64)) as owner
    -- publicKey
    , utils.udf_hex_to_base58('0x' || SUBSTR(hex_data, 1 + 16 + 64, 64)) as position
    -- publicKey
    , utils.udf_hex_to_base58('0x' || SUBSTR(hex_data, 1 + 16 + 64 + 64, 64)) as custodyMint
    -- u8
    , utils.udf_hex_to_int(to_char(reverse(to_binary( SUBSTR(hex_data, 1 + 16 + 64 + 64 + 64, 2) ) ) ) ) as side
    -- u64
    , utils.udf_hex_to_int('s2c', to_char(reverse(to_binary( SUBSTR(hex_data, 1 + 16 + 64 + 64 + 64 + 2, 16) ) ) ) ) as sizeUsd
    -- u64
    , utils.udf_hex_to_int('s2c', to_char(reverse(to_binary( SUBSTR(hex_data, 1 + 16 + 64 + 64 + 64 + 2 + 16, 16) ) ) ) ) as price
    -- u64
    , utils.udf_hex_to_int('s2c', to_char(reverse(to_binary( SUBSTR(hex_data, 1 + 16 + 64 + 64 + 64 + 2 + 16 + 16, 16) ) ) ) ) as collateralAmountUsd
    -- u64
    Last run: 19 days ago
    BLOCK_TIMESTAMP
    TX_ID
    OWNER
    POSITION
    CUSTODYMINT
    SIDE
    SIZEUSD
    PRICE
    COLLATERALAMOUNTUSD
    PROFITUSD
    LOSSUSD
    BORROWFEEUSD
    EXITFEEUSD
    POSITIONID
    1
    		2024-12-16 13:20:52.000
    4qry6V4zGXCQYHe7dJCh3R82i3TDZGJH95jCN3MefjbCxiUGMyeCuxbmeZCGSCdZ5mYvfXvz...
    		5PAwRPFatXfvhMbLwjCjeKE5Yr6eJrsXnapvEadTdo1z3CvF3MZ7qDbCn4WobTXgkMST8srTjPdHg29EqbBacY4xJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn1247743366172148601119700247743366771532720324494396400599572
    2
    		2024-12-20 16:52:52.000
    4swkoNbya79sK8QcpHcGoXNoX8qUstDS6McMh3NFG2i5wdsHEu8NTjbgYJWGwqhNtg8KiuL2...
    		GGb6Je5w37sdSYUCEUVcpXFjmUPTEHonbNFUmTqBc4hd93LBEHFofAUV8q9GD3d2z92U2ib5D218315nrDLSWi1FJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn122097621986189580724990022097621901794331025990223535760410072
    3
    		2024-12-17 17:14:02.000
    KggMP98Kcbsma8tYUp32WeYNZGSJuHmeHqunyDg5Y4rDEJpQEVsPznRLT7W1N5z3S8sz4f9R...
    		3ko1kReR4MXjjrkcjzEzfvVfSFjq1sZsCHR8bwKdRuPL9FB6TWLum19d2grRimoRSrMp5tvVuT45P1VeFjjM62qFDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB26324399974284389313439997428028741022248984470400369551
    4
    		2024-12-20 06:56:50.000
    37bWt3rU99GYtJN2zHpqGqYuCmFddx7QpYbDi6FaFCxxCT7fcVzPa2AnzqpDcGxmwh3Hb8f5...
    		G5uKrzQLWMTSHD2KcoYjjC4oT5jkRzsnM4T445AMtv6s2ZGJUFXmQRyyBHFga6g2SiZJnkjZHM9RKNFE7FgKowB6J1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn133662003318818476721001013915707349591677375386069905
    5
    		2024-12-20 22:06:04.000
    4xzaB3yeLc2dZEEckW2HJSquTgMuogkYQSJ2q88rUkc3nDnJj8M1k9yrvHa5ZtkMC2phu1iK...
    		FC83To9p4dycxFV427pbZy6QUAFLSjmGBteSxgeWrnQY973a7u7pkKBkkv7oy3RhwFqcCfNdfU6DaNwTLkPCveyEDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB2631113904156013414663796805200096503729182815338182475319724
    6
    		2024-12-19 01:59:05.000
    4pZRSVnKyDKcm1mXZ9EPqt6yT578TAAzLQd4XhxhoqqW6Qq8aNZGSE22ReYRHGV5GBAti8dZ...
    		DBAMcasPmgSrStwH82DhVSKaWZrsjUR3nz6M3vc13aCsAADkrYAoZsEowDczonRzJ6CRpdKzdkC3cvvZqrB1dmkt3NZ9JMVBmGAqocybic2c7LQCJScmgsAZ6vQqTDzcqmJh24999812761995773900000000999962556328969502090979997569771
    7
    		2024-12-18 20:01:14.000
    4uaZ33FQvJuvbxtA674HLuJmwGTJCTdLpUMTr8oaPUr5P15jhMH9G3tiYvaoguKZiRVXGnVs...
    		2PVJCmKTas6SrkfcfrwMVTnXLPEsnrZKP6ULg4aSTxMnFzzpqNKb7QcafNQteyUaMJewZL4rd1TLAwiygETQnGY3J1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn19138841182921222157183001986704604905461417635818701462253519737
    8
    		2024-12-19 17:29:37.000
    4ws4ywFiU3Tj5VfT5VbG94rdbgnuDhStxmvvSfr1Fms2F6FdAcSAWAyCUx5fE2HnvEeEhM7a...
    		8sqK4u5hMawT298RMdm4TvvmKmxmJS1hCxw9xvU4mCpB4Vyr2Cu9QjvHR29UhLdsnae4rE3epoLK4vYUjzGdiyi7J1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn116787104638202338008330070585614505767742565564229268598259839
    9
    		2024-12-19 04:58:46.000
    3Np49415oq6hJ9QnBsF8SJGDdgUfCLw9m3dhyTK88ShNK7LptFFAPC94A5LjbRzjCXZ23NnS...
    		9CsqxQ9Phh49gbzYge5cC7kMHkZjN97s7i4TQKYMAV266pYcjpmzhKno9aTeBo15jBxT7hEdR42qYtvPBm6eaXE5J1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn1100244247862075076429100200488495619022602902291620160396659754
    10
    		2024-12-19 04:58:48.000
    4GTxkeC8tDCWL5RmAfLtrkKBgRVcSPuJE5Gp1ranx5Hxwyg6rdCAfe1dzXBd9Bt9p27ZPm3k...
    		9KCNW3gVvT9TkP48YkKCCvvZEMjyKiZs5rJjqXzoR7PR6bhUD9Me7D4nFQ9fnx6cFHx8DSD4SN1LyjpAcn1inbEiJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn17852446683207507642910015704893360329496563665892125643119718
    11
    		2024-12-19 23:35:35.000
    42NHgNBYsEvNG6JnFq4hHNX5ufVMfeCRSugf92ggSr6LSHHgdVTWx5k7g3arRMAwCzFE1Gd5...
    		C57QxJGQtzjAG32LZBLT3HC2cG1B9beF722Xm8pg2aif4mieE4Kj39nfJc8TJHaGr4vqYLBKa7dfAtSMhdJ3cJqPJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn1334434932519389257724006688698602439533919595553510909953
    12
    		2024-12-18 15:31:40.000
    djPVEETshGpgPxSR7CHB1v5Gn4mkiHt6wa3q1ZARjDh8Qz3w5WdWZupJLvPKF1FN5xiWSDHL...
    		9CsqxQ9Phh49gbzYge5cC7kMHkZjN97s7i4TQKYMAV266pYcjpmzhKno9aTeBo15jBxT7hEdR42qYtvPBm6eaXE5J1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn13761010263217385538570075220205216248897029073360177039720
    13
    		2024-12-21 18:51:26.000
    33zhcRgeDhrnHs32LMYKDDrRNt7CJZPD1jY5fRKiWYTefHmLnVtyodsH9skvyXuNSVTYUXri...
    		2MMvWqfwRDzLow9fDBsimpa5rUK4t5KePnfYqs93mK5m3tiTuTb4gwd5fcJSjtMJQTNZGp8tkvpdQbS758mrjTxBDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB2631159510146130095839877536501468567966493195255514010135
    14
    		2024-12-22 22:05:23.000
    33neu3RSSL68VVSuyuwy2a3rFbEFeyNXdnwcjsQfk788heFomRmmmUxrxd2uY4ckdNZyy2A8...
    		D3ydAQj5GZavanXZaUvNmUnrstn1EDQibtsCQetGVMb3TqAGM8HgRY9wqdHZPocRFCwRALT9SQoVr2SQGB9oPvLJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn12453803772217881908426002453803770152133382294943926195210422
    15
    		2024-12-22 20:02:26.000
    2mgMJz3TV44CNqVTye8eg5YD4JAJ3MbZtH9WUWWRLwwXWGe7Hjixvan7zzeHhLzfWqPFpiQq...
    		FwdDPPg91n4iwhY4bUvgsxUrrM9Vrvii3CqASKxn5EZm4GT4Wsbd2hRg4CM9nPZomGWZaMGgmQXoAadE4MdFzo78J1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn118241247351780267524900182412473079098054888740291865810339
    16
    		2024-12-22 20:29:57.000
    v5yiqEi6gsdbXxSJ3PMQWzLGeE7GwnjZDWnUESeGpJBg4zCcUDcNqAXvMMWsYEyC83nJMuze...
    		ENAWgUMwKVTszWVvwATfpxZ38vLaaNKzLF5843YaVvZWLwaqQng8snxVNJpSEqEaZCuRythDUGaoDSzyxpB5JnaJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn18866781917178585963870017733563834582052801002921418715710400
    17
    		2024-12-24 19:57:38.000
    2MCt3uBgZQxFSBbWoPHBHMGtdMvAXSc5J9HnPtUboVqfrYWxFALK3gcHBCgMWoHY9QzMSFt1...
    		9KCNW3gVvT9TkP48YkKCCvvZEMjyKiZs5rJjqXzoR7PR6bhUD9Me7D4nFQ9fnx6cFHx8DSD4SN1LyjpAcn1inbEiJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn182744740481950856177800165489480909229051910402171323939910567
    18
    		2024-12-24 15:46:34.000
    5arJQ9cEGb4C3tYgdiLgUqjLUehJrapCEj8weTLRPFT2XzYLTmySuSj7KdhAzVJYS1xKKjef...
    		FsefAx3F7rWtTRy7tTuVMLKUsEQtvuPTxwNYg3sQTVj3CfKhArhni24btXzFq6WfcbgoxMHbn3RYgw9xcde78CbVDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB26316532500000334696163312500014203769808936591046454410557
    19
    		2024-12-30 10:16:18.000
    45rcN8acvoNvuJ7ZWf7ZRj6nxZrb8TxpJCmxfPsYSVGXxKseUPsdzT8GVK6FmK7n2NhWa4Zo...
    		FKPaQjwNWTUXKuWj3uv2KBNMGc8DsZ8fKAV6kJUnM1EK9DRErY25iGGvpXywcCti6g1efLsDzT8mjwrdNQyJd88eDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB2631159688980032032632260400016942162442352255932410793
    20
    		2024-12-31 04:37:22.000
    5X8biwKE33eCQ91N6A3y2gxCJYY8cy5UaE8M3qS6PshkWGFtghDeudf47skq7NBNGHVD3XrP...
    		8VSJXC23PAotsXncsvoBz5XUMSdDdUeMNGVUXwY7uLB377fi51iQnZt7eyvUanPeHsLQdpW2rtw3AZj2qwaqSajsJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn1146172089189676743440014617208031951420423388010857
    ...
    2982
    1006KB
    438s