▸case-01 We have an e-commerce orders table with 50 million rows. The web API currently fetches page 5,000 using `SELECT * FROM orders ORDER BY id DESC LIMIT 20 OFFSET 100000`, but response times exceed 8 seconds. How should the SQL query be rewritten to fetch the next batch instantly without scanning 100,000 skipped rows? | pass→pass | 11,435 | 11,553 | +1% | 1 | 1 | 0% | 2,007 | 2,052 | +2% | 0 | 0 | — |
▸case-02 Our microservices generate UUID v4 values for user IDs. A developer proposed storing them in MySQL as `VARCHAR(36)`. What column data type and conversion strategy should be used in MySQL 8.0 to optimize both storage space and clustered index insertion performance? | fail→fail | 18,249 | 15,910 | -13% | 1 | 1 | 0% | 3,138 | 2,791 | -11% | 0 | 0 | — |
▸case-03 A user table in MySQL was created with `DEFAULT CHARSET=utf8`. When users post comments containing emojis, MySQL throws an error or strips characters. What character set and collation should be configured on the MySQL table to fully support 4-byte UTF-8 characters? | pass→pass | 5,343 | 5,886 | +10% | 1 | 1 | 0% | 842 | 1,103 | +31% | 0 | 0 | — |
▸case-04 An inventory system needs to store dynamic custom attributes for products (e.g. color, size, voltage). The initial draft used an Entity-Attribute-Value schema with `product_attributes(product_id, attribute_name, attribute_value)` requiring multiple JOINs. What native MySQL column feature should be used to store these attributes in a single table while allowing indexed query filtering? | pass→pass | 8,357 | 6,612 | -21% | 1 | 1 | 0% | 1,432 | 1,147 | -20% | 0 | 0 | — |
▸case-05 In MySQL, we execute `SELECT * FROM transactions WHERE user_id = 42 AND transaction_date >= '2023-01-01' ORDER BY transaction_date DESC`. A developer created a composite index `(transaction_date, user_id)`. Why is this index ordering suboptimal and how should the composite index columns be ordered? | pass→pass | 10,489 | 10,156 | -3% | 1 | 1 | 0% | 1,958 | 1,925 | -2% | 0 | 0 | — |
▸case-06 A developer is designing a financial ledger schema in MySQL and wants to store transaction amounts as `FLOAT` or `DOUBLE` to support fractional cents. What database column definition should be used to guarantee exact decimal arithmetic and eliminate floating-point rounding errors? | pass→pass | 7,722 | 4,876 | -37% | 1 | 1 | 0% | 1,541 | 932 | -40% | 0 | 0 | — |
▸case-11 An article database runs `SELECT * FROM articles WHERE content LIKE '%database%'` across 5 million rows, causing full table scans. How should the MySQL schema and query be restructured to perform efficient keyword searches on large text fields? | pass→pass | 14,535 | 14,855 | +2% | 1 | 1 | 0% | 2,373 | 2,442 | +3% | 0 | 0 | — |
▸case-07 We need to store millions of IPv4 and IPv6 network request addresses in a MySQL analytics table. Storing them as `VARCHAR(45)` consumes too much disk space and index RAM. What binary column type and built-in MySQL functions should be used for optimal storage and retrieval? | pass→pass | 13,649 | 9,857 | -28% | 1 | 1 | 0% | 2,667 | 1,872 | -30% | 0 | 0 | — |
▸case-08 We need to add a non-null column with a default value to a 200GB production table on MySQL 5.7 that handles high concurrent writes. A direct `ALTER TABLE` locks write operations and causes site downtime. What zero-downtime migration strategy or tool should be applied? | fail→fail | 16,330 | 28,511 | +75% | 1 | 1 | 0% | 2,808 | 2,723 | -3% | 0 | 0 | — |
▸case-09 A data pipeline imports 100,000 log records into MySQL by executing individual `INSERT INTO logs VALUES (...)` statements in a Python loop with auto-commit enabled. It takes over 15 minutes. How should the SQL execution pattern be modified to reduce transaction overhead and speed up bulk insertion? | pass→pass | 13,861 | 12,178 | -12% | 1 | 1 | 0% | 2,316 | 2,178 | -6% | 0 | 0 | — |
▸case-10 An application checks if a user session exists with `SELECT id FROM user_sessions WHERE session_token = 'xyz'`, and then either runs `INSERT` or `UPDATE`. Under high concurrency, this race condition causes duplicate key errors. What atomic MySQL query syntax handles this insert-or-update requirement in a single statement? | pass→pass | 7,771 | 6,334 | -18% | 1 | 1 | 0% | 1,510 | 1,158 | -23% | 0 | 0 | — |
▸case-12 Global users record activity timestamps in MySQL. Developers are deciding between `DATETIME` and `TIMESTAMP`. If the system requires automated conversion to UTC on store and conversion to session time zone on retrieval, which column type should be chosen? | pass→pass | 11,158 | 5,257 | -53% | 1 | 1 | 0% | 1,222 | 866 | -29% | 0 | 0 | — |
▸case-13 A multi-tenant MySQL table uses `is_deleted TINYINT(1) DEFAULT 0`. Queries filtering `WHERE tenant_id = 5 AND is_deleted = 0` perform poorly because `is_deleted` has low cardinality. How should soft deletion be redesigned to improve query index efficiency? | pass→fail | 21,188 | 41,657 | +97% | 1 | 1 | 0% | 3,501 | 3,724 | +6% | 0 | 0 | — |
▸case-14 A high-frequency clickstream event table was created with `id INT AUTO_INCREMENT PRIMARY KEY`. The database administrator warns that the table will hit the maximum ID capacity in 2 weeks. What exact integer column definition should be used to prevent ID overflow? | pass→pass | 5,141 | 4,577 | -11% | 1 | 1 | 0% | 946 | 774 | -18% | 0 | 0 | — |
▸case-15 In legacy MySQL installations, inserting a 100-character string into a `VARCHAR(50)` column silently truncated data with a warning instead of raising an error. Which SQL mode setting enforces strict data validation and fails invalid inserts? | pass→pass | 4,855 | 4,970 | +2% | 1 | 1 | 0% | 802 | 862 | +7% | 0 | 0 | — |
▸case-21 When implementing a caching layer using Redis alongside an RDBMS in a Node.js microservice, what step-by-step logic should be followed during a read operation under the Cache-Aside pattern? | pass→pass | 14,485 | 13,933 | -4% | 1 | 1 | 0% | 2,613 | 2,774 | +6% | 0 | 0 | — |
▸case-16 Two inventory management workers read row data simultaneously using `SELECT balance FROM accounts WHERE account_id = 1` and update the balance, causing lost updates. What SQL clause should be appended to the SELECT query within a transaction to lock the read row for updates? | pass→pass | 3,193 | 2,762 | -13% | 1 | 1 | 0% | 492 | 522 | +6% | 0 | 0 | — |
▸case-17 A developer proposes using random SHA-256 strings as the clustered primary key on an InnoDB table. Why does this cause severe write performance degradation over time, and what key property prevents this issue? | pass→pass | 13,867 | 9,859 | -29% | 1 | 1 | 0% | 2,175 | 1,743 | -20% | 0 | 0 | — |
▸case-18 An administrative dashboard executes `SELECT COUNT(*) FROM audit_logs` every 5 seconds on a 50-million-row InnoDB table, taking several seconds each time. What architectural pattern or metadata source should be used for real-time dashboard display without running table scans? | fail→pass | 15,238 | 14,283 | -6% | 1 | 1 | 0% | 2,503 | 2,505 | +0% | 0 | 0 | — |
▸case-19 A MySQL query uses `GROUP_CONCAT(tags)` to combine thousands of tags, but the resulting output string is truncated at 1024 characters. What system variable must be adjusted to allow larger concatenated string outputs? | pass→pass | 4,694 | 3,713 | -21% | 1 | 1 | 0% | 736 | 666 | -10% | 0 | 0 | — |
▸case-20 In PostgreSQL 15, how do you write a SQL statement to create a parent partitioned table by range on `created_at` timestamp column and define a child partition for January 2024? | pass→pass | 6,770 | 5,006 | -26% | 1 | 1 | 0% | 1,315 | 977 | -26% | 0 | 0 | — |
▸case-22 In MongoDB, how should you model a 1-to-N relationship between a Course document and 100,000 Student Enrollment records to prevent exceeding the 16MB document size limit? | pass→pass | 15,166 | 14,770 | -3% | 1 | 1 | 0% | 2,652 | 2,761 | +4% | 0 | 0 | — |