Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE prices (ticker, dt, price) AS
select 'AAPL' ticker, date'2022-01-01' dt, 1.0 price FROM DUAL UNION ALL
select 'AAPL' ticker, date'2022-01-02' dt, 1.1 price FROM DUAL UNION ALL
select 'AAPL' ticker, date'2022-01-03' dt, 1.2 price FROM DUAL UNION ALL
select 'AAPL' ticker, date'2022-01-04' dt, 1.3 price FROM DUAL UNION ALL
select 'AAPL' ticker, date'2022-01-05' dt, 1.1 price FROM DUAL UNION ALL
select 'AAPL' ticker, date'2022-01-06' dt, 1.0 price FROM DUAL UNION ALL
select 'AAPL' ticker, date'2022-01-07' dt, 1.1 price FROM DUAL UNION ALL
select 'GOOG' ticker, date'2022-01-01' dt, 10.3 price FROM DUAL UNION ALL
select 'GOOG' ticker, date'2022-01-02' dt, 10.5 price FROM DUAL UNION ALL
select 'GOOG' ticker, date'2022-01-03' dt, 9.2 price FROM DUAL UNION ALL
select 'GOOG' ticker, date'2022-01-04' dt, 10.1 price FROM DUAL UNION ALL
select 'GOOG' ticker, date'2022-01-05' dt, 11.1 price FROM DUAL UNION ALL
select 'GOOG' ticker, date'2022-01-06' dt, 10.0 price FROM DUAL UNION ALL
select 'GOOG' ticker, date'2022-01-07' dt, 10.1 price FROM DUAL UNION ALL
select 'MSFT' ticker, date'2022-01-01' dt, 5.0 price FROM DUAL UNION ALL
select 'MSFT' ticker, date'2022-01-02' dt, 5.1 price FROM DUAL UNION ALL
select 'MSFT' ticker, date'2022-01-03' dt, 4.2 price FROM DUAL UNION ALL
select 'MSFT' ticker, date'2022-01-04' dt, 6.3 price FROM DUAL UNION ALL
select 'MSFT' ticker, date'2022-01-05' dt, 5.1 price FROM DUAL UNION ALL
select 'MSFT' ticker, date'2022-01-06' dt, 4.9 price FROM DUAL UNION ALL
select 'MSFT' ticker, date'2022-01-07' dt, 5.3 price FROM DUAL;
21 rows affected

CREATE TABLE orders (ticker, dt, type, shares) as
select 'AAPL' ticker, date'2022-01-02' dt, 'Buy' type, 1000 shares FROM DUAL UNION ALL
select 'GOOG' ticker, date'2022-01-02' dt, 'Buy' type, 100 shares FROM DUAL UNION ALL
select 'AAPL' ticker, date'2022-01-04' dt, 'Sell' type, -100 shares FROM DUAL UNION ALL
select 'AAPL' ticker, date'2022-01-04' dt, 'Sell' type, -50 shares FROM DUAL UNION ALL
select 'AAPL' ticker, date'2022-01-05' dt, 'Sell' type, -100 shares FROM DUAL UNION ALL
select 'GOOG' ticker, date'2022-01-05' dt, 'Buy' type, 1 shares FROM DUAL;
6 rows affected

-- Can have more than 1 order on the same day and same ticker
-- Can have multiple orders for multiple tickers on the same day
-- This table can have tens of thousands of orders but usually less than 20 tickers
select o.ticker,
       o.dt,
       p.price share_price,
       sum(o.shares) order_shares,
       sum(o.shares * p.price) order_dollars,
       sum(sum(o.shares)) over(partition by o.ticker order by o.dt) balance_shares,
       sum(sum(o.shares)) over(partition by o.ticker order by o.dt) * p.price balance_dollars,
       (
         select sum(o1.shares * p1.price)
         from  orders o1
               inner join prices p1
               on p1.ticker = o1.ticker
                  and p1.dt = o.dt
         where o1.dt <= o.dt
       ) portfolio_balance_dollars
from   orders o
       inner join prices p on p.ticker = o.ticker and p.dt = o.dt
group by o.ticker, o.dt, p.price
order by o.dt, o.ticker, p.price;


TICKER
DT
SHARE_PRICE
ORDER_SHARES
ORDER_DOLLARS
BALANCE_SHARES
BALANCE_DOLLARS
PORTFOLIO_BALANCE_DOLLARS


AAPL
02-JAN-22
1.1
1000
1100
1000
1100
2150

GOOG
02-JAN-22
10.5
100
1050
100
1050
2150

AAPL
04-JAN-22
1.3
-150
-195
850
1105
2115

AAPL
05-JAN-22
1.1
-100
-110
750
825
1946.1

GOOG
05-JAN-22
11.1
1
11.1
101
1121.1
1946.1
TICKER	DT	SHARE_PRICE	ORDER_SHARES	ORDER_DOLLARS	BALANCE_SHARES	BALANCE_DOLLARS	PORTFOLIO_BALANCE_DOLLARS
AAPL	02-JAN-22	1.1	1000	1100	1000	1100	2150
GOOG	02-JAN-22	10.5	100	1050	100	1050	2150
AAPL	04-JAN-22	1.3	-150	-195	850	1105	2115
AAPL	05-JAN-22	1.1	-100	-110	750	825	1946.1
GOOG	05-JAN-22	11.1	1	11.1	101	1121.1	1946.1