Relational Databases and SQL Questions

Focuses on relational database fundamentals and practical SQL skills. Candidates should be able to write and reason about SELECT queries, JOINs, aggregations, grouping, filtering, common table expressions, and window functions. They should understand schema design trade offs including normalization and denormalization, indexing strategies and index types, query performance considerations and basic optimization techniques, how to read an execution plan, and transaction semantics including isolation levels and ACID guarantees. Interviewers may test writing efficient queries, designing normalized schemas for given requirements, suggesting appropriate indexes, and explaining how to diagnose and improve slow queries.

HardTechnical

0 practiced

You see an EXPLAIN ANALYZE where the planner chooses Nested Loop joins and a Seq Scan on a large table, and the query runs for minutes. The snippet:

Nested Loop
  -> Index Scan on customers (cost ...)
  -> Seq Scan on orders (cost 1000000)

Describe step-by-step how you'd diagnose and fix the performance problem: statistics, indexes, rewriting the query, materialized intermediates, and parallelism. Be concrete about commands or actions.

MediumTechnical

0 practiced

As a data analyst you need to compute Customer Lifetime Value (CLV). Given tables orders(order_id, customer_id, order_date, amount) and refunds(refund_id, order_id, amount), write a CTE-based SQL query that produces customer_id, total_revenue, total_refunds, net_revenue, and average_order_value. Explain any assumptions you make about matching refunds to orders.

EasyTechnical

0 practiced

Schema:

products(product_id INT, name VARCHAR, price NUMERIC)
orders(order_id INT, product_id INT, qty INT, order_date DATE)

Write a correlated subquery that returns product_id, name, price, and last_order_date (the most recent order_date for that product). Use ANSI SQL and explain when a correlated subquery is less efficient than a JOIN.

EasyTechnical

0 practiced

Given a user_events table:

user_events(event_id INT, user_id INT, event_type VARCHAR, created_at TIMESTAMP)

Write SQL to return the number of unique users per event_type for the last 90 days. Explain the difference between COUNT(*) and COUNT(DISTINCT user_id) and performance considerations for COUNT(DISTINCT).

EasyTechnical

0 practiced

You need to break a complex query into readable steps. Given:

orders(order_id, customer_id, order_date, amount)

Write a Common Table Expression (CTE) that first computes monthly_revenue (month, customer_id, revenue) and then selects the top 5 customers by revenue for the latest month. Use PostgreSQL-compatible SQL and explain when CTEs can harm performance.

Unlock Full Question Bank

Get access to hundreds of Relational Databases and SQL interview questions and detailed answers.

Join thousands of developers preparing for their dream job.