▸case-01 Our Node.js API endpoint is making hundreds of individual database requests when fetching a list of blog posts with their comments, causing huge latency spikes. Could you diagnose why this N+1 pattern is happening and give me a clear step-by-step refactoring plan with SQL optimization strategies to batch or consolidate these queries? | pass→fail | 25,068 | 22,682 | -10% | 1 | 1 | 0% | 3,083 | 3,647 | +18% | 0 | 0 | — |
▸case-02 We have a PostgreSQL query fetching page 500 of user activity logs: 'SELECT * FROM activity_logs ORDER BY created_at DESC LIMIT 50 OFFSET 25000;'. It takes 4 seconds. The team wants to increase the work_mem memory parameter to fix this. How should we rewrite this query for fast pagination? | pass→pass | 17,194 | 15,193 | -12% | 1 | 1 | 0% | 2,238 | 2,060 | -8% | 0 | 0 | — |
▸case-03 Our nightly report query is doing 'SELECT order_id, total FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023;'. It runs a full table scan even though we have an index on order_date. The team suggests forcing an index scan hint. How should the query be rewritten? | pass→pass | 8,589 | 12,817 | +49% | 1 | 1 | 0% | 1,639 | 1,643 | +0% | 0 | 0 | — |
▸case-04 We frequently execute 'SELECT user_id, status FROM transactions WHERE status = 'PENDING' AND created_at >= '2024-01-01';'. A developer suggests creating a composite index on (created_at, status) because created_at has higher selectivity. Should we put created_at first or status first? | pass→pass | 16,841 | 10,120 | -40% | 1 | 1 | 0% | 2,128 | 2,197 | +3% | 0 | 0 | — |
▸case-05 We have a query filtering active users with orders: 'SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);'. A developer says IN and EXISTS perform identically so no optimization is needed, but orders.user_id allows NULLs. What is the optimal approach? | pass→pass | 20,213 | 19,771 | -2% | 1 | 1 | 0% | 2,757 | 2,864 | +4% | 0 | 0 | — |
▸case-06 Our high-throughput query runs 10,000 times per minute: 'SELECT email, created_at FROM users WHERE account_status = 'ACTIVE';'. We already have an index on (account_status). The query plan shows index scans followed by heavy heap table fetch operations. How can we eliminate table lookup IO without adding extra indexed keys to the B-tree structure? | pass→pass | 13,719 | 17,844 | +30% | 1 | 1 | 0% | 2,422 | 2,549 | +5% | 0 | 0 | — |
▸case-07 We have a query: 'SELECT * FROM products WHERE category_id = 42 OR brand_id = 99;'. We have single-column indexes on category_id and brand_id, but the query planner refuses to use them and performs a full table scan. The dev team wants to force a full table cache. How should we refactor the SQL query? | pass→pass | 19,179 | 15,656 | -18% | 1 | 1 | 0% | 2,509 | 2,165 | -14% | 0 | 0 | — |
▸case-08 We have an audit_logs table with 50 million rows. 99% of rows have status = 'PROCESSED' and 1% have status = 'FAILED'. We regularly query 'SELECT * FROM audit_logs WHERE status = 'FAILED';'. Creating an index on status takes 15GB of disk. How can we optimize this query's index footprint? | pass→pass | 16,919 | 14,804 | -13% | 1 | 1 | 0% | 2,116 | 2,007 | -5% | 0 | 0 | — |
▸case-09 A dashboard query joins 5 large tables using 'SELECT * FROM orders o JOIN order_items i ON o.id = i.order_id JOIN products p ON i.product_id = p.id...'. The database server memory spikes during execution. A developer recommends doubling database RAM. How should this query be optimized? | pass→fail | 17,200 | 12,447 | -28% | 1 | 1 | 0% | 2,239 | 2,501 | +12% | 0 | 0 | — |
▸case-10 In PostgreSQL 14, we have a query with a Common Table Expression: 'WITH regional_sales AS (SELECT * FROM sales WHERE region = 'US') SELECT * FROM regional_sales WHERE sale_date > '2024-01-01';'. The query is slow because the CTE evaluates all US sales before filtering date. How do we ensure the query planner pushes down predicates into the CTE? | pass→pass | 12,006 | 15,719 | +31% | 1 | 1 | 0% | 2,295 | 2,245 | -2% | 0 | 0 | — |
▸case-11 In a MySQL database, phone_number is indexed and stored as VARCHAR(20). The query 'SELECT * FROM accounts WHERE phone_number = 5550199;' performs a full table scan despite the index. A junior dev suggests rebuilding the index. Why is the index ignored and how should the query be fixed? | pass→pass | 12,608 | 11,413 | -9% | 1 | 1 | 0% | 1,424 | 1,623 | +14% | 0 | 0 | — |
▸case-12 A developer wrote 'SELECT DISTINCT user_id FROM orders WHERE status = 'SHIPPED';' to get a list of users with shipped orders, but noticed it generates an expensive aggregate hash operation across millions of order records. How should this query be optimized? | pass→pass | 12,051 | 9,963 | -17% | 1 | 1 | 0% | 2,247 | 2,079 | -7% | 0 | 0 | — |
▸case-13 An analytics dashboard displays total row count for an events table containing 200 million rows using 'SELECT COUNT(*) FROM events;'. The query takes 12 seconds and locks resources. The team wants an instant estimate for display. How should this be handled? | fail→pass | 17,218 | 17,467 | +1% | 1 | 1 | 0% | 2,103 | 2,501 | +19% | 0 | 0 | — |
▸case-14 A search query executes 'SELECT * FROM customers WHERE company_name LIKE '%Tech%';'. B-tree indexes on company_name are present but ignored. The team wants to force a B-tree index scan hint. How can we properly optimize substring searches? | pass→pass | 13,342 | 18,410 | +38% | 1 | 1 | 0% | 2,379 | 2,740 | +15% | 0 | 0 | — |
▸case-15 To find each employee's salary alongside their department's average salary, a developer wrote 'SELECT e.id, e.salary, d.avg_sal FROM employees e JOIN (SELECT dept_id, AVG(salary) as avg_sal FROM employees GROUP BY dept_id) d ON e.dept_id = d.dept_id;'. This scans the employees table twice. How can we optimize this to scan the table only once? | pass→pass | 5,627 | 6,360 | +13% | 1 | 1 | 0% | 1,117 | 1,329 | +19% | 0 | 0 | — |
▸case-16 We need to delete 20 million expired session rows using 'DELETE FROM sessions WHERE expires_at < NOW();'. Running this statement locks the table for 15 minutes and exhausts the database transaction log. How should this mass deletion be structured? | pass→pass | 14,618 | 14,585 | -0% | 1 | 1 | 0% | 2,663 | 2,770 | +4% | 0 | 0 | — |
▸case-17 Here is an EXPLAIN (ANALYZE) snippet from PostgreSQL: 'Seq Scan on orders (cost=0.00..18500.00 rows=10 width=32) (actual time=0.015..450.120 rows=500000 loops=1)'. The query planner estimated 10 rows but actual rows returned were 500,000, choosing a slow nested loop. How do we fix the query planner's row estimate mismatch? | pass→pass | 14,545 | 18,352 | +26% | 1 | 1 | 0% | 2,368 | 2,760 | +17% | 0 | 0 | — |
▸case-18 We run 'SELECT id, title, published_at FROM articles WHERE status = 'PUBLISHED' ORDER BY published_at DESC LIMIT 10;'. Even with an index on published_at, the database performs a full sort in memory. What composite index will allow the engine to avoid a sort operation entirely? | pass→pass | 13,668 | 12,785 | -6% | 1 | 1 | 0% | 1,507 | 1,629 | +8% | 0 | 0 | — |
▸case-19 An ingestion pipeline runs thousands of individual 'INSERT INTO metrics (device_id, timestamp, temp) VALUES (...) ON CONFLICT (device_id, timestamp) DO UPDATE SET temp = EXCLUDED.temp;' statements per second. Database CPU is at 95%. How should this insert load be optimized at the query level? | pass→pass | 14,222 | 17,830 | +25% | 1 | 1 | 0% | 2,672 | 2,633 | -1% | 0 | 0 | — |
▸case-20 We are tuning a MongoDB aggregation pipeline for user activity logs: db.logs.aggregate([{$match: {status: 'active'}}, {$group: {_id: '$user', total: {$sum: '$amount'}}}]);. How should we configure the MongoDB sharding key and chunk size for optimal cluster balancing? | fail→fail | 19,126 | 17,409 | -9% | 1 | 1 | 0% | 3,215 | 3,124 | -3% | 0 | 0 | — |
▸case-21 We are setting up Flyway for database schema migrations in a Java application. Could you write the Flyway configuration file (flyway.conf) to set baselineOnMigrate=true, specify migration location filesystem paths, and set up schema history table name? | fail→fail | 12,411 | 12,712 | +2% | 1 | 1 | 0% | 1,468 | 1,767 | +20% | 0 | 0 | — |
▸case-22 We are configuring a Redis instance used for caching database query results. Should we set volatile-lru, allkeys-lru, or maxmemory-samples in redis.conf when RAM reaches 8GB? | fail→fail | 18,425 | 13,992 | -24% | 1 | 1 | 0% | 2,324 | 1,812 | -22% | 0 | 0 | — |
▸case-23 We need to perform a physical point-in-time recovery backup using pg_basebackup with WAL streaming. What exact CLI flags should be passed to pg_basebackup to enable write-ahead log streaming and progress output? | fail→fail | 6,581 | 10,168 | +55% | 1 | 1 | 0% | 1,232 | 1,226 | -0% | 0 | 0 | — |