Connect two SQL queries

EDITED: I cut the previous SQL query to make things easier - no need to connect both queries as I said previously.

The stock_tracking_negociacao table (I filtered only one ticker 'LEVE3' to make things easier):

#;data;tipo;mercado;ticker;quantidade;preco;valor
606;2019/12/02;Compra;Mercado à Vista;LEVE3;400;24.95;9980
437;2020/03/19;Compra;Mercado à Vista;LEVE3;100;18.05;1805
775;2021/06/10;Venda;Mercado à Vista;LEVE3;200;35.74;7148
716;2021/08/11;Venda;Mercado à Vista;LEVE3;300;33.6;10080
1214;2022/07/21;Compra;Mercado à Vista;LEVE3;600;23.7;14220
1564;2023/03/13;Compra;Mercado à Vista;LEVE3;200;32;6400

where:

• data = date
• tipo: 'Compra' = 'Buy' and 'Venda' = 'Sell'
• quantidade = amount
• preco = price
• valor = amount * price

I need to build a query with the same stock_tracking_negociacao table. The table must be orderer by date (data) from the oldest to newest records.

My goal is to build two new columns for each line (regardless it appears more than one time): balance and avg_price.

Explaining the new columns:

1. balance: if tipo = Compra, we need to add quantidade from the previous records. Otherwise, if tipo = Venda, we need to subtract quantidade from the previous records. The first record of each ticker has balance = quantidade.
2. avg_price: if tipo = Compra, then ((preco * quantidade) + ([previous] balance * [previous] avg_price)) / (quantidade + [previous] balance). If tipo = Venda, then avg_price = [previous] avg_price. The first record of each ticker has avg_price = preco. In MS Excel it's very similar to SUMPRODUCT.

Expected result:

Tks!

Your existing query is capable of producing the wanted result, I have included the previous quantidade as well, but it does not seem to be needed:

WITH RECURSIVE negociacao
AS (
    SELECT row_number() OVER (
            PARTITION BY ticker ORDER BY ticker, data
            ) AS seq, data, ticker, tipo, preco, quantidade AS qnt
          , coalesce(lag(quantidade) over(partition by ticker
                                 order by data ASC),0) as prev_qnt
    FROM stock_tracking_negociacao
    WHERE tracking IS NULL
    ), adjust_table
AS (
    SELECT *, SUM(CASE WHEN tipo = 'Compra' THEN qnt ELSE - qnt END) OVER (
            PARTITION BY ticker ORDER BY seq
            ) AS balance
    FROM negociacao
    ), recurse
AS (
    SELECT adjust_table.*, preco AS avg
    FROM adjust_table
    WHERE seq = 1
    
    UNION ALL
    
    SELECT n.*, CASE WHEN n.tipo = 'Compra' THEN ((n.preco * n.qnt * 1.0) + (s.balance * s.avg)) / (n.qnt + s.balance) ELSE s.avg END AS avg
    FROM adjust_table n
    INNER JOIN recurse s ON n.seq = s.seq + 1
        AND n.ticker = s.ticker
    )

SELECT
       *
  --, round(balance * medio, 2) AS valor_total
FROM recurse

fiddle