MoDeFieclipse rps - game history
    Updated 54 minutes ago
    with hand_paper_raw as (
    select BLOCK_TIMESTAMP, SIGNERS[0]::string as user, 'play' as event, split(VALUE:parsed,'_')[0]::string as action, split(VALUE:parsed,'_')[1]::float as amount, value as log, tx_id
    from eclipse.core.fact_transactions,
    table(FLATTEN(input => INSTRUCTIONS)) as flattened
    where ACCOUNT_KEYS[1]:pubkey='BGh13zVibtk3kge1K3u8kTbfk4Zyqmf7coqm8YoU6Wio'
    and SIGNERS[1] is null
    and VALUE:parsed IS NOT NULL
    and VALUE:parsed:info IS NULL
    and BLOCK_TIMESTAMP::date>='2024-12-20'
    union all
    select BLOCK_TIMESTAMP, SIGNERS[0]::string as user, 'result' as event, split(VALUE:parsed,'_')[0]::string as action, split(VALUE:parsed,'_')[1]::float as amount, value as log, tx_id
    from eclipse.core.fact_transactions,
    table(FLATTEN(input => INSTRUCTIONS)) as flattened
    where SIGNERS[1]='BGh13zVibtk3kge1K3u8kTbfk4Zyqmf7coqm8YoU6Wio'
    and VALUE:parsed IS NOT NULL
    and VALUE:parsed:info IS NULL
    and BLOCK_TIMESTAMP::date>='2024-12-20'),

    hand_paper_ranked as (
    select *,
    row_number() over (partition by user order by BLOCK_TIMESTAMP) as row_num
    from hand_paper_raw)
    select
    case when b.action='win' then '🟩 Win'
    when b.action='draw' then '⬜ Draw'
    else '🟥 Lose' end as result,
    a.*,
    case when b.action='win' then a.amount*2
    when b.action='draw' then a.amount
    else 0 end as received_amount
    from hand_paper_ranked a
    left join hand_paper_ranked b
    on a.user=b.user and a.row_num=b.row_num-1 and b.event='result'
    where a.event='play'
    order by 2 desc
    Last run: about 1 hour agoAuto-refreshes every 1 hour
    RESULT
    BLOCK_TIMESTAMP
    USER
    EVENT
    ACTION
    AMOUNT
    LOG
    TX_ID
    ROW_NUM
    RECEIVED_AMOUNT
    1
    🟥 Lose2025-03-15 22:33:22.000C5Uo3uoUfKap4pSCvzR8m6e8xAvfaNBX8ApHeHjYWqA8playrock0.01
    {"parsed":"rock_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb9...
    3SuJtojzKnNM3PZ6t6g5ps2TLeMGyqdmvTZFvxZgNgmVuSkW9BqCLin9vhSDHMXXzgQXfe7B...
    30
    2
    🟩 Win2025-03-15 22:32:34.000C5Uo3uoUfKap4pSCvzR8m6e8xAvfaNBX8ApHeHjYWqA8playpaper0.01
    {"parsed":"paper_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb...
    61oA6Bc1KZmEcWHUNc8p9SedJLBdcr5SaPBv8PnVT6Ra4yHukrJacWKEHQ1gtYW6KgiUqHXd...
    10.02
    3
    ⬜ Draw2025-03-15 21:04:44.000GEYG3bTYGTJ4b7DERGxXQq126vQjQ9AiLjK9jYEoy9xgplayrock0.01
    {"parsed":"rock_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb9...
    5kLPwtKQYTHq23APivhAMH2a1iF1PnALLCfaWPtGykGwUeaqpARghY2KKJ3R9pKLoDeSsRWo...
    50.01
    4
    ⬜ Draw2025-03-15 21:02:40.000GEYG3bTYGTJ4b7DERGxXQq126vQjQ9AiLjK9jYEoy9xgplaypaper0.01
    {"parsed":"paper_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb...
    5dFk9kC8Uh2aKDVqvKp6q1tbnw4hCCsV4egFvZDmJpxnyTLupf6KXiJUhyeAoJPWihwz95cH...
    30.01
    5
    🟥 Lose2025-03-15 19:57:58.000CeT2yQPssENyBcwevnnPRktVoCk2ixjkZAxESpGzTMLJplayrock0.01
    {"parsed":"rock_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb9...
    3WLki5YkVUCN27r7ij64n1kAErkRW5sPL9HXuuFk2QdVshesSnpQdfVLGVM3VvGDE5ZCr9V4...
    80
    6
    🟥 Lose2025-03-15 19:57:47.000CeT2yQPssENyBcwevnnPRktVoCk2ixjkZAxESpGzTMLJplayscissors0.01
    {"parsed":"scissors_0.01","program":"spl-memo","programId":"MemoSq4gqABA...
    3n4yGZRyN3pmMVv5zXThFHSgG7JcMsM6C45vFZJeZ3GrwiEWQHYD9w3FhNUcV4LZUA6EWW4q...
    70
    7
    🟩 Win2025-03-15 19:10:47.000F7qA8nj7k3vrZ6Kuk7YfN73Qzu1qc33MFg7d7KvN9Bi2playscissors0.01
    {"parsed":"scissors_0.01","program":"spl-memo","programId":"MemoSq4gqABA...
    KBzHzQr8H6asJ6P4N1okXUzUuxLLTpxzYMde6DSHmQQqnGTLJMNQ6wvMzuWiH2LYwBxHHDkB...
    30.02
    8
    🟩 Win2025-03-15 19:10:02.000F7qA8nj7k3vrZ6Kuk7YfN73Qzu1qc33MFg7d7KvN9Bi2playscissors0.01
    {"parsed":"scissors_0.01","program":"spl-memo","programId":"MemoSq4gqABA...
    5E6vJfinkkERD7qczah1hi2aayXgASimRjkCs35ZVyN9SepFBC4zCdWobVtzm1176548H2Rr...
    10.02
    9
    🟩 Win2025-03-15 19:06:56.000DikxgjsmaRxhDMamqAPhTHTy4nKUmuK5PWYbZjdx8Hyjplaypaper0.01
    {"parsed":"paper_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb...
    4FeaeVXVbCdPNYj1KdqojgCsHJfCiwYHYCAeZtxfYb22Q9q223py5ngRtP6E7vwVHqRY29wa...
    20.02
    10
    🟥 Lose2025-03-15 19:06:09.000DikxgjsmaRxhDMamqAPhTHTy4nKUmuK5PWYbZjdx8Hyjplaypaper0.01
    {"parsed":"paper_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb...
    LEbN8LnrppnePDAF7WvoLShTB1JfBq2Gxhf4MgipoMQbuTgKt1KxYpERTHM28e7gnFwoBeJo...
    10
    11
    🟩 Win2025-03-15 18:30:22.000FaxwYnJjzQnn33S4y9fQFNNEi1jVuzcRHGnr1YrS6kuVplayrock0.01
    {"parsed":"rock_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb9...
    4SasmKRN7FptvN6mvakNaTZ2UfLcwDsAk94ZEB1qJD5SuX2BxFgTEu5SFD3VSzFXohT2vkQZ...
    20.02
    12
    🟥 Lose2025-03-15 18:04:37.0002db1E2pUnradhpJrcAPPpHHeJiG6H8iE4zaRMGkkiDoZplayscissors0.01
    {"parsed":"scissors_0.01","program":"spl-memo","programId":"MemoSq4gqABA...
    8HZ2gxY7W3PmUdMMyAMEsHsSZgM2ysRaD7DTwUC78DUjTcRmdc8K86LEEtvrmWfF3Fqz9hAx...
    30
    13
    ⬜ Draw2025-03-15 18:04:04.0002db1E2pUnradhpJrcAPPpHHeJiG6H8iE4zaRMGkkiDoZplaypaper0.01
    {"parsed":"paper_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb...
    2bFDz2LUHGCDrDamfouNvXJqW53GDRwHbzqJmbyd4TvCurVpRtzv6EFS1BGjoR1ZPoMBSvGg...
    10.01
    14
    🟥 Lose2025-03-15 17:59:27.0005iU1iNRv4HkeYB7B2PNC44HaXTapukCQSEjaUcRaDAvrplayrock0.01
    {"parsed":"rock_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb9...
    3tC9b4GNK6U9tGG18PmRHLZonYzHqRLz4RzLBgmfUobz5CEi1NvUTVdPxvcNAnuJCzMwuzGV...
    20
    15
    🟥 Lose2025-03-15 17:59:17.0005iU1iNRv4HkeYB7B2PNC44HaXTapukCQSEjaUcRaDAvrplayrock0.01
    {"parsed":"rock_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb9...
    2xps5MAfxeLCJFduqHiXLkch9xey1nK9rirK8KCU26ZFmU72FXjg26qJXCGE1JUn2Cnj4NCL...
    10
    16
    🟩 Win2025-03-15 17:51:41.000Hzg9JkrE6p3pKH1CdY3JcZS21iYaqCrfjivMuQRofQgfplayscissors0.01
    {"parsed":"scissors_0.01","program":"spl-memo","programId":"MemoSq4gqABA...
    2VMhWrK74S4TM7ZBL7SKcve1KbD5wwJjaYmFhwJ8JvvGfQenxVKmSbayVmqo3UCta1hosNac...
    30.02
    17
    ⬜ Draw2025-03-15 17:51:07.000Hzg9JkrE6p3pKH1CdY3JcZS21iYaqCrfjivMuQRofQgfplayrock0.01
    {"parsed":"rock_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb9...
    3RB22GCkc4Jx1mpnSPbzr78cRKU3TBo7jAASoDKq9ocwehifKzP2sHFJwrzPmu77Q4vYXBQ4...
    10.01
    18
    🟥 Lose2025-03-15 17:26:35.0004DDJtyofwXEakeKFJhSBbt93nbKg4c3LdskxyrnbnVFdplaypaper0.01
    {"parsed":"paper_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb...
    4pHc4MsP1RMiJU77NWyVMDEyyNsxmrCv1rJcX4AVZj76NX4iqUo9ArdPGgGRZyUVYapVcKj8...
    40
    19
    ⬜ Draw2025-03-15 17:25:59.0004DDJtyofwXEakeKFJhSBbt93nbKg4c3LdskxyrnbnVFdplaypaper0.01
    {"parsed":"paper_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb...
    5UJHMcNQ5csCi3Enpa8rrFDLRcFiezJRRfqwrWwqTAzc8Rrfap9D9s2YDFajXticFQqHugvz...
    20.01
    20
    🟥 Lose2025-03-15 17:25:36.0004DDJtyofwXEakeKFJhSBbt93nbKg4c3LdskxyrnbnVFdplaypaper0.01
    {"parsed":"paper_0.01","program":"spl-memo","programId":"MemoSq4gqABAXKb...
    32z1rqNZiM2rocYoXtJoQLGHK6jEvQ9oNeEqhXjmFLjD7fWdH45J4BTHjsFFLE8ETpde6FxE...
    10
    ...
    9779
    3MB
    699s