Uniqueness, Secondary Lookups and Joins Across Shards

An e-commerce backend has sharded users and orders by user_id. Three requirements arrive:

• Email addresses must remain globally unique at sign-up.
• Support agents look up an order by order_id alone.
• The seller dashboard needs "all orders for seller S in the last 24 hours".

1. For each requirement, explain why the current sharding makes it hard.
2. Propose a design for each, stating consistency and cost trade-offs.
3. Which of the three would you push back on as a product/architecture conversation rather than solve purely in the database?