▸case-01 We are building a telemetry platform receiving 1,000,000 sensor payload writes per second from industrial equipment. Queries will primarily analyze aggregated sensor metrics across sliding time windows (e.g., 5-minute averages over the past 30 days). A traditional relational table setup is struggling under write volume. Recommend an appropriate database technology family and specific database engine for this workload, explaining the storage architecture benefits. | pass→pass | 17,237 | 19,829 | +15% | 1 | 1 | 0% | 2,563 | 6,517 | +154% | 0 | 0 | — |
▸case-02 An enterprise employee directory needs to support real-time organizational chart traversals and deep relationship pathfinding up to 8 hops deep (e.g., finding shortest reporting paths or shared cross-team project ownership). Relational SQL queries using recursive common table expressions are timing out. Recommend a dedicated database technology model and specific database solution suited for low-latency graph traversals. | pass→pass | 13,326 | 17,484 | +31% | 1 | 1 | 0% | 2,087 | 6,375 | +205% | 0 | 0 | — |
▸case-03 A globally distributed financial payment gateway requires active-active multi-region database deployments across US, EU, and Asia. The business demands strict serializable ACID transactions for account balance transfers with zero stale reads across regions. Standard asynchronous primary-replica setups risk data loss during failover. Recommend a database class and target technology engineered for globally distributed ACID compliance. | pass→pass | 17,053 | 18,607 | +9% | 1 | 1 | 0% | 2,696 | 6,439 | +139% | 0 | 0 | — |
▸case-04 We are designing a document-based database model for an e-commerce platform handling order records. Each order contains between 1 and 10 line items. Once an order is placed, line items are immutable historical records that are never queried across orders independently. Devs are planning to split orders and line items into separate collections with references. Provide the optimal schema modeling approach for this access pattern. | pass→pass | 11,031 | 11,485 | +4% | 1 | 1 | 0% | 2,076 | 5,454 | +163% | 0 | 0 | — |
▸case-05 A B2B SaaS platform serves both thousands of small self-service business clients and several large healthcare enterprise clients bound by strict HIPAA data isolation requirements. Architect a multi-tenant database strategy that balances operational efficiency for self-service clients while satisfying strict data isolation requirements for enterprise tenants. | pass→pass | 21,691 | 22,684 | +5% | 1 | 1 | 0% | 3,336 | 6,999 | +110% | 0 | 0 | — |
▸case-06 A financial regulatory system requires tracking historical changes to client residential addresses. Auditing rules mandate that any transaction historical report must query the exact residential address a client held on the transaction date. Developers plan to simply update the address string on the client row. Provide a database schema strategy to handle this temporal requirement. | pass→pass | 16,469 | 15,430 | -6% | 1 | 1 | 0% | 3,128 | 6,087 | +95% | 0 | 0 | — |
▸case-07 In PostgreSQL, an order fulfillment service frequently executes the query `SELECT * FROM shipments WHERE status = 'SHIPPED' AND shipped_at >= '2023-01-01' ORDER BY shipped_at DESC`. Developers created a composite index `(shipped_at, status)`. Is this index order optimal for B-Tree index traversal, and if not, how should the composite index columns be ordered? | pass→pass | 10,528 | 10,412 | -1% | 1 | 1 | 0% | 1,988 | 5,341 | +169% | 0 | 0 | — |
▸case-08 A task processing queue table contains 50,000,000 rows. Over 99% of rows have `status = 'COMPLETED'`, while less than 0.5% have `status = 'PENDING'`. Worker processes constantly query `SELECT * FROM tasks WHERE status = 'PENDING'` to pick up jobs. Developers want to build a full index on the `status` column. Recommend an optimized indexing strategy that minimizes disk footprint and maintenance overhead. | pass→pass | 13,280 | 12,925 | -3% | 1 | 1 | 0% | 2,296 | 5,595 | +144% | 0 | 0 | — |
▸case-09 A media news portal experiences extreme traffic spikes when breaking news articles expire from Redis cache. Simultaneous cache misses trigger hundreds of concurrent queries against the database for the exact same article row, causing database CPU exhaustion. Propose an architectural pattern for the caching layer to prevent this cache stampede. | pass→pass | 17,185 | 18,465 | +7% | 1 | 1 | 0% | 2,698 | 6,488 | +140% | 0 | 0 | — |
▸case-10 An e-commerce wallet service tracks real-time account balances. Devs propose using an asynchronous write-behind (write-back) caching strategy where wallet updates are written only to Redis first and persisted to PostgreSQL asynchronously every 5 minutes. Evaluate this caching pattern against financial consistency requirements and propose a safer caching write model. | pass→pass | 20,789 | 20,255 | -3% | 1 | 1 | 0% | 3,164 | 6,979 | +121% | 0 | 0 | — |
▸case-11 A multi-tenant application with 100,000 tenants is sharding its relational database across 16 database nodes. Over 98% of application queries contain a `tenant_id` filter. Developers suggest sharding tables on auto-incrementing record primary key `id` columns. Evaluate this shard key choice and recommend the optimal sharding key. | pass→pass | 14,168 | 15,916 | +12% | 1 | 1 | 0% | 2,396 | 5,991 | +150% | 0 | 0 | — |
▸case-17 A peer-to-peer money transfer engine executes balance transfers between two accounts. Concurrent transfer requests are suffering from phantom reads and write skew inconsistencies under default `READ COMMITTED` isolation level, leading to negative account balances. Recommend the target database isolation level and concurrency control pattern to guarantee transfer integrity. | pass→pass | 14,375 | 14,345 | -0% | 1 | 1 | 0% | 2,458 | 5,987 | +144% | 0 | 0 | — |
▸case-12 A social application directs all read traffic to database read replicas. Immediately after a user updates their profile bio, the web app redirects them back to their profile page, which reads from a replica. Users complain that their bio updates are missing for several seconds. Explain the underlying issue and propose two database architectural remedies without abandoning replicas. | pass→pass | 14,012 | 22,547 | +61% | 1 | 1 | 0% | 2,105 | 5,952 | +183% | 0 | 0 | — |
▸case-13 A high-traffic web platform needs to rename the column `user_phone_number` to `phone_number` in a PostgreSQL table with 200 million rows under continuous read/write traffic. Executing an immediate column rename will break running application instances during deployment. Design a zero-downtime, multi-phase migration strategy for this column rename. | pass→pass | 19,563 | 16,291 | -17% | 1 | 1 | 0% | 3,473 | 6,152 | +77% | 0 | 0 | — |
▸case-14 We have finalized our database schema changes. Please connect to our staging PostgreSQL server at `pg-staging.internal:5432` with username `admin` and execute the schema migration script directly to update the tables. | pass→pass | 4,755 | 8,578 | +80% | 1 | 1 | 0% | 704 | 4,833 | +587% | 0 | 0 | — |
▸case-15 Architect a relational database schema for a subscription billing platform containing Customers, Subscriptions, Invoices, and PaymentMethods. The user explicitly asks: Please include a complete ERD diagram in your output. | pass→pass | 16,566 | 25,758 | +55% | 1 | 1 | 0% | 3,752 | 8,580 | +129% | 0 | 0 | — |
▸case-16 Design a relational schema for a library management system with books, authors, and loans. Provide table definitions, constraints, primary keys, and index strategy. | pass→pass | 17,031 | 17,799 | +5% | 1 | 1 | 0% | 3,452 | 6,757 | +96% | 0 | 0 | — |
▸case-18 An application log database stores 10 billion events in a single PostgreSQL table, causing vacuum operations and query performance to degrade severely. 95% of queries filter logs by a specific date range within the last 30 days, and logs older than 90 days must be purged daily. Recommend a table partitioning strategy and lifecycle management approach. | pass→pass | 17,899 | 20,412 | +14% | 1 | 1 | 0% | 2,913 | 6,918 | +137% | 0 | 0 | — |
▸case-19 A globally distributed mobile app allows users to update their user profile settings from mobile clients connected to active-active database clusters in both Europe and North America. Simultaneous updates in both regions cause write conflicts during multi-region replication. Architect a strategy for resolving multi-region write conflicts deterministically. | pass→pass | 23,304 | 21,872 | -6% | 1 | 1 | 0% | 3,898 | 6,988 | +79% | 0 | 0 | — |
▸case-20 A database administrator strictly prohibits altering schemas, changing data models, or adding database indexes. You are given an existing query: `SELECT * FROM orders WHERE status = 'PENDING' AND created_at > '2023-01-01' ORDER BY id DESC LIMIT 20`. The query runs slowly because of unbounded SELECT * projection and inefficient sorting. Optimize this query strictly within query-level SQL refactoring constraints without changing database infrastructure or schemas. | pass→fail | 12,518 | 12,399 | -1% | 1 | 1 | 0% | 1,939 | 5,218 | +169% | 0 | 0 | — |
▸case-21 Design the client-side user interface workflow, React state management, and form validation error handling for a multi-step user registration wizard in a web application. | pass→fail | 30,190 | 31,736 | +5% | 1 | 1 | 0% | 6,023 | 9,592 | +59% | 0 | 0 | — |
▸case-22 You are integrating with a third-party legacy database view where you have read-only query permissions and zero ability to modify schemas, indexes, or database parameters. Write a SQL query to calculate the 7-day rolling average revenue per product category from the `vw_sales_transactions` view. | pass→pass | 13,094 | 12,471 | -5% | 1 | 1 | 0% | 2,122 | 5,517 | +160% | 0 | 0 | — |