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

53 practiced

Write an SQL query to find customers who placed more than 5 completed orders in the last 30 days. Table: orders(order_id, customer_id, order_date DATE, status). Return customer_id and order_count. Use GROUP BY and HAVING; explain why HAVING is required in this case.

Sample Answer

Approach: Filter orders to the last 30 days and only completed orders, then group by customer_id and use HAVING to keep customers with count > 5.

sql

SELECT
  customer_id,
  COUNT(*) AS order_count
FROM orders
WHERE
  order_date >= CURRENT_DATE - INTERVAL '30' DAY
  AND status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5;

Key points:- WHERE filters rows before aggregation (date range and status) which improves performance by reducing rows to aggregate.- GROUP BY aggregates orders per customer.- HAVING applies a condition to the aggregated result (order_count > 5). You cannot use WHERE for this because WHERE cannot reference aggregate values like COUNT().- Returning customer_id and order_count directly from the aggregation gives the required output.

Edge cases & best practices:- If order_date includes timestamps or different time zones, use appropriate casting or timezone normalization.- If status values vary (e.g., 'Completed', 'complete'), normalize or use case-insensitive comparison.- For large tables, ensure indexes on (order_date) and (status, customer_id) to speed up filtering and grouping.- If you need ties or inclusive boundary (last 30 days including today), confirm business definition of "last 30 days."

EasyTechnical

45 practiced

Write an SQL query to list customers ordered by last_login descending and then by name ascending, ensuring NULL last_login values appear last. Table: customers(customer_id, name, last_login TIMESTAMP). Address SQL dialect differences where NULLS LAST may not be supported.

Sample Answer

Approach: sort by last_login descending but force NULLs to the end, then by name ascending. Some dialects support NULLS LAST directly; others require a workaround (explicit NULL ordering using CASE/IS NULL or COALESCE).

Postgres / Oracle (supports NULLS LAST):

sql

SELECT customer_id, name, last_login
FROM customers
ORDER BY last_login DESC NULLS LAST, name ASC;

MySQL / MariaDB (no NULLS LAST keyword) — use a boolean expression so NULLs sort after non-NULLs:

sql

SELECT customer_id, name, last_login
FROM customers
ORDER BY (last_login IS NULL), last_login DESC, name ASC;

SQL Server (no NULLS LAST) — use CASE to emulate:

sql

SELECT customer_id, name, last_login
FROM customers
ORDER BY CASE WHEN last_login IS NULL THEN 1 ELSE 0 END, last_login DESC, name ASC;

BigQuery — COALESCE can help by mapping NULL to very small value, but for DESC you want NULL last so map to very old date:

sql

SELECT customer_id, name, last_login
FROM customers
ORDER BY COALESCE(last_login, TIMESTAMP '1900-01-01') DESC, name ASC;

Key points:- Ensure the expression that places NULLs last is the first ORDER BY key; then apply last_login DESC so non-NULLs are newest first.- Tie-breaker: name ASC.- Pick the dialect-appropriate pattern above.

EasyTechnical

56 practiced

As a BI analyst building an executive dashboard, write an ANSI SQL query that returns the 50 largest completed orders between '2024-01-01' and '2024-06-30', sorted by amount descending. Table schema: orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2), status VARCHAR). Return columns: order_id, customer_id, order_date, amount. Do not use SELECT * and make the query parameterizable for date window and status.

Sample Answer

Approach: filter by date window and status, select only required columns, sort by amount descending and return the top 50. Use parameter placeholders so the query can be used in BI tools or prepared statements. Use ANSI-standard pagination (FETCH FIRST ... ROWS ONLY).

sql

-- Parameterized ANSI SQL: :start_date, :end_date, :status
SELECT
  order_id,
  customer_id,
  order_date,
  amount
FROM orders
WHERE order_date BETWEEN :start_date AND :end_date
  AND status = :status
ORDER BY amount DESC
FETCH FIRST 50 ROWS ONLY;

Key points:- Uses explicit column list (no SELECT *).- Parameterized for date window and status (bind :start_date, :end_date, :status).- ANSI-compliant pagination via FETCH FIRST 50 ROWS ONLY (preferred over vendor LIMIT).- Ensure :start_date and :end_date are provided as DATE types; if time components exist, adjust with inclusive/exclusive bounds.

EasyTechnical

49 practiced

Write two SQL patterns to find customers who have never placed an order. Tables: customers(customer_id, name) and orders(order_id, customer_id). Provide both a LEFT JOIN ... IS NULL and a NOT EXISTS variant; explain the null semantics and which is safer in presence of NULLs in orders.customer_id.

Sample Answer

Approach: show two common anti-join patterns — LEFT JOIN ... IS NULL and NOT EXISTS — and explain NULL semantics and safety compared to NOT IN.

LEFT JOIN ... IS NULL:

sql

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;  -- no matching order row

NOT EXISTS variant:

sql

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Key points / null semantics:- Both queries return customers for which there is no order row matching on customer_id.- LEFT JOIN ... WHERE o.order_id IS NULL detects the absence of a matching orders row because all columns from o are NULL when no match exists.- NOT EXISTS checks for the absence of any orders row where o.customer_id = c.customer_id; it does not suffer from three-valued-logic surprises.

Safety note:- NOT IN with a subquery can produce incorrect results if the subquery returns NULL values; do NOT use NOT IN here if orders.customer_id can be NULL.- LEFT JOIN ... IS NULL and NOT EXISTS are both safe when orders.customer_id contains NULLs. Between the two, NOT EXISTS is often preferred for clarity and predictable semantics; LEFT JOIN can be more readable in some reporting queries. Consider execution plan and indexing (index on orders.customer_id) for performance in large tables.

MediumTechnical

54 practiced

You must implement a multi-step transformation for a BI dataset involving heavy aggregation and joins. Compare using CTEs (inline) versus temporary tables (materialized) in terms of performance, readability, and ability to add indexes on intermediate results. Provide guidance when to prefer each approach.

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.