flipside

    Close 1 - '2024-09-25' and '2024-11-25'
    ArioClose 1 - '2024-09-25' and '2024-11-25'
    Updated 7 days ago
    -- 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 between '2024-09-25' and '2024-11-25'
    )
    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
    , utils.udf_hex_to_int('s2c', to_char(reverse(to_binary( SUBSTR(hex_data, 1 + 16 + 64 + 64 + 64 + 2 + 16 + 16 + 16, 16) ) ) ) ) as profitUsd
    -- u64
    Last run: 7 days ago
    BLOCK_TIMESTAMP
    TX_ID
    OWNER
    POSITION
    CUSTODYMINT
    SIDE
    SIZEUSD
    PRICE
    COLLATERALAMOUNTUSD
    PROFITUSD
    LOSSUSD
    BORROWFEEUSD
    EXITFEEUSD
    POSITIONID
    1
    		2024-11-20 15:10:29.000
    2t7aMkfsierM4r7j91UPxRhXSv6kpJkBJemPgFDkVvaxkEF2ZhWLPWVZvnReQ2zSacevSFsT...
    		4WdPKYEevhxcZhvCtzkF4PYuWqz6PDy3Sxk8LHVdXfuDEa2VmT7dgpjqgx1WiSJMfwtFF7NdoCoNPGvLp4BP2q2UJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn11190670238323657013410003080893290253651911232154190516386449
    2
    		2024-11-20 03:02:09.000
    5qwM12E6wn67vAjYLRgqvK8dqmM2RjPR56TQcEfgq3sbB72fg4FAJMJV7m9s9EKnjs3WhHBm...
    		97RVdUx4dZs1A2kNsofcQ2N7DDauUwqUEKBnkjt2LkA4D1t4Vvr4PWohXM6hqihjzpPLPzYCoL97eNAuvfcdT17WJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn1403414861232498306550016136594018371186496454976379
    3
    		2024-11-21 05:38:51.000
    4rtDZ3CKLPLsfy84uH6y9Kn6DwN4Wu9Khid3Tt4RkCnonvDJYA9vP5KR1LDtsuxitazjHixs...
    		2W86dBwAjHoGoVQM6fXWU96WCi9zJPEKgzLCbYZJJ6Es5gT4G3CVUeU64PvSBtZurwAfc8mzD2Aqsf8uMSBoPMLXJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn1277519943392404352932700277519943067595223620784444042166576
    4
    		2024-11-23 23:36:50.000
    5Q6eyYkEiJpS6ih5wTvUZC4JQBS2DUVVtiMq4NJJ7Q2EkmGbV7drG1FeYdDoN9iaQbytLYqU...
    		4WdPKYEevhxcZhvCtzkF4PYuWqz6PDy3Sxk8LHVdXfuDEa2VmT7dgpjqgx1WiSJMfwtFF7NdoCoNPGvLp4BP2q2UJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn110980543164254555169170014811597918513108079163175693606964
    5
    		2024-11-24 05:38:03.000
    2RUx1tFXkYDoJnyF75PpNPpKnxveJHzxdSUb2yGhWgduSKLXwupJsBFbbiUwMfjqncrgG3ye...
    		G5uKrzQLWMTSHD2KcoYjjC4oT5jkRzsnM4T445AMtv6s2ZGJUFXmQRyyBHFga6g2SiZJnkjZHM9RKNFE7FgKowB6J1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn148886110425516083773002777619925380982017128357822106204
    6
    		2024-11-23 04:36:34.000
    3Q1isTm5H3NrH9hB2bDTHpvQCvFBsVPMLU8RaLEpVwn6yhveAZmQdwSQV8LMqnTuSe3zUfup...
    		2cGP4G5mCGjtAGG6V9EBQYCnBCARSUmxzCv5b7hALxig2xqKhe1yJzivbQYC67ryAf4ST6w9EicjPQbNsPu9Q5GPJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn11894192033626306843000003090035940379961044242303100876879
    7
    		2024-11-22 21:10:41.000
    5EMBaDYa64uyTf93yghAmQuiHS455d3YN9b9WHMK2zbSNDjmSUdMzBKQzkr6o49vMoZh7W5W...
    		HnGQNDC8e7P7bi7axP6fmKi2WRiS4qhmpMKyt2rEbc5C4xH4pydNgMCTYUvgGPfWo74xa4qdAaH7cc6CRAm6SwqzJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn2243881216225440681129004877624329463090140739021496831
    8
    		2024-11-23 03:01:10.000
    3qhqtKdCM17MiQ7y2tebtzsbD8pkonx4cErxmEtF99oGnBwzntxpfJd9r6yGACgs6CAdt5Cu...
    		5DCWYaBE7Eajd9SUp1U7WdC6WoW4RpwxSnsgsdUohgnB6dmjjFbjgYaNKXfx4jQ6yHRps3Er7jhEToYkCFTvKCuvDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263123883790000476612238837900084160556120971025313382756645265
    9
    		2024-11-23 13:13:00.000
    NM8GMWx4zuesmnKUmFDN8ToaF4RDkLjMXnbtCChBdoGcTFA91iw6SBBqkaxuDwabfQZ6h1r4...
    		268w1MgvA8USQQG8DkZEHYLDSbDnWCU12LfLYj3USARnF7nPdipyafrTYB8irFFPkjhLmefweaAfUB15nMtq41TrJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn110681548571325899315896005963914286569993071064522961709079336898
    10
    		2024-11-21 07:22:15.000
    29rAMW63GuGBZ4TKYcs9ifRvzJvkERcumVBqRMiSwGePs2GAJsHReHvc2k7iWWbKWGuwthTX...
    		B3NyyoWNzyzvpWv1G6a9gZJMjD1sTQBdZojBaH18mNjY7kxVvrDwqTJecSTrvaJb66t2sKNBRx4qCByCuGzFPoGPJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn1898568501074240838296870042096920586447035299403800736414377894756585
    11
    		2024-11-19 15:02:10.000
    3o7HdTtvdha8kjkBcgt7AM7a4bUW4cVSPS9EGnJRQKzpb11JKntVg49aWkVz73QuDDiSw3pr...
    		DBAMcasPmgSrStwH82DhVSKaWZrsjUR3nz6M3vc13aCs9bwptStQYrxWZcawtRmusgxfUuFDg2Er8HLXurM3XAdjDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB2632199996550354396019999655074777814531999836269
    12
    		2024-11-14 06:22:26.000
    5hM19dqmQdxdiyHHK1Mxx7DRWjdyQfRpVVqp7hwEfGX5oMuFKFJgrG6y8mxdFvBgTfnRr6T2...
    		4nenNCBMnonSv7btkqRDWqpGJ95X4EjXUGoeKJGyUN9o8bokafVgvdHsEcP76jPB5hxUrqAJaZWmqMUz4Dw6zuyaDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB26317701339524200021046717691041991750134366612353144855
    13
    		2024-11-15 00:34:41.000
    4xz5RS72rSs9TuXpY4UinzUK9zb5qPZkftBb5butedJemVED6WznB1Pr1Sc6wfw5h2PoxJfb...
    		4WdPKYEevhxcZhvCtzkF4PYuWqz6PDy3Sxk8LHVdXfuDEa2VmT7dgpjqgx1WiSJMfwtFF7NdoCoNPGvLp4BP2q2UJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn12983831920212704354060098802381754828501559347743035236
    14
    		2024-11-13 05:34:10.000
    2i6mEWe31ZBpkqyKHH8dzw7aGF1TxM89yxCKh87AmsFUtzYk3iiSsG5DEVMqx3CzZ6bZPWkT...
    		74s9xWbopPiVdEe1ntpS5BJgK2DCAhed5y6LWPcuHBYHGL11Bj8HrYEnDLPFYsYWmqrRBuh1wcBcqwq2mb3YkvRNDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263129690184503306893958608910686375013820747623094576
    15
    		2024-11-13 05:34:19.000
    4q6AdwQrDxdMgqHAqcnFuZbtuR5WbZqK6YGdz3WMyrWEBiCDDmEeyau8YrLJ8nAyLUvGeZ9v...
    		74s9xWbopPiVdEe1ntpS5BJgK2DCAhed5y6LWPcuHBYH1TD59vrNL3PeuoRzrYWo9BpXKVvh5wiNj4xESPFJiHJ3NZ9JMVBmGAqocybic2c7LQCJScmgsAZ6vQqTDzcqmJh1198579626187271409342040019857962067778772108531773964578
    16
    		2024-11-13 19:06:39.000
    3g7oMkwXApaeJRzHH5Xgp5Qsj2d81dDhjm4iToukjoBzBSCxnytd9iW8vPUcRQ1zY3Y3tfgi...
    		Fixb1fabvCiMUaxvbEjsSG6ZcY5EjyquJjF4aDFXqHxk8tyKpoFHq3be4dYuwPbKr7YwjtCfU6F3axApBrtxy5AgJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn199214734221844371375009921473425827310019778215875124710
    17
    		2024-11-12 23:32:48.000
    nkVVamvHMemtBJVm6Qi1SsaJApcnWChkmMmASPe7a1SfLDxaXMyUDHtEuGZMpzThS3cFPH7K...
    		DitY2XaKTtijzkJWUpdgSaqB6vHZPKEn9ECSp51xxcxRWFDbDPAGtWRYewp2eMJfQpkfgLj4LLMo9yty9dByuihJ1toso1uCk3RLmjorhTtrVwY9HJ7X8V9yYac6Y7kGCPn177029212121365999689002492854711057040531512325294415
    18
    		2024-11-12 23:32:50.000
    2wJVaSM5cRMnNEsdV4n4N5tJYESPL9SSqtQfYNG4fC1G5aWnzkqdDMJcAjEJWVwLrxcQTHmY...
    		6We9if5aPZSbEzYhSgxrgRFkomHwyRBFF8kuDDvURRsGByeBW8QN6LWypFDJ7yMLDxJPXbSMrQNckA626MJXj3vdDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB2632199984033239683719998403752176105531997774424
    19
    		2024-11-10 23:10:57.000
    5tEyLAhwh4nT9fHGmC6vttG5a9MxXLQdQ4dm7sVe9vubRDjxPoki2bTM1AUxa62mYgPoRtL3...
    		A6e17GeAKsoffPBex46fUyhFDudHKXNARoWvgzaXUDTf2nyGh1uYvcA9x6f3wQem6NXaNZwDYaT3SGC8k8noGwdh3NZ9JMVBmGAqocybic2c7LQCJScmgsAZ6vQqTDzcqmJh23706751030803718100000000487730391926691702860459308673475
    20
    		2024-11-12 06:00:32.000
    659w9NkdDEUNirguSthKPHwDfYDuGv3pfSsDdmu3L5ESx4dr7EJqeZrSMCifYWx9iBTraAtf...
    		DD6URYwPKs2sSRDa9d2zEmXXZHnMjPkgstR3xXX379mTHSHKafa65KTiUCac1zZLS1ducdgH5xBHDeaqEbVQATEoDezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB26311799509800028861384898460008825030750100732743288546723490
    ...
    4347
    1MB
    856s