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.

MediumTechnical

0 practiced

You need to find records present in table A but not in table B and vice versa to reconcile two systems. Given `crm_customers(id, email)` and `billing_customers(id, email)`, write a query using FULL OUTER JOIN that lists unmatched rows and explain how NULLs indicate mismatches. Also present an alternative approach using UNION ALL and aggregation.

EasyTechnical

0 practiced

You are building a simple API that pages results. Given `users(user_id, signup_date)`, write a SQL query (ANSI SQL) to fetch page N of size 50 ordered by signup_date descending. Describe briefly why OFFSET-based pagination can be slow on large tables and outline a faster alternative.

MediumTechnical

0 practiced

Refactor the following messy SQL into readable, maintainable SQL using CTEs and comments. Original query:

SELECT u.id,u.name,COUNT(o.id) FROM users u,orders o WHERE u.id=o.user_id AND o.created_at>'2024-01-01' GROUP BY u.id,u.name HAVING COUNT(o.id)>5 ORDER BY 3 DESC;

Provide the refactored query and describe 3 readability improvements you applied.

HardTechnical

0 practiced

Design an efficient SQL approach to sessionize web events. Given `events(user_id, event_ts TIMESTAMP)`, define a session as consecutive events where the gap between events is <= 30 minutes. Return user_id, session_id, session_start, session_end, session_duration_seconds, number_of_events. Provide the SQL (using window functions) and explain how it scales to large datasets.

MediumTechnical

0 practiced

You ran EXPLAIN ANALYZE on a slow query and see the planner estimates are much lower than actual rows for a key join. List steps you would take to diagnose and fix this issue and describe at least three possible causes of misestimation.

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.