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

50 practiced

Given a page_views table:

page_views( view_id SERIAL PRIMARY KEY, user_id INT, page_url TEXT, viewed_at TIMESTAMP)

Write a SQL query (PostgreSQL) to list the distinct pages visited by user_id = 123 in the last 7 days, sorted alphabetically. Return only the page_url column and limit results to 100. Explain why DISTINCT and ORDER BY together may be costly on large tables.

Sample Answer

To get distinct pages visited by user 123 in the last 7 days, sorted alphabetically and limited to 100:

sql

SELECT DISTINCT page_url
FROM page_views
WHERE user_id = 123
  AND viewed_at >= now() - interval '7 days'
ORDER BY page_url
LIMIT 100;

Why DISTINCT + ORDER BY can be costly on large tables:- DISTINCT must eliminate duplicates across all matching rows, and ORDER BY requires sorting the result set. Together they often force scanning many rows, producing a large intermediate set that must be de-duplicated and sorted (CPU and memory heavy; may spill to disk).- If no useful index exists, the database will perform a full (or large) scan + sort. You can speed this up by adding an index that matches the filter and ordering, for example: CREATE INDEX ON page_views (user_id, viewed_at DESC, page_url); or a covering index: (user_id, viewed_at, page_url) to allow index-only scans that avoid an expensive sort/dedup.- Alternative: GROUP BY page_url can be equivalent, but has similar costs. For repeated queries, consider a materialized view or pre-aggregated table. Edge cases: page_url NULLs (DISTINCT treats NULL as equal), timezone considerations for viewed_at.

MediumTechnical

48 practiced

You have a raw_import_users table with duplicates. Schema: raw_import_users(id, user_id, email, updated_at). Write a SQL query to deduplicate by user_id, keeping the row with the latest updated_at. Use a window function to assign row numbers and return only the latest rows per user_id.

Sample Answer

Approach: use ROW_NUMBER() partitioned by user_id ordered by updated_at DESC to assign 1 to the latest row per user_id, then filter where rn = 1. This is efficient, clear, and preserves ties deterministically if you add a tiebreaker.

sql

-- Return deduplicated rows: one latest row per user_id
SELECT id, user_id, email, updated_at
FROM (
  SELECT
    id,
    user_id,
    email,
    updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY updated_at DESC, id DESC  -- tie-breaker: newest id if updated_at equal
    ) AS rn
  FROM raw_import_users
) t
WHERE rn = 1;

Key points:- ROW_NUMBER() assigns a unique rank per user_id; rn = 1 is the latest by updated_at.- The ORDER BY includes a deterministic tie-breaker (id) so results are stable when updated_at duplicates exist.- This returns all columns you need; wrap in CREATE TABLE AS or use INSERT ... SELECT to persist deduplicated data.

Edge cases:- If you need to keep a specific email when updated_at ties occur, change the ORDER BY accordingly.- Very large tables: ensure proper maintenance (indexes, sufficient temp space) as window functions may use sorting.- If you prefer to keep all duplicates with the latest timestamp (when multiple rows share same updated_at), use RANK() instead of ROW_NUMBER().

Alternative (deleting duplicates in-place, PostgreSQL example):

sql

DELETE FROM raw_import_users r
USING (
  SELECT id
  FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC, id DESC) rn
    FROM raw_import_users
  ) x
  WHERE x.rn > 1
) dup
WHERE r.id = dup.id;

MediumTechnical

54 practiced

A report joins a large orders table to several dimension tables but runs slowly. The original query filters only on order_date. Show 2 concrete SQL refactorings to push down filters or pre-aggregate data and reduce join volume. Explain why pushing predicates or pre-aggregating before joining helps performance.

Sample Answer

Approach: reduce rows early — either filter the large orders table before joining (predicate pushdown) or pre-aggregate/order-rollup before joining so joins operate on much smaller intermediate results.

Refactor 1 — push filter into a CTE and join reduced set:

sql

WITH filtered_orders AS (
  SELECT *
  FROM orders
  WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'  -- original predicate
)
SELECT fo.order_id, c.customer_name, p.product_name, fo.total_amount
FROM filtered_orders fo
JOIN customers c ON fo.customer_id = c.customer_id
JOIN products p  ON fo.product_id  = p.product_id;

Refactor 2 — pre-aggregate orders to reduce join volume (e.g., daily totals per customer/product):

sql

WITH agg_orders AS (
  SELECT customer_id, product_id, order_date,
         SUM(quantity)   AS qty,
         SUM(total_amount) AS total
  FROM orders
  WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'
  GROUP BY customer_id, product_id, order_date
)
SELECT a.order_date, c.customer_name, p.product_name, a.qty, a.total
FROM agg_orders a
JOIN customers c ON a.customer_id = c.customer_id
JOIN products p  ON a.product_id  = p.product_id;

Why this helps:- Fewer rows: applying predicates or aggregation before joins reduces I/O and memory footprint for join operations, which is often the bottleneck on large tables.- Better use of indexes: filtering on orders lets the planner use date/index scans rather than massive full-table joins.- Less network/shuffle in distributed engines: smaller intermediates reduce data movement and sort/hash costs.Edge notes: ensure predicates are sargable, statistics are up-to-date, and consider materialized views or partition pruning for repeated reports.

MediumTechnical

38 practiced

A CTE is used multiple times in a query and seems to be recomputed by the planner. Explain differences between inline CTEs and materialized CTEs, and describe approaches (temp tables, materialized views, explicit materialization hints) you might use when a heavy CTE is reused several times in a reporting query.

Sample Answer

Inline vs materialized CTEs:- Inline CTEs (the default in many engines) are treated like query macros: the planner substitutes the CTE into the outer query and may re-evaluate it each time it's referenced, enabling further optimization (predicate pushdown, join reordering) but causing repeated work if the CTE is expensive and used multiple times.- Materialized CTEs force the engine to compute and store the CTE result once and reuse it for subsequent references, avoiding recomputation at the cost of memory/temp-storage and losing some cross-query optimizations.

Approaches when a heavy CTE is reused multiple times:

1) Explicit materialized CTE (engine-specific)- PostgreSQL example:

sql

WITH heavy AS MATERIALIZED (
  SELECT ... -- expensive aggregation
)
SELECT h.col, count(*) FROM heavy h JOIN ...;
SELECT sum(x) FROM heavy;

- Pros: simple, computed once. Cons: may block some optimizations and result can be stale during long transactions.

2) Temporary table- Create once, index as needed, drop after use:

sql

CREATE TEMP TABLE tmp_heavy AS
SELECT ...;
CREATE INDEX ON tmp_heavy(col);
-- run multiple reporting queries
DROP TABLE tmp_heavy;

- Pros: control over indexing, stats, reuse across multiple queries/sessions (session-scoped), predictable performance. Cons: extra DDL, IO cost to write/read, needs cleanup.

3) Materialized view (persistent)- Good for repeated reports with acceptable staleness; supports REFRESH (CONCURRENTLY in Postgres).

sql

CREATE MATERIALIZED VIEW mv_heavy AS SELECT ...;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_heavy;

- Pros: persisted, can be indexed. Cons: maintenance, storage, possible staleness.

4) Planner hints / temporary caching (engine-specific)- Some DBs allow hints or session settings to force materialization or control optimizer behavior (e.g., Oracle, SQL Server hints). Use carefully and document.

Decision factors / trade-offs:- Freshness vs cost: temp tables/materialized views introduce staleness unless refreshed.- Query optimization: inline CTEs let optimizer do more but can repeat work; materializing prevents certain optimizations.- Resource impact: materialization uses disk/temps and may require indexes for joins; large intermediate results can affect concurrency.- Complexity & maintenance: temp tables add procedural steps; materialized views require refresh policies.

Recommendation:- For ad-hoc reporting run once: create a TEMP TABLE, index it if reused in heavy joins, drop after.- For repeated scheduled reports: use a MATERIALIZED VIEW with a refresh schedule and proper indexes.- For quick fixes inside a single multi-step query: use WITH ... MATERIALIZED (or engine hint) to force single computation, but test plans to ensure acceptable performance and correctness.

EasyTechnical

78 practiced

Explain the difference between WHERE and HAVING in SQL. Then write a PostgreSQL query using the products table below that finds product_category and average(price) per category, returning only categories whose average price is greater than 100.

products(product_id INT, product_name TEXT, product_category TEXT, price NUMERIC)

Show why HAVING is necessary in this example.

Sample Answer

WHERE filters individual rows before any grouping/aggregation; HAVING filters the grouped results after aggregation. Use WHERE to remove rows (e.g., exclude NULLs or a category), and HAVING to apply conditions on aggregate values (SUM, AVG, COUNT).

Correct PostgreSQL query to get product_category and average price per category where average(price) > 100:

sql

SELECT
  product_category,
  AVG(price) AS avg_price
FROM products
GROUP BY product_category
HAVING AVG(price) > 100;

Why HAVING is necessary:- AVG(price) is an aggregate computed per group. WHERE cannot reference aggregates because it runs before GROUP BY.- If you try WHERE AVG(price) > 100 you'll get an error. HAVING runs after grouping, so it can filter groups by their aggregate values.- You can combine WHERE and HAVING: use WHERE to pre-filter rows (e.g., WHERE price IS NOT NULL) and HAVING for aggregate-based filters.

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.