flipside

    magic L by staking power copy
    jsbmuditmagic L by staking power copy
    Updated 2025-01-17
    -- forked from damidez / magic L by staking power @ https://flipsidecrypto.xyz/damidez/q/_9ydZW5hv9wI/magic-l-by-staking-power

    WITH staking_p AS (
    SELECT
    TRUNC(block_timestamp, 'day') AS day, -- Staking date
    block_timestamp,
    tx_id,
    signers[0] AS owner,
    utils.udf_hex_to_int(
    TO_CHAR(REVERSE(TO_BINARY(SUBSTR(utils.udf_base58_to_hex(instruction:data), 19, 16))))
    ) / POW(10, 6) AS amount_staked, -- Amount staked in human-readable format
    TO_TIMESTAMP(
    utils.udf_hex_to_int(
    TO_CHAR(REVERSE(TO_BINARY(SUBSTR(utils.udf_base58_to_hex(instruction:data), 35, 8))))
    )
    ) AS lockup_end, -- Lockup period end timestamp
    DATEDIFF(
    'day',
    block_timestamp, -- Staking start date
    TO_TIMESTAMP(
    utils.udf_hex_to_int(
    TO_CHAR(REVERSE(TO_BINARY(SUBSTR(utils.udf_base58_to_hex(instruction:data), 35, 8))))
    )
    )
    ) AS lockup_days, -- Duration in days
    -- Calculate the linear multiplier:
    1 + ((DATEDIFF(
    'day',
    block_timestamp,
    TO_TIMESTAMP(
    utils.udf_hex_to_int(
    TO_CHAR(REVERSE(TO_BINARY(SUBSTR(utils.udf_base58_to_hex(instruction:data), 35, 8))))
    )
    )
    ) - 1) / (1460 - 1)) * (20 - 1) AS multiplier,
    -- Calculate staking power:
    Last run: 3 months ago
    OWNER
    STAKING_POWER
    LOCKUP_DAYS
    TOTAL_$ME_STAKED
    1
    Bt3VgJo5ES99DqD9vyJYq9ErrCEi7qmY7qYh3Ewx4KBA
    		696868.411457534142935561.211174
    2
    4y2qgNTH6bmJYqKJnFDemicFQLUS3gb5xTET4ZJYDKQo
    		678087.3073161067628948.795798
    3
    9U4eV9LUpjA7u7hXuhpRRfjUG8tfWvKHZxJj7W6Hzb2k
    		500008146025000.4
    4
    8vCTfcCzdWr3M9P27qahZ9w6c7EnKLhZ2ymmcqtECdHj
    		299320146014966
    5
    2PkaiCVZRyp1m8ALbFvswmo6M6D2fqGbGLwMv4ig7ppn
    		250000146012500
    6
    FSDuSKPv8rnPqd4j1gEA2jTDjgqYCQ62UhXqVD9bkwJk
    		250000146012500
    7
    GvKAro7SwZLm7VS9GV6Zo6CTd2HMk5WCPFvxMCXtMBaN
    		250000146012500
    8
    9JvP9t6kGfxHodRmn3vc3Yo9sTwNyVbg9fM8ZxYaxjAZ
    		240920146012046
    9
    mrbsToVcdRS64JgauLd4BQbAxT3oTcYreaSYDYKBNGj
    		224920.9235640000
    10
    BgmffZceg2aVAbddyYicScNLAzP1ntedhtfbSTc1upeu
    		220820146011041
    11
    HUWiewvLD2Y6VFDCPd3Ph9HuLdPGuXP3ecPUK7NBgcue
    		203322.69404146010166.134702
    12
    6Av24oRPVMm3jHdYqzvz2EMi77aaqshHFHisQ2JRvw9A
    		201868.5485145910100
    13
    29yGs5JJ1CPj4TF6HHiwVo5Vw2Hvk2Lq3NNZpnVcmFqR
    		200015.94738146010000.797369
    14
    AvA8F11a7dkgpwxduTvKELStzLnboev7MjhSDra82yDk
    		200000146010000
    15
    2S5JDnseFzM3r5ZQ9LyGYZGuduQXQ4SkXXtvRbuD9qyC
    		197786.12144310000
    16
    UuikE7ZZo1P6zk3T9ZzVToBHSzXWSxDRYzf39ibgWqG
    		197265.33143910000
    17
    BFhSfSztg6x4LBksx31WVg6UQHw2KNmzcy4hVDJphLAk
    		160191.1079414608009.555397
    18
    8WwoSX2Giqpk8gnQxoqSwsBzVvS9Xnc4U5Wgv3BxAyEj
    		160190.9760814608009.548804
    19
    4Kb2AusKPJGFHEhqnnMRG63S2HJiDKbGHDmRPdizTV2R
    		160171.1079414608008.555397
    20
    DxuMWkk2wkRMhYqDndyQX7tpCVEoGp2V9pHLkDPo9qp3
    		160151.1079414608007.555397
    ...
    200
    14KB
    48s