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

0 practiced

Write a readable SQL query using Common Table Expressions (CTEs) to compute monthly cohort retention. Given `events(user_id, event_date)` define first_month as DATE_TRUNC('month', MIN(event_date)) per user, then compute for each cohort month the percentage of users who returned in the next month. Return columns: cohort_month, cohort_size, next_month_retention.

EasyTechnical

0 practiced

Tables: `users(user_id, name, email)` and `orders(order_id, user_id, total_amount, order_date)`.

Write a SQL query to list user name, email and order_id, total_amount for orders where total_amount > 100. Only include users who have placed such orders; do not include users with no qualifying orders.

MediumTechnical

0 practiced

In a heavy ETL query, repeating expensive subqueries is slowing down development. Describe when to use a temporary table (or persisted staging table) versus a CTE. Explain costs, visibility, and performance trade-offs and give a short SQL example where a temp table is preferable.

EasyTechnical

0 practiced

Tables: `products(product_id, name)` and `orders(order_id, product_id)`.

Write two SQL approaches to find products that have never been ordered: (1) using NOT IN or NOT EXISTS, and (2) using LEFT JOIN and WHERE orders.product_id IS NULL. Describe a scenario where NOT EXISTS is preferred over LEFT JOIN (or vice versa).

HardTechnical

0 practiced

Given hierarchical categories in `categories(category_id, parent_id, name)` and sales in `sales(product_id, category_id, revenue)`, write a recursive CTE (ANSI SQL) that aggregates total revenue for each ancestor category (not just immediate parent). Return ancestor_category_id, descendant_category_id, total_revenue.

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.