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

50 practiced

Write a SQL query to find all products with average rating > 4.5 and at least 50 reviews. Use the following schema and apply GROUP BY and HAVING correctly.

Tables:

products(product_id, name)
reviews(review_id, product_id, rating int, created_at timestamp)

Return product_id, name, avg_rating, review_count ordered by avg_rating DESC.

Sample Answer

Approach: aggregate reviews per product using GROUP BY on product_id, compute average rating and count, filter with HAVING for avg > 4.5 and count >= 50, then join to products to get name and order by avg_rating desc.

sql

SELECT
  r.product_id,
  p.name,
  ROUND(AVG(r.rating)::numeric, 2) AS avg_rating,
  COUNT(*) AS review_count
FROM reviews r
JOIN products p ON p.product_id = r.product_id
GROUP BY r.product_id, p.name
HAVING AVG(r.rating) > 4.5
   AND COUNT(*) >= 50
ORDER BY avg_rating DESC;

Key points:- GROUP BY must include product_id and name (or group by product_id and use MIN/ANY_VALUE for name depending on DB).- HAVING filters aggregates after grouping.- I used ROUND(...,2) for readable avg; casting to numeric to control precision in PostgreSQL.- Performance: ensure index on reviews(product_id) (and optionally reviews(product_id, rating)) for faster grouping and counts.

Edge cases:- No reviews -> product omitted (correct).- Null ratings -> AVG ignores NULLs; if rating can be NULL and should be excluded, query is fine; otherwise filter WHERE rating IS NOT NULL.- Large tables -> consider pre-aggregated materialized views or incremental aggregates in pipelines.

HardTechnical

54 practiced

Explain the difference between ROWS BETWEEN and RANGE BETWEEN in window frame clauses. Provide SQL examples where the two produce different results (e.g., duplicate timestamps) and discuss performance implications and when each is appropriate.

Sample Answer

ROWS BETWEEN and RANGE BETWEEN both define the window frame for window functions, but they differ in how they interpret "distance" from the current row.

- ROWS BETWEEN: physical offset by row count. "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW" looks at exactly that many rows relative to the current row, regardless of values.- RANGE BETWEEN: logical offset by value of ORDER BY expression. "RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW" includes all rows whose ORDER BY value falls within that value range. If ORDER BY has duplicates, RANGE can include multiple rows with identical ORDER BY values even if they are outside a fixed row count.

Examples with duplicate timestamps (PostgreSQL):

sql

CREATE TEMP TABLE t(ts timestamptz, val int);
INSERT INTO t VALUES
('2025-01-01 00:00',1),
('2025-01-01 00:00',2),
('2025-01-01 01:00',3),
('2025-01-01 02:00',4);

-- ROWS: last 2 rows including current (physical)
SELECT ts,val,
  sum(val) OVER (ORDER BY ts ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_rows
FROM t
ORDER BY ts, val;

-- RANGE: last 1-hour window by timestamp value
SELECT ts,val,
  sum(val) OVER (ORDER BY ts RANGE BETWEEN '1 hour'::interval PRECEDING AND CURRENT ROW) AS sum_range
FROM t
ORDER BY ts, val;

Behavioral difference:- For the two rows with identical ts='2025-01-01 00:00', ROWS with "1 PRECEDING" computes per physical neighbor; the first row sees only itself, the second sees the first + second. RANGE with 1-hour preceding considers both identical timestamps simultaneously — both rows will include each other because their ORDER BY values are equal, so they see the same aggregated set.

Performance and semantics:- ROWS is deterministic and simple: planner can use row-position based operations, often streaming-friendly and predictable for pagination.- RANGE can be heavier: it requires comparing ORDER BY values and may translate into more complex plans (e.g., grouping equal values), especially with wide frames or non-sargable expressions; some databases (including older PG versions) have limited RANGE support (only numeric/date with simple offsets).- RANGE can produce variable-size frames (many duplicates → large frame) which can increase memory and CPU for aggregations.

When to use which:- Use ROWS when you need a fixed number of preceding/following rows (e.g., rolling N rows), deterministic performance, or order ties matter by physical neighbor.- Use RANGE when you need logical windows based on value distance (time ranges, price ranges) so results align with value intervals; ensure ORDER BY expression and duplicates are understood and that the DB supports efficient RANGE semantics.

Best practice: pick ROWS for stable performance and use RANGE only when semantic correctness requires value-based windows; test with realistic duplicate distributions and inspect query plans.

HardTechnical

43 practiced

Describe approximate distinct count functions available in modern warehouses (e.g., approx_count_distinct, HyperLogLog). Write a SQL example using an approximate function and discuss accuracy, memory, and when to choose approximation over exact count.

Sample Answer

Approximate distinct-count functions trade perfect accuracy for orders-of-magnitude lower memory and faster execution on large datasets. Common algorithms in modern warehouses:

- HyperLogLog (HLL): probabilistic, uses hashed values and leading-zero counts across 2^p registers. Typical error ≈ 1.04 / sqrt(m) where m = 2^p. Memory: tens to a few KB (e.g., p=14 → m=16384 → ~1.04/128 ≈ 0.8% error).- Flajolet–Martin / FM sketch: older bitstream approach, higher variance than HLL.- Theta Sketch (Datasketches library): supports unions/intersections, configurable error bounds similar to HLL; good for sketch composability.- Linear Counting / Bitmap-based: accurate when cardinality is small relative to bitmap; memory grows with expected cardinality.

SQL example (Snowflake/BigQuery/Redshift Spectrum support variants):

sql

-- BigQuery example using approx_count_distinct (HLL-based)
SELECT
  country,
  APPROX_COUNT_DISTINCT(user_id) AS approx_users,
  COUNT(DISTINCT user_id) AS exact_users
FROM analytics.events
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY country;

Accuracy, memory, and performance:- Accuracy: HLL gives configurable relative error; e.g., p=12 → ~1.8% error. Theta sketches provide error guarantees and set-ops.- Memory: HLL typically uses O(2^p) bytes (kilobytes). Exact DISTINCT (hash aggregation) can require memory proportional to unique values (could be GBs).- CPU / I/O: Approx functions avoid heavy shuffles and large in-memory hash tables, reducing job time and resource usage.

When to choose approximation:- Use approximate when cardinality is large (millions+), queries are exploratory/analytics dashboards, SLAs tolerate a small relative error, or when you need fast, repeated aggregations (e.g., daily dashboards).- Use exact when legal/financial accuracy required, small cardinalities, or final reports where even small errors are unacceptable.

Practical tips:- Validate by sampling: compare approx vs exact on smaller partitions to measure actual error.- Choose algorithm based on operations: HLL for single-stream counts/unions, Theta for complex set operations (intersections), exact for low cardinality or regulatory needs.

EasyTechnical

57 practiced

Write a SQL query that returns all customers who have total_spent greater than the average customer spend. Use a subquery to compute the average. Table schema:

customers(customer_id, name)
payments(payment_id, customer_id, amount, paid_at)

Return customer_id, name, total_spent, and ensure correct handling of customers with no payments.

Sample Answer

Approach: compute each customer's total (treating missing payments as 0 with LEFT JOIN + COALESCE), compute the average over all customers using a subquery, then return customers whose total_spent is greater than that average.

sql

-- PostgreSQL: customers with total_spent > average customer spend
SELECT
  c.customer_id,
  c.name,
  cust_tot.total_spent
FROM customers c
JOIN (
  -- per-customer totals, treating no payments as 0
  SELECT
    c.customer_id,
    COALESCE(SUM(p.amount), 0) AS total_spent
  FROM customers c
  LEFT JOIN payments p ON p.customer_id = c.customer_id
  GROUP BY c.customer_id
) AS cust_tot ON cust_tot.customer_id = c.customer_id
WHERE cust_tot.total_spent > (
  -- average across all customers (includes zeros for customers without payments)
  SELECT AVG(total_spent::numeric)
  FROM (
    SELECT COALESCE(SUM(p.amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN payments p ON p.customer_id = c.customer_id
    GROUP BY c.customer_id
  ) s
)
ORDER BY cust_tot.total_spent DESC;

Key points:- Use LEFT JOIN + COALESCE to include customers with no payments (total_spent = 0).- Average is computed over per-customer totals so customers with zero are included.- Casting to numeric in AVG ensures fractional avg in PostgreSQL if totals are integers.

Complexity: single scan of payments joined to customers; roughly O(#customers + #payments) for aggregations.

Edge cases:- NULL or negative amounts (validate or filter in payments if needed).- Very large sums may need numeric/decimal types to avoid overflow.

HardTechnical

43 practiced

A JOIN between orders (1B rows) and products (10K rows) is slow. Describe join strategies (broadcast/hash/nested-loop/merge) and write a SQL hint or configuration example to force or suggest a broadcast join in a distributed engine such as Spark SQL. Explain risks.

Sample Answer

Brief approach: choose a join strategy based on sizes, skew, and cluster resources. For orders (1B rows) × products (10K rows) a broadcast (map-side) join is usually best: broadcast the small products table to all executors so each partition of orders can join locally.

Join strategies:- Broadcast (hash) join: send small table to all workers, build in-memory hash table; fastest for small-side fits in memory; minimal shuffle.- Shuffle hash join: both sides partitioned by join key and one side hashed per partition; requires shuffle of large data.- Sort-merge join: both sides shuffled and sorted by key; good for large datasets and for range partitioning; stable for large-scale joins.- Nested-loop (cartesian) join: worst-case; used for cross-joins or when no join keys; avoid for large data.

Spark SQL: force/suggest broadcast join with hint or config.

SQL hint example:

sql

SELECT /*+ BROADCAST(products) */ o.*, p.*
FROM orders o
JOIN products p
  ON o.product_id = p.id;

Programmatic/DataFrame API:

python

from pyspark.sql.functions import broadcast
df = orders.join(broadcast(products), orders.product_id == products.id)

Configuration knobs:- spark.sql.autoBroadcastJoinThreshold (default ~10MB): increase if products slightly larger- spark.sql.broadcastTimeout: raise if broadcasting large tablesSet example:

python

spark.conf.set("spark.sql.autoBroadcastJoinThreshold", str(50 * 1024 * 1024))  # 50MB
spark.conf.set("spark.sql.broadcastTimeout", "1200")

Risks and mitigations:- OOM on executors if broadcasted table is too large or cluster memory tight — monitor size, increase threshold cautiously.- Skewed keys: broadcasting doesn't help if a few keys in orders explode; consider salting or pre-aggregating.- Network and GC overhead when many large broadcasts; prefer shuffle/sort-merge if small table grows.- Stale statistics: ensure table sizes are accurate; use ANALYZE TABLE or compute stats.

When in doubt: sample cardinalities, test with EXPLAIN and spark UI, and prefer broadcast for small stable lookup tables (10K rows usually safe).

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.