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

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.

MediumTechnical

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

EasyTechnical

0 practiced

You are building a simple API that pages results. Given `users(user_id, signup_date)`, write a SQL query (ANSI SQL) to fetch page N of size 50 ordered by signup_date descending. Describe briefly why OFFSET-based pagination can be slow on large tables and outline a faster alternative.

HardTechnical

0 practiced

A complex ad-hoc query joining four large tables is running very slowly. As a data scientist, list a step-by-step approach you would take to optimize it. Include at least five actionable steps such as query rewrite, index suggestions, statistics, and materialization techniques.

HardTechnical

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

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.