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.

MediumSystem Design

0 practiced

Design a schema and indexing strategy to support geospatial queries: 'find venues within 5 km of a point' for a dataset of 10M venues and 1000 queries/sec. Discuss spatial index types, approximate filters, exact checks, and scaling options.

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.

EasyTechnical

0 practiced

Describe the differences between row-oriented and columnar storage engines. For a data scientist building models and dashboards, when would you choose a row store (e.g., Postgres) vs a columnar store (e.g., Redshift, ClickHouse, Parquet files)? Discuss compression, IO patterns, and update/insert behavior.

HardTechnical

0 practiced

Design an automated missing-index recommendation system that consumes query logs, explain plans, and index usage stats to propose beneficial indexes while avoiding over-indexing. Describe features, heuristics to estimate cost/benefit, and a safe rollout/test strategy.

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.

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.