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

Two partitioned event tables exist: events_2023 and events_2024 with identical schema (event_id, user_id, event_type, occurred_at). Write a query to combine them into a single result set showing event_id, user_id, event_type, occurred_at and remove duplicates. Explain the difference between UNION and UNION ALL and which one you chose.

EasyTechnical

0 practiced

Given products(product_id INT, name TEXT, price NUMERIC, discount_price NUMERIC), write a SQL query (Postgres) that returns product_id, name, and display_price where display_price uses the discounted price when present, otherwise the regular price, and if both are NULL shows 0. Demonstrate using COALESCE and explain NULL-handling pitfalls.

HardTechnical

0 practiced

Implement sessionization (gaps-and-islands): given events(user_id INT, event_ts TIMESTAMP) produce a query that assigns a session_id per user such that events within 30 minutes of the previous event belong to the same session. Return user_id, session_id, session_start, session_end, event_count. Explain key steps (lag, flag, cumulative sum).

HardTechnical

0 practiced

Compute the median order amount per customer. Table: orders(order_id, customer_id, total_amount). Show two SQL approaches: (a) percentile_cont(0.5) WITHIN GROUP (Postgres) to compute median per customer, and (b) an approach using window functions to get the median row per group for databases without percentile functions. Discuss performance implications and approximate alternatives.

MediumTechnical

0 practiced

A report uses a correlated subquery per row and runs slowly:

SELECT u.user_id, u.email, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id AND o.status='completed') AS completed_ordersFROM users u;

Rewrite this to a set-based approach using JOINs/aggregates and explain why that improves performance on large data.

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.