▸case-05 Write an Apache Airflow DAG in Python that extracts JSON logs from an S3 bucket daily, transforms the records, and loads them into Snowflake using the SnowflakeHook. | pass→fail | 21,714 | 26,254 | +21% | 1 | 1 | 0% | 3,929 | 5,516 | +40% | 0 | 0 | — |
▸case-01 I need a complete database design for a multi-tenant SaaS platform running on PostgreSQL. Please provide the DDL creation scripts for all core entities, a text-based ASCII entity relationship diagram, and corresponding up/down migration scripts formatted into clear labeled sections. | fail→fail | 26,528 | 47,322 | +78% | 1 | 1 | 0% | 6,192 | 6,578 | +6% | 0 | 0 | — |
▸case-02 Can you help me structure a MongoDB database for a subscription video streaming service? I need collection schema definitions, index specifications for fast lookup, a text-format ERD showing relationships between documents, and reversible migration templates. | pass→pass | 27,906 | 29,128 | +4% | 1 | 1 | 0% | 5,312 | 6,570 | +24% | 0 | 0 | — |
▸case-03 Here is a slow SQL query with multiple JOINs and window functions running against our production PostgreSQL database: `SELECT u.id, COUNT(o.id) OVER (PARTITION BY u.region_id) FROM users u JOIN orders o ON u.id = o.user_id WHERE o.created_at > NOW() - INTERVAL '30 days'`. Rewrite this query to improve execution performance without changing the underlying database tables or indexes. | pass→pass | 27,947 | 31,419 | +12% | 1 | 1 | 0% | 5,375 | 6,279 | +17% | 0 | 0 | — |
▸case-04 Write a Terraform HCL script to provision an AWS RDS PostgreSQL database instance configured for multi-AZ deployment with automatic backups and storage encryption enabled. | pass→fail | 14,794 | 29,096 | +97% | 1 | 1 | 0% | 3,071 | 6,228 | +103% | 0 | 0 | — |
▸case-06 I am setting up a PostgreSQL database for a retail store catalog. Should I store product attributes with unpredictable key-value pairs using EAV (Entity-Attribute-Value) tables, JSONB columns, or XML? Provide the full PostgreSQL DDL schema with primary keys, foreign keys, and indexes. | pass→pass | 17,808 | 31,619 | +78% | 1 | 1 | 0% | 3,372 | 6,421 | +90% | 0 | 0 | — |
▸case-07 We are building an IoT telemetry platform tracking millions of temperature readings per minute. We plan to create a separate SQL table for every single sensor device ID to handle high throughput. Is this table-per-device pattern optimal in PostgreSQL, or should we use a single hypertable partitioned by time and sensor ID? Provide the schema creation script and down migration script. | pass→pass | 19,160 | 28,289 | +48% | 1 | 1 | 0% | 3,124 | 5,672 | +82% | 0 | 0 | — |
▸case-08 Design a MySQL schema for user accounts, session tokens, and password reset requests. I am considering storing raw plaintext password reset tokens directly in a VARCHAR column to make lookups simple. Provide the CREATE TABLE DDL and down migration rollback SQL. | fail→fail | 18,586 | 24,954 | +34% | 1 | 1 | 0% | 2,739 | 5,321 | +94% | 0 | 0 | — |
▸case-09 For a MongoDB database powering an online shop, should line items for an order be stored as separate documents in a line_items collection with order_id references, or embedded inside the orders document array? Provide the collection schema definition and index definitions. | pass→pass | 17,446 | 19,408 | +11% | 1 | 1 | 0% | 3,254 | 3,877 | +19% | 0 | 0 | — |
▸case-10 We are designing a PostgreSQL schema for a news blog with threaded comments. A teammate suggested running aggregate query counts on the comments table every time an article page is rendered to display comment counts. Show the optimized schema with a counter cache or summary table, along with up and down migration scripts. | pass→pass | 18,482 | 29,037 | +57% | 1 | 1 | 0% | 3,303 | 6,205 | +88% | 0 | 0 | — |
▸case-11 In our MySQL banking database schema for financial ledger entries, should we store transaction amounts using FLOAT or DOUBLE for maximum speed, or DECIMAL(15,2)? Provide the complete CREATE TABLE DDL and migration script with up and down operations. | pass→pass | 16,965 | 26,058 | +54% | 1 | 1 | 0% | 2,946 | 5,576 | +89% | 0 | 0 | — |
▸case-12 We need a PostgreSQL schema for a customer management system that supports soft deletion. Should we duplicate soft-deleted rows into an archived_customers table, or use a deleted_at TIMESTAMP WITH TIME ZONE column with partial indexes? Provide the DDL statements and down migration script. | pass→pass | 15,762 | 26,492 | +68% | 1 | 1 | 0% | 2,976 | 5,982 | +101% | 0 | 0 | — |
▸case-13 In a MongoDB database for an article publishing platform, articles have tags. Should we store tags in a separate join collection article_tags with object IDs, or embed tag strings in an array within the articles document? Provide the collection structure and index definitions. | pass→pass | 13,020 | 29,288 | +125% | 1 | 1 | 0% | 2,244 | 6,012 | +168% | 0 | 0 | — |
▸case-14 We are creating a PostgreSQL relational database with heavily queried child tables like order_items. PostgreSQL automatically creates indexes on primary keys, but does it automatically index foreign key columns? Provide the DDL statements including explicit foreign key index definitions and down migration scripts. | pass→fail | 12,196 | 17,512 | +44% | 1 | 1 | 0% | 2,181 | 3,765 | +73% | 0 | 0 | — |
▸case-15 We need a MySQL table schema for CMS blog posts where URL slugs must be unique per published article. Should we enforce uniqueness only in application code or add a UNIQUE constraint on the slug column? Provide the DDL and migration script. | pass→pass | 19,415 | 21,321 | +10% | 1 | 1 | 0% | 3,135 | 4,635 | +48% | 0 | 0 | — |
▸case-16 In a PostgreSQL order processing schema with status values ('pending', 'processing', 'shipped', 'delivered', 'cancelled'), should we use a SQL native ENUM type or a foreign key lookup table if status values rarely change? Provide the CREATE TYPE and CREATE TABLE DDL, text ERD, and migration script. | fail→fail | 15,820 | 37,943 | +140% | 1 | 1 | 0% | 2,731 | 6,343 | +132% | 0 | 0 | — |
▸case-17 For a MongoDB user management service, should active user session tokens and security logs be stored in the main users document, or separated into distinct collections to avoid exceeding document size limits and high write contention? Provide collection schemas and index specifications. | pass→pass | 15,106 | 23,599 | +56% | 1 | 1 | 0% | 2,789 | 5,046 | +81% | 0 | 0 | — |
▸case-18 We are architecting a multi-tenant application on PostgreSQL. Should we use a single database with a tenant_id column on every table protected by Row Level Security (RLS), or separate PostgreSQL schemas per tenant? Provide the DDL creation script for the single-database RLS approach and rollback scripts. | pass→pass | 16,816 | 29,021 | +73% | 1 | 1 | 0% | 3,064 | 6,588 | +115% | 0 | 0 | — |
▸case-19 We are designing a high-volume MySQL InnoDB database for invoicing. To prevent primary key fragmentation while maintaining unique identity, should we use sequential auto-increment BIGINT primary keys or standard random UUID v4 strings as clustered primary keys? Provide the DDL and migration scripts. | fail→pass | 25,272 | 29,200 | +16% | 1 | 1 | 0% | 4,464 | 6,108 | +37% | 0 | 0 | — |
▸case-20 Design an audit logging system for a PostgreSQL database. Should we add audit columns directly to every business domain table, or create a generic audit_logs table populated via PostgreSQL triggers? Provide the DDL, trigger function definition, and migration script. | pass→pass | 20,366 | 30,382 | +49% | 1 | 1 | 0% | 4,066 | 6,575 | +62% | 0 | 0 | — |
▸case-21 We are building a local delivery app on MongoDB requiring radius searches for nearby drivers. How should geographical coordinates be stored in driver documents, and what index type must be applied? Provide collection document schema, index command, and migration template. | pass→pass | 10,377 | 18,566 | +79% | 1 | 1 | 0% | 2,179 | 4,677 | +115% | 0 | 0 | — |
▸case-22 We are creating a join table user_roles in PostgreSQL connecting users and roles. Should we add a surrogate id SERIAL PRIMARY KEY to user_roles, or use a composite primary key PRIMARY KEY (user_id, role_id)? Provide the complete DDL script and down migration. | pass→pass | 10,145 | 14,089 | +39% | 1 | 1 | 0% | 2,098 | 3,469 | +65% | 0 | 0 | — |