flipside

    organisational-aquamarine
    Sbhn_NPorganisational-aquamarine
    Updated 2025-04-02
    with bs_d as (select topics_decoded:topics_decoded[2]:value as user,
    sum(split(replace(replace(data_decoded:value::string, '[', ''), ']', ''), ' ')[0]/pow(10,7)) as deposited
    from stellar.core.fact_contract_events
    where topics_decoded:topics_decoded[0]:value = 'deposit'
    and topics_decoded:topics_decoded[1]:value = 'CDVQVKOY2YSXS2IC7KN6MNASSHPAO7UN2UR2ON4OI2SKMFJNVAMDX6DP'
    and contract_id = 'CAO3AGAMZVRMHITL36EJ2VZQWKYRPWMQAPDQD5YEOF3GIF7T44U4JAL3'
    group by 1),

    bs_w as (
    select topics_decoded:topics_decoded[2]:value as user,
    sum(split(replace(replace(data_decoded:value::string, '[', ''), ']', ''), ' ')[0]/pow(10,7)) as withdrawn
    from stellar.core.fact_contract_events
    where topics_decoded:topics_decoded[0]:value = 'withdraw'
    and topics_decoded:topics_decoded[1]:value = 'CDVQVKOY2YSXS2IC7KN6MNASSHPAO7UN2UR2ON4OI2SKMFJNVAMDX6DP'
    and contract_id = 'CAO3AGAMZVRMHITL36EJ2VZQWKYRPWMQAPDQD5YEOF3GIF7T44U4JAL3'
    group by 1
    )
    SELECT
    COALESCE(a.user, b.user) AS "User",
    COALESCE(deposited, 0) AS "Deposited BLND-USDC LP",
    COALESCE(withdrawn, 0) AS "Withdrawn BLND-USDC LP",
    COALESCE(deposited, 0) - COALESCE(withdrawn, 0) AS "Current Deposited BLND-USDC LP",
    FROM bs_d a
    FULL JOIN bs_w b
    ON a.user = b.user
    having "User" is not null
    ORDER BY 4 DESC


    Last run: 25 days ago
    User
    Deposited BLND-USDC LP
    Withdrawn BLND-USDC LP
    Current Deposited BLND-USDC LP
    1
    GCQQRVD75YXVFWTURGHNQJY2G3QVNUYYAUGNRAHVRGTVXL47JP5MH6SU
    		255890.58348590255890.5834859
    2
    GDTZSZTGBXPBV4GC7AUYNWUWCAT56CNYSKR7IOIZYNAOPIWCWWHMTIPS
    		243238.74008820243238.7400882
    3
    GA3FXHBMRVNTNPSOA46WTTLNAKJTKD7HOQCZ62WYOK4KJTIC46ZLITVC
    		213064.4293490213064.429349
    4
    GDIQFXBZAS4TA5Z7JSCOLGSDFMK4UF4SPYER5IVPNRPUYFQ6U4GKVZ7P
    		151723.89659340151723.8965934
    5
    GDPMAJLQWYB6NE3NPE7CGIEA744PV427NPGWL5XZTNR6T7P6K2YGI5IX
    		146129.39294350146129.3929435
    6
    GBUNDGF4GERH44WAOSJFBDZG5AFFHSZBAZVTFNN4SOE63BGDDDYQAV2G
    		91800.1883237091800.1883237
    7
    GDAQL5DLEB6MU77BZQPJVIHCHM4UEGIAK3AYIE5UXBZI26AGWGEGCFYI
    		64560.4372247064560.4372247
    8
    GCYVR6JS6DLRAFF2HEC6LN7J6H6JS2VMERCTGV64RESTZAJMOHB5RHU7
    		63607.8499057063607.8499057
    9
    GDGEWZMIJ2K6AEYYV2L4FYN27YJP5OVZSWCJIM662D5OS7EL6T6WBGBP
    		54488.9928508054488.9928508
    10
    GAGNU4JUDML3BYEAH5MOKWDNYSIMWNKP2WZX5XM4WZZ4H7Y4RPGYBLOX
    		50528.1197477050528.1197477
    11
    GC2LIJHKPVSTUILOTPGAEBEGIS4NCMRQEOP3Z45CFYT7NTJCOJDTTP5X
    		47946.9797688047946.9797688
    12
    GCEGTBEIR3CTSRJW6MF3XBMTH67TXE5V5D6T5IURRSTXB5ZOOOA6XD22
    		73006.034985938811.307301134194.7276848
    13
    GC2CNV3ZP2ARORYJMPFYO56LJVVMN2QLLVCICQD24B3QMDZ5FQZVJOM7
    		30125.4988243030125.4988243
    14
    GCRU3HYKAAN4PF4DHMSRBX7AHNLZSR3WSOANTZAYZRGJXUNM6ZEHBCXM
    		11935.9566771011935.9566771
    15
    GB5F2BYPFRSCMQPPR226EDCMZDVAVYNN5JIEDLPIN2AV3QMYKUFNXHNB
    		11707.1222706011707.1222706
    16
    GANSQULZ33F7DBXXF4JQSSO6MMPTAUATJGID2MG3YEV2ILRCWIZJ7EII
    		11803.1926339371.380714911431.811919
    17
    GABR5FJYD4AITJWFV2YOTEQKJPKSGMC37ESKRU6FEGNNRYV6YAPXZPWK
    		10822.4674472010822.4674472
    18
    GAJDS3W5L3EOTBZFXCQR7ZXDHA274F3DGGXW27GEQ7Q5JUYKD45PIZZA
    		8187.224680708187.2246807
    19
    GAET44DXN422BYZTNKGVLDF6HAQMBNC73PL2F6PQCVBHWTIJHRZTTFG6
    		23261.348118415446.69324087814.6548776
    20
    GBXQRU73P63NSSEKRQRYQTSQ6SDUQY7LM6JEPD56WDQLSBT2FCDIQ6H7
    		7396.420787607396.4207876
    ...
    140
    12KB
    4s