▸case-01 I need an advanced SQL script for our data warehouse that computes rolling 30-day active user metrics and cohort retention percentages month-over-month. Please construct the query using modern analytical SQL constructs like window frames and CTEs. | fail→fail | 22,699 | 32,849 | +45% | 1 | 1 | 0% | 4,601 | 6,257 | +36% | 0 | 0 | — |
▸case-02 Our transactional application is experiencing slow writes whenever background analytics jobs run on the order history tables. Can you suggest an optimized query structure and database strategy to isolate analytical reporting from our production OLTP traffic without degrading write latency? | fail→fail | 14,256 | 22,756 | +60% | 1 | 1 | 0% | 2,468 | 3,643 | +48% | 0 | 0 | — |
▸case-03 We are building a Node.js application using Prisma ORM with PostgreSQL. We need advice on configuring Prisma Schema relation directives (`@relation`, `fields`, `references`) and using Prisma Client fluent query methods like `prisma.user.findUnique({ include: { posts: true } })` to handle nested object hydration. We do not want raw SQL queries or database engine plan analysis. | pass→pass | 17,188 | 25,014 | +46% | 1 | 1 | 0% | 3,424 | 4,419 | +29% | 0 | 0 | — |
▸case-04 We are migrating an application to MongoDB and need to write a MongoDB Aggregation Framework pipeline using `$match`, `$group`, and `$lookup` stages to aggregate user activity documents. The database is strictly document-based with no SQL interface. | pass→pass | 17,782 | 17,741 | -0% | 1 | 1 | 0% | 2,637 | 3,053 | +16% | 0 | 0 | — |
▸case-05 We are using a closed third-party SaaS analytics API that exposes a restricted GraphQL endpoint. We have no access to the underlying database schema, statistics, indexes, or EXPLAIN plans, and we need advice on configuring GraphQL query batching in Apollo Client. | pass→pass | 21,202 | 9,328 | -56% | 1 | 1 | 0% | 2,821 | 2,065 | -27% | 0 | 0 | — |
▸case-06 I am troubleshooting a missing invoice issue in PostgreSQL production database. I plan to run `SELECT * FROM invoice_line_items WHERE created_at >= '2023-01-01'` directly against the primary production instance to inspect all historical records. | pass→pass | 16,442 | 18,726 | +14% | 1 | 1 | 0% | 2,002 | 3,061 | +53% | 0 | 0 | — |
▸case-07 Our PostgreSQL database is running slow on `SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'PENDING'`. Should we immediately add a multi-column index on `(user_id, status)` without checking the current query plan? | fail→fail | 18,676 | 16,621 | -11% | 1 | 1 | 0% | 2,511 | 2,605 | +4% | 0 | 0 | — |
▸case-08 We are ingesting streaming event data into Snowflake with Snowpipe and running continuous real-time aggregations for a live dashboard. Can we run these heavy analytical aggregations directly on our active ingestion virtual warehouse cluster? | fail→fail | 19,934 | 22,857 | +15% | 1 | 1 | 0% | 2,344 | 3,398 | +45% | 0 | 0 | — |
▸case-09 We need to calculate a 7-day moving average of daily sales in MySQL 8.0. Write a query using `AVG(amount) OVER (ORDER BY sale_date)` without specifying frame boundaries. | fail→fail | 22,375 | 14,686 | -34% | 1 | 1 | 0% | 3,518 | 2,377 | -32% | 0 | 0 | — |
▸case-10 We query a 50 Terabyte Hive metastore table partitioned by `event_date` in Trino. A developer wants to query `WHERE YEAR(event_date) = 2024`. Is this filter optimal for partition pruning? | pass→pass | 12,085 | 14,196 | +17% | 1 | 1 | 0% | 2,094 | 2,197 | +5% | 0 | 0 | — |
▸case-11 We have a 15-step analytics pipeline in PostgreSQL that references a 10-million row CTE five separate times in downstream JOINs. Should we keep it as a standard WITH clause CTE? | fail→fail | 12,757 | 14,080 | +10% | 1 | 1 | 0% | 2,221 | 2,975 | +34% | 0 | 0 | — |
▸case-12 We load incremental updates into a PostgreSQL order fulfillment database. Should we use individual `SELECT` queries to check record existence followed by `INSERT` or `UPDATE` statements in single application transactions? | fail→fail | 14,024 | 12,987 | -7% | 1 | 1 | 0% | 2,582 | 2,911 | +13% | 0 | 0 | — |
▸case-13 We need to query an employee organizational chart in SQL Server using a recursive CTE. Is it safe to execute `WITH emp_cte AS (...) SELECT * FROM emp_cte` without recursion depth safeguards? | fail→fail | 19,736 | 18,956 | -4% | 1 | 1 | 0% | 2,667 | 2,916 | +9% | 0 | 0 | — |
▸case-14 We need to purge 50 million expired session records from a production PostgreSQL table. Should we run `DELETE FROM sessions WHERE expired_at < NOW();` in a single query during peak traffic? | pass→pass | 13,301 | 20,901 | +57% | 1 | 1 | 0% | 2,173 | 3,386 | +56% | 0 | 0 | — |
▸case-15 Our user profile API reads `user_metadata` joined with `user_settings` on every web request. To minimize latency, a developer suggests merging all tables into a single unindexed 200-column wide table. Is this appropriate? | fail→fail | 13,426 | 25,097 | +87% | 1 | 1 | 0% | 2,351 | 3,315 | +41% | 0 | 0 | — |
▸case-16 We have a PostgreSQL query filtering on a `status` column with values ('ACTIVE', 'INACTIVE') in a table with 10 million rows where 99% are 'ACTIVE'. Should we add a standard B-tree index on `status` to speed up queries filtering for 'ACTIVE'? | pass→pass | 16,695 | 20,333 | +22% | 1 | 1 | 0% | 2,096 | 3,180 | +52% | 0 | 0 | — |
▸case-17 We store IoT sensor readings timestamped every second in PostgreSQL. We want to aggregate hourly averages for a sensor over 30 days, even for hours where no readings were recorded. Should we use a standard `GROUP BY date_trunc('hour', timestamp)`? | fail→fail | 11,655 | 11,463 | -2% | 1 | 1 | 0% | 2,373 | 2,695 | +14% | 0 | 0 | — |
▸case-18 We need to retrieve the top 3 most recent orders for each customer across 500,000 customers in PostgreSQL. Is a correlated subquery in the `WHERE` clause optimal? | fail→fail | 18,101 | 18,824 | +4% | 1 | 1 | 0% | 2,435 | 3,114 | +28% | 0 | 0 | — |
▸case-19 We run a heavy analytical inventory audit query on MySQL InnoDB primary node using `SELECT ... FOR UPDATE` to snapshot inventory counts. How does this affect OLTP write latency? | pass→pass | 21,757 | 19,933 | -8% | 1 | 1 | 0% | 2,613 | 2,820 | +8% | 0 | 0 | — |
▸case-20 We store tenant data in a distributed Citus / PostgreSQL database sharded by `tenant_id`. One enterprise tenant accounts for 80% of all queries and data. Should we execute cross-shard aggregations directly without accounting for node distribution? | pass→pass | 19,371 | 20,938 | +8% | 1 | 1 | 0% | 2,425 | 3,136 | +29% | 0 | 0 | — |
▸case-21 Our daily analytics dashboard runs `SELECT COUNT(DISTINCT user_id)` over 500 million daily log rows in Amazon Redshift. It is taking 45 seconds to execute. How can we optimize this count query when exact precision is not strictly mandatory? | fail→fail | 12,697 | 19,061 | +50% | 1 | 1 | 0% | 2,298 | 3,097 | +35% | 0 | 0 | — |
▸case-22 A dashboard runs complex multi-table JOINs and window aggregations over 100 million rows in SQL database on every user page load. Should we keep executing the dynamic SQL query directly on every HTTP request? | fail→fail | 11,654 | 21,238 | +82% | 1 | 1 | 0% | 1,997 | 3,326 | +67% | 0 | 0 | — |