SQL Fundamentals and Query Writing Questions

Comprehensive query writing skills from basic to intermediate level. Topics include SELECT and WHERE, joining tables with inner and outer joins, grouping with GROUP BY and filtering groups with HAVING, common aggregation functions such as COUNT SUM AVG MIN and MAX, ORDER BY and DISTINCT, subqueries and common table expressions, basic window functions such as ROW_NUMBER and RANK, union operations, and principles of readable and maintainable query composition. Also covers basic query execution awareness and common performance pitfalls and how to write correct, efficient queries for combining and summarizing relational data.

EasyTechnical

0 practiced

You have `customers(customer_id, name)` and `feedback(customer_id, feedback_text, feedback_date)` where not every customer has feedback.

Write a SQL query to return customer_id, name, and the most recent feedback_date (or NULL if none) for every customer. Use LEFT JOIN and an aggregation approach.

EasyTechnical

0 practiced

Table `profiles(profile_id, user_id, phone_number TEXT, updated_at TIMESTAMP)` may contain NULL or empty-string phone numbers.

Write a SQL query to find profiles that are missing a usable phone number (treat empty string and NULL as missing). Return profile_id and user_id.

HardTechnical

0 practiced

Using LATERAL (or CROSS APPLY) write a query that returns, for each customer, the top 3 most recent orders along with that customer's lifetime average order value. Explain how LATERAL simplifies the expression compared to window-based approaches and discuss performance implications.

MediumTechnical

0 practiced

Given an EXPLAIN output that shows a sequential scan dominating cost and a filter on a specific timestamp range, propose a specific index and show the SQL you would add to create it. Explain how you would validate the index improves the plan and what write-side implications it has.

MediumTechnical

0 practiced

Compare a correlated subquery and a join+aggregate for computing order counts per customer. Given `customers` and `orders`, write both approaches to get customer_id and order_count. Discuss performance implications and when the DB optimizer may transform one into the other.

Unlock Full Question Bank

Get access to hundreds of SQL Fundamentals and Query Writing interview questions and detailed answers.

Join thousands of developers preparing for their dream job.