Database Design and Query Optimization Questions

Principles of database schema design and performance optimization including relational and non relational trade offs, normalization and denormalization, indexing strategies and index types, clustered and non clustered indexes, query execution plans, common table expressions for readable complex queries, detecting missing or redundant indexes, sharding and partitioning strategies, and consistency and availability trade offs. Candidates should demonstrate knowledge of optimizing reads and writes, diagnosing slow queries, and selecting the appropriate database model for scale and consistency requirements.

MediumTechnical

0 practiced

A complex report query uses many CTEs and performs worse than an equivalent flattened query. Explain why CTEs can hurt performance in some databases and describe optimization approaches you would try to improve execution time.

HardTechnical

0 practiced

You inherit a 500GB OLTP database with poor indexing and 5s average query latency. Create a step-by-step audit and remediation plan: what metrics and tools you would collect first, how you would prioritize fixes, and how to rollout changes with minimal disruption.

EasyTechnical

0 practiced

Using Common Table Expressions (CTEs) write a PostgreSQL query to compute month-over-month revenue growth per product. Schemas:

products(product_id BIGINT, name TEXT)order_items(order_item_id BIGINT, order_id BIGINT, product_id BIGINT, price NUMERIC, qty INT, created_at TIMESTAMP)

Return: product_id, product_name, revenue_prev_month, revenue_curr_month, pct_change. Use CTEs to structure the query for readability.

HardSystem Design

0 practiced

Design a multi-tenant metrics ingestion and storage system for thousands of tenants producing time-series metrics. Discuss schema choices (single table with tenant_id vs separate tables per tenant), retention, compression, partitioning, cost, and noisy-neighbor isolation.

MediumTechnical

0 practiced

A table experiences heavy page contention and locks during bursts of inserts (hundreds of thousands per minute). Propose concrete strategies to reduce contention: index design, partitioning, batching, or schema changes. Explain trade-offs and implementation considerations.

Unlock Full Question Bank

Get access to hundreds of Database Design and Query Optimization interview questions and detailed answers.

Join thousands of developers preparing for their dream job.