flipside

    Debt Over Time
    0xHaM-dDebt Over Time
    Updated 2025-05-13
    with priceTb as (
    select
    hour,
    price
    from ethereum.price.ez_prices_hourly
    where SYMBOL = 'WBTC'
    and hour::date >= '2025-03-01'
    )
    ,
    openTrove as (
    select
    BLOCK_TIMESTAMP,
    tx_hash,
    FROM_ADDRESS as borrower_add,
    value as collateral,
    value*price as collateral_usd,
    regexp_substr_all(SUBSTR(INPUT, 11, len(INPUT)), '.{64}') AS segmented_data,
    ethereum.public.udf_hex_to_int(segmented_data[1])::int/1e18 as debt_value,
    from mezo.testnet.fact_traces
    join priceTb on trunc(BLOCK_TIMESTAMP, 'hour') = hour
    where TO_ADDRESS = '0x20faea18b6a1d0fcdbccfffe3d164314744baf30'
    and left(input,10) ilike '0x8f09162b'
    and tx_succeeded = 'True'
    )
    ,
    adjustTrove as (
    select
    BLOCK_TIMESTAMP,
    tx_hash,
    FROM_ADDRESS as borrower_add,
    value as collateral,
    value*price as collateral_usd,
    regexp_substr_all(SUBSTR(INPUT, 11, len(INPUT)), '.{64}') AS segmented_data,
    ethereum.public.udf_hex_to_int(segmented_data[1])::int/1e18 as debt_value,
    ethereum.public.udf_hex_to_int(segmented_data[2])::int/1e18 as adjust_amt,
    ethereum.public.udf_hex_to_int(segmented_data[3])::int/1e18 as increase_debt,
    Last run: 27 days ago
    DATE
    N_TXN
    N_BORROWERS
    TOTAL_DEBT
    TOTAL_COLLATERAL
    TOTAL_COLLATERAL_USD
    AVG_DEBT
    AVG_COLLAT
    MEDIAN_DEBT
    MEDIAN_COLLAT
    AVG_DEBT_PER_BORROWER
    AVG_COLLAT_PER_BORROWER
    TOTAL_N_TXN
    TOTAL_DEBT_AMT
    TOTAL_COLLAT_AMT
    TOTAL_COLLAT_AMT_USD
    1
    		2025-05-13 00:00:00.0006115304154109.110.164754263916987.6405504116798.8692471358430.000269646913131000.00018235257837.9417169811320.000310857101735160331131713.8794920.3882971721799837.24283087
    2
    		2025-05-12 00:00:00.0005204536427519.570.393139429740958.79879417212360.6145576923080.00075603736473328.810.00019514188.7849227373060.000867857460634549326977604.7694920.2235429081782849.60228046
    3
    		2025-05-11 00:00:00.0002522002015028.180.082388260158567.3588902847996.1435714285710.000326937540330000.000101053536910075.14090.000411941300834029320550085.1994919.8304034781741890.80348629
    4
    		2025-05-10 00:00:00.0001521351354731.880.05073280295245.8667951318912.7097368421050.000333768440227000.000132602510035.0509629629630.0003757985433777318535057.0194919.7480152181733323.44459601
    5
    		2025-05-09 00:00:00.0006856645088806.950.5429248455901.1047896667428.9152554744530.000792591007320530.00018499478847663.8658885542170.000817657891533625317180325.1394919.6972824151728077.57780088
    6
    		2025-05-08 00:00:00.0008378265010205.470.655388368365302.6607284735985.9085663082440.000783020750720030.0001216052756065.6240556900730.000793448387832940312091518.1894919.1543575751672176.47301121
    7
    		2025-05-07 00:00:00.000121311136494128.260.709339942668553.3270679675353.7743281121190.00058478148620000.000121660555834.796280323450.000637322500132103307081312.7194918.4989692071606873.81228274
    8
    		2025-05-06 00:00:00.0001846120711565279.940.59055927355579.8472546986265.048721560130.000319912932320000.000121559581.8392212096110.000489278602330890300587184.4594917.7896292641538320.48521477
    9
    		2025-05-05 00:00:00.000195214518745545.289490.609686967757484.1228922394480.2998409272540.000312339635120000.000121556027.2538177050310.000420183988729044289021904.5194917.1990699911482740.63796007
    10
    		2025-05-04 00:00:00.0006505136702829.730.221676381621197.61448999810312.0457384615380.000341040587120000.000075877513065.9448927875240.000432117702927092280276359.2316.5893830231425256.51506783
    11
    		2025-05-03 00:00:00.00010259248917622.890.445169504942803.006789978700.1198926829270.00043431171212966.980.0001759651.1070238095240.000481785178526442273573529.516.3677066421404058.90057784
    12
    		2025-05-02 00:00:00.0001684155311504520.050.530932348751298.3002072716831.6627375296910.000315280492121000.0001557407.9330650354150.00034187530525417264655906.6115.9225371371361255.89378787
    13
    		2025-05-01 00:00:00.000209519586688586.610.417862955340048.0666895613192.642773269690.000199457257921000.0001453416.029933605720.000213413153923733253151386.5615.3916047881309957.5935806
    14
    		2025-04-30 00:00:00.000133912964447869.150.338169771631927.2716277863321.784279312920.000252553974328000.0001753431.9978009259260.000260933465721638246462799.9514.9737418331269909.52689104
    15
    		2025-04-29 00:00:00.000138013655537218.570.354665958933633.9406019834012.4772246376810.000257004318135000.000224056.5703809523810.000259828541320299242014930.814.6355720611237982.25526325
    16
    		2025-04-28 00:00:00.0008898724466999.490.272727964325685.8424287235024.746332958380.000306780612326050.00018156637315122.7058371559630.00031276142718919236477712.2314.2809061021204348.31466127
    17
    		2025-04-27 00:00:00.0007287162361787.610.145846828813726.7021873343244.213750.000200339050530000.000193298.5860474860340.000203696688218030232010712.7414.0081781381178662.47223254
    18
    		2025-04-26 00:00:00.000118011473810454.930.204222822419314.5442209673229.1990932203390.00017307018842064.50.0001875942253322.1054315605930.0001780495417302229648925.1313.8623313091164935.77004521
    19
    		2025-04-25 00:00:00.000108010372932265.330.201897077618974.6422323562715.0604907407410.000186941738520600.0001816242827.6425554484090.00019469342116122225838470.213.6581084871145621.22582424
    20
    		2025-04-24 00:00:00.0006105601346062.310.082786317927690.3168053452206.6595245901640.000135715275320000.0001552403.6826964285710.000147832710615042222906204.8713.4562114091126646.58359189
    46
    9KB
    40s