MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE products (
 code varchar(10) NOT NULL,
 name varchar(100) NOT NULL,
 PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE transactions (
 date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 code varchar(10) NOT NULL,
 mode enum('Buy','Sell','Count','Return') NOT NULL,
 stock int(11) NOT NULL,
 average_price decimal(14,4) NOT NULL,
 PRIMARY KEY (date),
 KEY Code (code),
 CONSTRAINT transactions_ibfk_1 FOREIGN KEY (code) REFERENCES products (code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT INTO products(code, name) VALUES
  ("A", "Alpha"),
  ("B", "Beta")
SELECT code, name FROM products


code
name


A
Alpha

B
Beta


INSERT INTO transactions(date, code, mode, stock, average_price) VALUES
  ("2000-1-2", "A", "Buy", 5, 1),
  ("2000-1-3", "B", "Buy", 4, 2),
  ("2000-1-4", "B", "Sell", 2, 3),
  ("2000-1-5", "B", "Sell", 1, 4),
  ("2000-1-6", "A", "Buy", 6, 5),
  ("2000-1-7", "A", "Buy", 8, 6)
SELECT date, code, mode, stock, average_price FROM transactions


date
code
mode
stock
average_price


2000-01-02 00:00:00
A
Buy
5
1.0000

2000-01-03 00:00:00
B
Buy
4
2.0000

2000-01-04 00:00:00
B
Sell
2
3.0000

2000-01-05 00:00:00
B
Sell
1
4.0000

2000-01-06 00:00:00
A
Buy
6
5.0000

2000-01-07 00:00:00
A
Buy
8
6.0000


Create VIEW products_view_joinA
  AS select t1.code, t1.name, t2.stock, t2.average_price as cost_usd
    from (products t1 left join (
      select t3.stock, t3.code, t3.average_price from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code) order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code)))
SELECT code, name, stock, cost_usd FROM products_view_joinA


code
name
stock
cost_usd


B
Beta
1
4.0000

A
Alpha
8
6.0000


Create VIEW products_view_joinB
  AS select t1.code, t1.name, t2.date as last_buy
    from (products t1 left join (
      select t3.code, t3.date from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code and t4.mode = "Buy") order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code)))
SELECT code, name, last_buy FROM products_view_joinB


code
name
last_buy


A
Alpha
2000-01-07 00:00:00

B
Beta
2000-01-03 00:00:00


Create VIEW products_view_joinA_and_JoinB
  AS select t1.code, t1.name, t2.stock, t2.average_price as cost_usd, t5.date as last_buy
    from (products t1 left join (
      select t3.stock, t3.code, t3.average_price from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code) order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code))
      left join(
        select t6.code, t6.date from transactions t6
        where (t6.date = (
          select t7.date from transactions t7 where (t7.code = t6.code and t7.mode = "Buy") order by t7.date desc limit 1)
       )) t5 on((t5.code = t1.code)))
SELECT code, name, stock, cost_usd, last_buy FROM products_view_joinA_and_JoinB


code
name
stock
cost_usd
last_buy


B
Beta
1
4.0000
2000-01-03 00:00:00

A
Alpha
8
6.0000
2000-01-07 00:00:00

date	code	mode	stock	average_price
2000-01-02 00:00:00	A	Buy	5	1.0000
2000-01-03 00:00:00	B	Buy	4	2.0000
2000-01-04 00:00:00	B	Sell	2	3.0000
2000-01-05 00:00:00	B	Sell	1	4.0000
2000-01-06 00:00:00	A	Buy	6	5.0000
2000-01-07 00:00:00	A	Buy	8	6.0000

code	name	stock	cost_usd	last_buy
B	Beta	1	4.0000	2000-01-03 00:00:00
A	Alpha	8	6.0000	2000-01-07 00:00:00