I have 5 tables related to sales. Three of them are like this:

The concepts used are linked below.

Window Functions: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

Common Table Expressions (CTE): https://www.mysqltutorial.org/mysql-cte/