flipside

    magic L by staking power copy
    jsbmuditmagic L by staking power copy
    Updated 2025-01-31
    -- 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: 2 months ago
    OWNER
    STAKING_POWER
    LOCKUP_DAYS
    TOTAL_$ME_STAKED
    1
    Bt3VgJo5ES99DqD9vyJYq9ErrCEi7qmY7qYh3Ewx4KBA
    		696868.411457534142935561.211174
    2
    4y2qgNTH6bmJYqKJnFDemicFQLUS3gb5xTET4ZJYDKQo
    		678087.3073161067628948.795798
    3
    A9bHmoYkjXHwwFWfLDuvNwbGmjLQuxFGYmwcrzXiVD1a
    		521514.483593151140727007.753656
    4
    9U4eV9LUpjA7u7hXuhpRRfjUG8tfWvKHZxJj7W6Hzb2k
    		500008146025000.4
    5
    2S5JDnseFzM3r5ZQ9LyGYZGuduQXQ4SkXXtvRbuD9qyC
    		388539.96141620000
    6
    2S5JDnseFzM3r5ZQ9LyGYZGuduQXQ4SkXXtvRbuD9qyC
    		313598.56491141916110
    7
    8vCTfcCzdWr3M9P27qahZ9w6c7EnKLhZ2ymmcqtECdHj
    		299320146014966
    8
    2S5JDnseFzM3r5ZQ9LyGYZGuduQXQ4SkXXtvRbuD9qyC
    		266338.950887324141213746.583967
    9
    DN5gwVEW5bR4uR4MmPmFDiz1Bsv2cNtriKKCdYoCeu1m
    		262331.252140996140913567.0883
    10
    FSDuSKPv8rnPqd4j1gEA2jTDjgqYCQ62UhXqVD9bkwJk
    		250000146012500
    11
    2PkaiCVZRyp1m8ALbFvswmo6M6D2fqGbGLwMv4ig7ppn
    		250000146012500
    12
    GvKAro7SwZLm7VS9GV6Zo6CTd2HMk5WCPFvxMCXtMBaN
    		250000146012500
    13
    GAEPC1FWJZECn1UajFXQg1Hz6K3YmPdGNwhYkkXdxB1f
    		244173.960651751142212518.43863
    14
    9JvP9t6kGfxHodRmn3vc3Yo9sTwNyVbg9fM8ZxYaxjAZ
    		240920146012046
    15
    mrbsToVcdRS64JgauLd4BQbAxT3oTcYreaSYDYKBNGj
    		224920.9235640000
    16
    8sbHUG9C7teSzJWKLQmaBgBmAiP6aCf25MaFJFaq6sNd
    		222656.0175144411250
    17
    BgmffZceg2aVAbddyYicScNLAzP1ntedhtfbSTc1upeu
    		220820146011041
    18
    DDX66UQ3dPrEBtoCPfaW3h6qf6zHNgkJ3KFuEK8fNJYX
    		220000146011000
    19
    9cXiFyCY3k8jVmKRaEMeg9A5qbA88Xjp38j52cWPdrij
    		208235.59332146010411.779666
    20
    HUWiewvLD2Y6VFDCPd3Ph9HuLdPGuXP3ecPUK7NBgcue
    		203322.69404146010166.134702
    ...
    1000
    72KB
    55s