A large stock buy or sell order with multiple counterparties is typically divided into child orders, which can be further divided. An order to sell a specific stock can cause a cascade of transactions, each of which descends from the original order. For example, an order to sell 100 shares of a specific stock can trigger orders to sell 70 and 30 shares of that stock, and the order to sell 70 shares can trigger orders to sell 50 and 20 shares, and so on.
All stock transactions are stored in a single table. Each row represents one transaction, which is identified by its order_id and linked to its parent by its parent_id.
A stock broker must be able to identify the root order for each transaction. To do so with SQL requires an unknown number of self-joins, but with Aster Database SQL-MapReduce, you can partition the data by stock symbol and then by date and use the nTree function to create a tree from each root order.