Posible to obtain history ledger(balance) with sql query?
To start, and this is just to get you somewhere,

SELECT *,
  jsonb_agg(jsonb_build_object('p',price,'a',amount))
    FILTER (WHERE type='buy')
    OVER (PARTITION BY item ORDER BY id)
    AS cumpurchased
FROM operations;
 id | type | item  | price | amount |             cumpurchased              
----+------+-------+-------+--------+---------------------------------------
  1 | buy  | item1 |     2 |     10 | [{"a": 10, "p": 2}]
  2 | buy  | item1 |     3 |      4 | [{"a": 10, "p": 2}, {"a": 4, "p": 3}]
  3 | sell | item1 |     4 |     12 | [{"a": 10, "p": 2}, {"a": 4, "p": 3}]


Here, we serialize the purchases into cumpurchased. This seems simple, now we can blow this up by expanding cumpurchased to the individual transactions.

SELECT id, type, item, price, amount, cumsale, e, eid
FROM (
        SELECT *,
                sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
        jsonb_agg(jsonb_build_object('p',price,'a',amount))
                        FILTER (WHERE type='buy')
        OVER (PARTITION BY item ORDER BY id)
        AS cumpurchased
        FROM operations
) AS with_agg
CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid)
ORDER BY id, eid;
 id | type | item  | price | amount | cumsale |         e         | eid 
----+------+-------+-------+--------+---------+-------------------+-----
  1 | buy  | item1 |     2 |     10 |         | {"a": 10, "p": 2} |   1
  2 | buy  | item1 |     3 |      4 |         | {"a": 10, "p": 2} |   1
  2 | buy  | item1 |     3 |      4 |         | {"a": 4, "p": 3}  |   2
  3 | sell | item1 |     4 |     12 |      12 | {"a": 10, "p": 2} |   1
  3 | sell | item1 |     4 |     12 |      12 | {"a": 4, "p": 3}  |   2
(5 rows)


You should be ale to see where I'm going with this...

SELECT id,type,item,e,eid,sum(
        (e-&gt;&gt;'a')::int
) OVER (PARTITION BY id ORDER BY eid)
        - coalesce(cumsale,0)
  AS qty_remaining
FROM (
        SELECT *,
                sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
        jsonb_agg(jsonb_build_object('p',price,'a',amount))
                        FILTER (WHERE type='buy')
        OVER (PARTITION BY item ORDER BY id)
        AS cumpurchased
        FROM operations
) AS with_agg
CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid);
 id | type | item  |         e         | eid | qty_remaining 
----+------+-------+-------------------+-----+---------------
  1 | buy  | item1 | {"a": 10, "p": 2} |   1 |            10
  2 | buy  | item1 | {"a": 10, "p": 2} |   1 |            10
  2 | buy  | item1 | {"a": 4, "p": 3}  |   2 |            14
  3 | sell | item1 | {"a": 10, "p": 2} |   1 |            -2
  3 | sell | item1 | {"a": 4, "p": 3}  |   2 |             2
(5 rows)


From this point, you need to filter out the -2, we wrap the query again and put that conditional in there. Then we just select from that.

SELECT id, e-&gt;&gt;'p' AS price, least(qty_remaining,(e-&gt;&gt;'a')::int) AS amount
FROM (
      SELECT id,type,item,e,eid,sum(
        (e-&gt;&gt;'a')::int
      ) OVER (PARTITION BY id ORDER BY eid)
        - coalesce(cumsale,0)
        AS qty_remaining
      FROM (
        SELECT *,
          sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
          jsonb_agg(jsonb_build_object('p',price,'a',amount))
            FILTER (WHERE type='buy')
            OVER (PARTITION BY item ORDER BY id)
            AS cumpurchased
        FROM operations
      ) AS with_agg
      CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid)
      ) AS t
WHERE qty_remaining &gt; 0;
 id | price | amount 
----+-------+--------
  1 | 2     |     10
  2 | 2     |     10
  2 | 3     |      4
  3 | 3     |      2
(4 rows)