Imagine you are at Walmart store and go to checkout stage, you would have to pick item from the shelf and take it from availability for other shoppers, before you pay for the item.
What shopify did, is customer enters the store, heads straight to checkout and retail workers races back to shelves to pick up items for client. Sometimes it says: sorry bud, item is sold out, frustrating customer experience, who is already mentally prepared to pay and own an item.
Re SKU storage, you will have multiple row entries per SKU, if I order two items, there will be two rows corresponding to the items in your Purchase order.
The sum() aggregation check will run across all active orders per sku
Re single tenant: shopify creates 1000 rows per SKU per shop(tenant!!). As long as tenant is on the same DB you can run it, just add shop_id to the group by field