flipside

    magic L by amount power copy
    jsbmuditmagic L by amount power copy
    Updated 2025-02-01
    WITH staking_p AS (
    SELECT
    signers[0] AS owner,
    SUM(utils.udf_hex_to_int(
    TO_CHAR(REVERSE(TO_BINARY(SUBSTR(utils.udf_base58_to_hex(instruction:data), 19, 16))))
    ) / POW(10, 6)) AS total_amount_staked, -- Total amount staked per owner
    MAX(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))))
    )
    )
    )) AS max_lockup_days -- Maximum lockup period among stakes
    FROM
    solana.core.fact_events
    WHERE
    succeeded
    AND fact_events.program_id = 'veTbq5fF2HWYpgmkwjGKTYLVpY6miWYYmakML7R7LRf'
    AND SUBSTR(utils.udf_base58_to_hex(fact_events.instruction:data), 3, 16) = 'ceb0ca12c8d1b36c' -- staking
    AND block_timestamp >= '2024-12-10 14:05:13.000'
    GROUP BY
    owner
    ),
    staking_calculated AS (
    SELECT
    owner,
    total_amount_staked,
    max_lockup_days,
    -- Compute staking power using precomputed max_lockup_days
    total_amount_staked * (
    1 + ((max_lockup_days - 1) / (1460 - 1)) * (20 - 1)
    ) AS total_staking_power
    FROM staking_p
    )
    Last run: 2 months ago
    OWNER
    TOTAL_STAKING_POWER
    TOTAL_AMOUNT_STAKED
    MAX_LOCKUP_DAYS
    1
    2S5JDnseFzM3r5ZQ9LyGYZGuduQXQ4SkXXtvRbuD9qyC
    		21064001053201460
    2
    GcXFgTPypQPqTBGTgZdZHHweZAaDJ3Suwugj52kyAcov
    		1789949.5302864193771.4969511390
    3
    7RVsvF7K2R2s1jQ4T4TMFWhjtfCKxGbySenKVTKfSqKP
    		1003469.637250173.481861460
    4
    Hn2iU87MPL67Thp49dyC6G3ommDXW3kQHHrJSBH7Rv2A
    		934251.906577867790182.64585515
    5
    HUGoBCUqK5eFAybExfcjQTnC7At7EhysXjkzv3TQUXi2
    		768745.76400001400
    6
    Bt3VgJo5ES99DqD9vyJYq9ErrCEi7qmY7qYh3Ewx4KBA
    		763453.1153438172.6557671460
    7
    4y2qgNTH6bmJYqKJnFDemicFQLUS3gb5xTET4ZJYDKQo
    		678087.307316106628948.7957987
    8
    6ryG4EHMB8SpQi5SkeFqCWNpSHv3NhPaZAtd4LgC7Kdb
    		677411.6769833870.5838491460
    9
    GTJw31XHrxrYhnzhbW9XUPcXEs3fi9T77YvPd9nqWFPo
    		629299.7682431464.9884121460
    10
    HLmv58ujf9Sy4b5cKFipEufT5t8FCgnDe3BWSupw9eXs
    		614592.631830729.631591460
    11
    3kQQqL5YFG29AxXriexHjuKepDovL45hqWFQRmA4ATV5
    		602092.8235430104.6411771460
    12
    8KgimJXmqNRXpYaocP7VFGufAmh2gFcA7he3kmFJqY6C
    		600000300001460
    13
    Ec4zx6uyxWyNvRvukMWMSfPgWPw2q2HbmToLzTUXEXQK
    		571940285971460
    14
    DggDVRataeiZPDuwaoRQ9MkLBYi2jixst386yJDGPxDa
    		54689227344.61460
    15
    9ceKtYkz1AMd7mdJeCsdcmooqrGmvESQ2D3yCacqStrC
    		508150.0385425407.5019271460
    16
    29yGs5JJ1CPj4TF6HHiwVo5Vw2Hvk2Lq3NNZpnVcmFqR
    		500030.4312425001.5215621460
    17
    9U4eV9LUpjA7u7hXuhpRRfjUG8tfWvKHZxJj7W6Hzb2k
    		50000825000.41460
    18
    Gsojys5tZvsCLTWX3JfAySubeg3KpTFAiqR7Gn679ciH
    		499958.85527048725277.7523151443
    19
    6j2nY5VJyswY4g3svqjEj5oZihB3j3bi3LosuEtaMkuJ
    		497014.83781520925704.3113851409
    20
    2LQWVC9nP14pc9V331M3zffmZQeDoupZdv7UJYNHptvw
    		490868.2023424543.4101171460
    ...
    1000
    73KB
    28s