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.

HardTechnical

0 practiced

Compute the median order amount per customer. Table: orders(order_id, customer_id, total_amount). Show two SQL approaches: (a) percentile_cont(0.5) WITHIN GROUP (Postgres) to compute median per customer, and (b) an approach using window functions to get the median row per group for databases without percentile functions. Discuss performance implications and approximate alternatives.

EasyTechnical

0 practiced

Using orders(order_id, customer_id, order_date), write a query that returns each customer_id and their most recent order_date. Demonstrate two approaches: (a) correlated subquery in SELECT or WHERE, and (b) a derived table / GROUP BY join. Explain pros and cons of each approach.

HardTechnical

0 practiced

Align datasets with differing date grains: you have daily events(events table) and monthly budgets(budgets table with month DATE and region_id). Write a SQL query that aggregates daily events into month granularity and joins against monthly budgets by region and month. Describe how you handle partial-month data (events in a month before budget start) and timezone issues when truncating timestamps to months.

HardTechnical

0 practiced

Cohort retention: Given events(user_id INT, event_name TEXT, occurred_at DATE) where 'signup' events exist, produce a SQL query that computes weekly retention rates for cohorts defined by users' signup week. The output should show cohort_week, week_number (0 = signup week, 1 = week after), cohort_size, retained_users, and retention_rate. Explain any assumptions. (Postgres/ANSI SQL)

MediumTechnical

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

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.