How would you design a database schema for Mercury that supports a multi-tenant ledger system where startups can track internal transactions?
Use the provided relational model to produce a tenant-isolated account summary for posted transactions during January 2025. Include accounts with no qualifying activity.
Output
- One row per Mercury account.
- Columns:
startup_name, account_id, account_name, balance_cents, posted_transaction_count, latest_posted_at, and balance_rank.
- Include only activity from the account's startup, exclude non-posted or out-of-range transactions, and treat missing activity as zero.
- Order by startup, balance rank ascending, then account ID ascending.
Output details
- Treat January as
2025-01-01 inclusive through 2025-02-01 exclusive.
- Rank accounts separately within each startup by
balance_cents descending.
- Assign equal balances the same
balance_rank, leaving gaps after ties.
- Count each qualifying transaction once per account, even when it has multiple ledger entries.
- Return
latest_posted_at as NULL for accounts with no qualifying activity.
- Order startups ascending before applying the specified rank and account ID ordering.
