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

41 practiced

Table `events` has columns: event_id INT, user_id INT, event_type TEXT, occurred_at TIMESTAMP.

Write a SQL query that returns one row per user who had an 'purchase' event in the last 30 days: columns user_id and last_purchase_at. Order users by last_purchase_at descending. Use ANSI SQL and assume no DB-specific DISTINCT ON.

Sample Answer

Approach: filter to 'purchase' events in the last 30 days, aggregate per user to get the most recent purchase timestamp, and order descending.

sql

SELECT
  user_id,
  MAX(occurred_at) AS last_purchase_at
FROM events
WHERE event_type = 'purchase'
  AND occurred_at >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY user_id
ORDER BY last_purchase_at DESC;

Key points:- Filtering first reduces data scanned and ensures only purchases in the 30-day window are considered.- MAX(occurred_at) returns each user's most recent purchase in that window.- GROUP BY user_id yields one row per user; ORDER BY sorts by recency.

Edge cases / notes:- If you need date (not timestamp) use CURRENT_DATE and CAST/DATE_TRUNC as appropriate.- Consider timezone consistency for occurred_at and CURRENT_TIMESTAMP.- If you must include users with no purchases, use LEFT JOIN from users and handle NULLs (not required here).

Alternative: use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY occurred_at DESC) and filter rn = 1 when you need more columns from the latest row.

MediumTechnical

44 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.

Sample Answer

Approach summary:- Two SQL patterns: (A) correlated subquery per customer, (B) join + GROUP BY aggregate. Both return customer_id and order_count; performance differs based on table sizes, indexes, and optimizer.

Correlated subquery:

sql

SELECT c.customer_id,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

Join + aggregate:

sql

SELECT c.customer_id,
       COALESCE(o.order_count, 0) AS order_count
FROM customers c
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
) o ON o.customer_id = c.customer_id;

Key points / performance implications:- Correlated subquery conceptually runs the inner COUNT for each customer. Naively that’s O(#customers * cost(count on orders)), which can be very slow for large tables.- Join+aggregate scans orders once, groups by customer_id, then joins — usually much faster (single scan, then hash or sort aggregate). Complexity: O(#orders + #customers) plus grouping cost.- Real DB optimizers often transform correlated subqueries into a join/aggregation or use caching/rewriting, so the correlated form can end up with the same plan as the join form.- Use LEFT JOIN or COALESCE to return zero counts for customers without orders.- Indexes: a covering index on orders(customer_id) helps both methods; join+aggregate benefits most.- When correlated subquery is acceptable: small customers table, orders indexed on customer_id, or when optimizer rewrites it. For large scales prefer explicit aggregation for clarity and predictable performance.- Always inspect EXPLAIN/EXPLAIN ANALYZE to verify actual plan, and consider statistics, parallelism, and memory for grouping.

HardTechnical

38 practiced

You need to join a very large table to a dimension where one key (a 'heavy-hitter') appears in billions of rows, causing severe skew and long-running distributed joins. Propose at least four strategies (SQL-level and architecture-level) to mitigate skew and explain trade-offs.

Sample Answer

Framework: explain the goal (avoid huge skewed shuffle) then list strategies with short how-to and trade-offs.

1) Salting the key (SQL-level)- How: add a salt column to the large table and replicate the dimension with matching salt values, join on (key,salt).- SQL: ALTER table ADD salt = hashmod(id, N); join on (id,salt).- Trade-off: increases data size by factor N, requires deterministic salt and downstream aggregation to remove salt; picks for N need tuning.

2) Broadcast / replicate small dimension (engine-level)- How: if dimension fits memory, force a map-side (broadcast) join so heavy-hitter rows don’t shuffle.- Trade-off: memory pressure on executors; not possible if dimension is large.

3) Isolate heavy-hitters (hybrid partitioning)- How: detect top keys (top-k) and handle them separately: filter big-table WHERE key IN (top-k) → process with specialized partitioning or single-node aggregation; process remaining keys normally.- Trade-off: extra pipeline complexity and coordination; requires up-to-date heavy-hitter list.

4) Pre-aggregate / sketching- How: pre-aggregate or use approximate structures (HyperLogLog, count-min) per key before join so fewer rows for heavy-hitter.- Trade-off: loss of row-level detail or precision; acceptable for analytics but not for exact joins.

5) Repartition and skew-aware scheduling (platform features)- How: use adaptive query execution, skew hints, or custom partitioner to move large partitions to more workers or split partitions dynamically.- Trade-off: depends on engine support (Spark/Presto), more complex tuning.

6) Denormalize / materialize join- How: create a pre-joined/materialized table for frequent queries or store dimension attributes in the fact table during ingestion.- Trade-off: storage cost, data staleness, ETL complexity.

Recommendation: start by broadcasting if possible, otherwise isolate heavy-hitters + salting. For reporting workloads consider pre-aggregation or denormalization. Measure with explain plan and test different N for salting; monitor executor memory and shuffle bytes.

EasyTechnical

44 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.

Sample Answer

Approach: treat NULL and empty (including whitespace-only) as missing. Use TRIM to remove whitespace, then NULLIF to convert empty strings to NULL and filter those rows.

sql

SELECT profile_id,
       user_id
FROM profiles
WHERE NULLIF(TRIM(phone_number), '') IS NULL;

Key points:- TRIM(phone_number) removes leading/trailing spaces so " " becomes "".- NULLIF(..., '') returns NULL when the trimmed string is empty.- Filtering IS NULL captures both original NULLs and empty/whitespace-only values.

Performance: full table scan O(N) — typical for a simple WHERE on a non-indexed text column. If phone_number indexed and you need frequent checks, consider a persisted clean-phone column or a functional index on TRIM(phone_number) (DB-dependent).

Edge cases & alternatives:- If you want to treat strings like "n/a" or "unknown" as missing, extend the WHERE with lower(TRIM(phone_number)) IN (...).- For validating format (digits, country codes), use regex (e.g., phone_number !~ '^[+0-9]{7,}$' in PostgreSQL) to find unusable values beyond just empty/NULL.

MediumTechnical

56 practiced

You are asked to compute average purchase amount per user, but users with no purchases should be treated as having zero for business reporting. Given `users(user_id)` and `purchases(user_id, amount)`, write a query that returns user_id and avg_amount_treated_zero. Explain the difference between treating NULL as zero and excluding NULLs in averages.

Sample Answer

Approach: do a LEFT JOIN from users to purchases and use COALESCE on the aggregate so users with no purchases return 0 instead of NULL.

sql

SELECT
  u.user_id,
  COALESCE(AVG(p.amount), 0) AS avg_amount_treated_zero
FROM users u
LEFT JOIN purchases p
  ON u.user_id = p.user_id
GROUP BY u.user_id;

Why this works:- LEFT JOIN preserves all users; users without purchases produce no matching p rows.- AVG(p.amount) over zero matching rows yields NULL (because AVG ignores absent/NULL values).- COALESCE(..., 0) converts that NULL to 0 for reporting.

Difference between treating NULL as zero vs excluding NULLs:- Excluding NULLs (the default AVG behavior) means you compute the mean only over existing purchase records — a user with no purchases would be omitted or yield NULL.- Treating NULL as zero (COALESCE on the aggregate) explicitly reports those users as having average 0, which matters for business metrics (e.g., lifetime average across all users should include inactive users).- Important subtlety: replacing individual amounts with zero before averaging (AVG(COALESCE(amount,0))) is different — it changes the denominator and will lower averages because it treats NULL purchase rows as explicit zero purchases. For per-user averages with no rows, AVG(COALESCE(...)) still returns NULL; COALESCE must be applied to the aggregate result to get 0 for users with no rows.

Edge considerations & best practices:- If you need to include users who had zero transactions in a given time window, filter purchases in the JOIN condition, not in the WHERE clause (use LEFT JOIN with date condition inside ON) to avoid turning the LEFT JOIN into an inner join.- Decide how to treat zero-amount refunds versus “no purchase” — business definition affects correctness.- If computing company-level average across users, use this per-user result and average it (i.e., AVG(avg_amount_treated_zero)) to ensure inactive users count as zeros.

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.