Common Table Expressions and Subqueries Questions

Covers writing and structuring complex SQL queries using Common Table Expressions and subqueries, including when to prefer one approach over another for readability, maintainability, and performance. Candidates should be able to author WITH clauses to break multi step logic into clear stages, implement recursive CTEs for hierarchical data, and use subqueries in SELECT, FROM, and WHERE clauses. This topic also includes understanding correlated versus non correlated subqueries, how subqueries interact with joins and window functions, and practical guidance on choosing CTEs, subqueries, or joins based on clarity and execution characteristics. Interviewers may probe syntax, typical pitfalls, refactoring nested queries into CTEs, testing and validating each step of a CTE pipeline, and trade offs that affect execution plans and index usage.

HardTechnical

0 practiced

A dashboard query uses a CTE to produce intermediate results and then performs several analytic aggregates over that result, but the analytic aggregates are slightly inconsistent with existing reports that used a different SQL approach. Explain how differences in NULL handling, join direction, and aggregation order between CTE-based pipelines and previous queries may produce different results and how to reconcile them.

EasyTechnical

0 practiced

You have a dataset with daily pageviews per article: `pageviews(article_id, view_date, views)`. Using a CTE and a window function, write a query that returns each article's 7-day rolling average views, ordered by article_id and date. Specify you are using PostgreSQL or ANSI SQL. Explain why a CTE may help readability for this pipeline.

MediumTechnical

0 practiced

A query using multiple correlated subqueries in the WHERE clause is running slowly on production. Describe a structured approach to optimize it: how to analyze, possible rewrites (joins, window functions, CTEs), use of indexes, and how to validate that your changes preserve correctness.

EasyTechnical

0 practiced

Explain what a Common Table Expression (CTE) is and how it differs from a subquery (derived table) and a temporary table in the context of building BI dashboards and reports. Include brief SQL fragments showing a `WITH` CTE and a derived table in `FROM`, and discuss when you would prefer a CTE versus a subquery or temp table for readability, maintainability, and stepwise debugging.

MediumTechnical

0 practiced

Given a star schema with `fact_sales(fact_id, product_id, store_id, sale_date, units, revenue)` and dimension tables, outline how you would use CTEs to stage filters and aggregates for a dashboard that shows daily sales and top products per store. Discuss trade-offs between writing everything in one complex query versus multiple staged queries that populate temporary tables for the dashboard refresh.

Unlock Full Question Bank

Get access to hundreds of Common Table Expressions and Subqueries interview questions and detailed answers.

Join thousands of developers preparing for their dream job.