Relational Databases and SQL Questions

Focuses on relational database fundamentals and practical SQL skills. Candidates should be able to write and reason about SELECT queries, JOINs, aggregations, grouping, filtering, common table expressions, and window functions. They should understand schema design trade offs including normalization and denormalization, indexing strategies and index types, query performance considerations and basic optimization techniques, how to read an execution plan, and transaction semantics including isolation levels and ACID guarantees. Interviewers may test writing efficient queries, designing normalized schemas for given requirements, suggesting appropriate indexes, and explaining how to diagnose and improve slow queries.

EasyTechnical

67 practiced

You are a data scientist joining an analytics team. Write a SQL query (ANSI SQL) to return the top 5 customers by total revenue in the last 12 months. Tables:

1) orders(order_id, customer_id, order_date, total_amount)2) customers(customer_id, name, signup_date)

Requirements:- Only consider orders with order_date within the last 12 months from today.- If two customers tie, order by customer_id ascending.- Return columns: customer_id, name, total_revenue.

Assume order_date is a DATE column.

Sample Answer

Approach: filter orders to last 12 months, aggregate revenue per customer, join customer names, sort by revenue desc then customer_id asc, limit 5.

SQL:

sql

SELECT c.customer_id,
       c.name,
       SUM(o.total_amount) AS total_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY c.customer_id, c.name
ORDER BY total_revenue DESC, c.customer_id ASC
LIMIT 5;

Key points: uses ANSI INTERVAL for portability, aggregates before limiting to ensure correct top-N by revenue. Ties broken by customer_id ascending per requirement. Edge cases: customers with no orders are excluded; if you want include zero-revenue customers use LEFT JOIN and COALESCE.

MediumTechnical

81 practiced

Create a single SQL query that identifies 'churned' users: users who were active (had events) in month N-2 and N-1 but had zero events in month N. Tables: events(user_id, event_date DATE). Return user_id and last_active_month. Discuss how data delays and late-arriving events affect this metric and strategies to mitigate false churn signals.

Sample Answer

Goal: find users active in months N-2 and N-1 but zero events in month N. Single-query (Postgres syntax):

sql

WITH months AS (
  SELECT
    user_id,
    DATE_TRUNC('month', event_date) AS month_start
  FROM events
  WHERE event_date >= (DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months')
), counts AS (
  SELECT user_id, month_start, COUNT(*) AS events
  FROM months
  GROUP BY user_id, month_start
)
SELECT c1.user_id AS user_id, TO_CHAR(c2.month_start,'YYYY-MM') AS last_active_month
FROM counts c1
JOIN counts c2 ON c1.user_id = c2.user_id
LEFT JOIN counts c3 ON c1.user_id = c3.user_id AND c3.month_start = DATE_TRUNC('month', CURRENT_DATE)
WHERE c1.month_start = DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '2 months'
  AND c2.month_start = DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
  AND (c3.events IS NULL OR c3.events = 0);

Data delays: late-arriving events can falsely label churn. Mitigation: apply a grace window (compute churn for month N only after X days past month end), use event watermarking, or include CDC to backfill late events and recompute churn periodically. Also report churn with confidence intervals or a "provisional" flag until the window closes.

EasyTechnical

82 practiced

Write a SQL query to pivot monthly revenue per product into columns for Jan, Feb, Mar (assume table revenue(product_id, month INT 1-12, revenue)). Use conditional aggregation so the result has columns product_id, revenue_jan, revenue_feb, revenue_mar. Explain scalability concerns for pivoting many months.

Sample Answer

Approach: use conditional aggregation with SUM(CASE WHEN month = X THEN revenue ELSE 0 END) grouped by product_id.

SQL:

sql

SELECT
  product_id,
  SUM(CASE WHEN month = 1 THEN revenue ELSE 0 END) AS revenue_jan,
  SUM(CASE WHEN month = 2 THEN revenue ELSE 0 END) AS revenue_feb,
  SUM(CASE WHEN month = 3 THEN revenue ELSE 0 END) AS revenue_mar
FROM revenue
GROUP BY product_id;

Key points: handles missing months (returns 0); use COALESCE if you prefer NULL for missing data.

Scalability concerns: conditional aggregation scales poorly when pivoting many months (dozens/years) because SQL text grows and optimizer may produce wide rows. Large number of columns increases memory usage and network I/O. Alternatives: use dynamic SQL to generate columns, store month as row (keep narrow schema) and pivot at analytics layer (Pandas/BI tool), or pre-aggregate into monthly wide tables incrementally. For many months, prefer time-series storage or columnar/OLAP engine with native pivoting for performance.

EasyTechnical

72 practiced

Explain ACID properties (Atomicity, Consistency, Isolation, Durability). For each property, give one concrete example of why it matters when building features for a recommendation system that updates user-item interactions in the OLTP database.

MediumTechnical

82 practiced

You run EXPLAIN ANALYZE on a slow query and see a sequential scan on the orders table (10M rows) despite having an index on order_date. List possible reasons the planner chose a seq scan and propose 4 actionable steps to diagnose and improve performance.

Unlock Full Question Bank

Get access to hundreds of Relational Databases and SQL interview questions and detailed answers.

Join thousands of developers preparing for their dream job.