CTEs & Subqueries Questions

Common Table Expressions (CTEs) and subqueries in SQL, including syntax, recursive CTEs, usage patterns, performance implications, and techniques for writing clear, efficient queries. Covers when to use CTEs versus subqueries, refactoring patterns, and potential pitfalls.

MediumTechnical

31 practiced

Using CTEs, compute the earliest date when cumulative sales reach 80% of the total sales. Schema: sales(sale_date DATE, amount DECIMAL). Return the threshold_date and cumulative_percentage at that date. Explain tie-breaking if multiple dates have the same cumulative percentage.

Sample Answer

Approach: aggregate sales by date, compute running cumulative sum and percent of total using window functions in CTEs, then pick the earliest date where cumulative_percentage >= 0.8. If multiple rows share the same cumulative_percentage on different dates, we choose the earliest sale_date (tie-breaking by MIN(date)). If sales occur multiple times per date, aggregate first to ensure date-level granularity.

sql

WITH daily AS (
  -- aggregate to date level
  SELECT
    sale_date::date AS sale_date,
    SUM(amount) AS daily_amount
  FROM sales
  GROUP BY sale_date::date
),
tot AS (
  SELECT SUM(daily_amount) AS total_amount FROM daily
),
running AS (
  SELECT
    sale_date,
    daily_amount,
    SUM(daily_amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount,
    total_amount
  FROM daily CROSS JOIN tot
),
pct AS (
  SELECT
    sale_date,
    cumulative_amount,
    total_amount,
    CASE
      WHEN total_amount = 0 THEN 0
      ELSE cumulative_amount / total_amount::numeric
    END AS cumulative_percentage
  FROM running
)
SELECT
  MIN(sale_date) AS threshold_date,
  ROUND(MIN(cumulative_percentage)::numeric, 4) AS cumulative_percentage
FROM pct
WHERE cumulative_percentage >= 0.8;

Key points:- Aggregation to date ensures correct granularity.- Window SUM ordered by sale_date computes cumulative totals.- We handle total = 0 to avoid division by zero.- Tie-breaking: MIN(sale_date) returns the earliest date meeting the >=80% threshold.Edge cases: empty table (returns no rows), total_amount = 0 (returns 0%), or if you want the exact first row rather than MIN, use ORDER BY sale_date LIMIT 1 on pct WHERE cumulative_percentage >= 0.8.

EasyTechnical

30 practiced

You are asked to explain to a stakeholder when to use a CTE in dashboard queries versus an inline subquery for readability and maintenance. Describe the trade-offs (readability vs performance), and what database-specific considerations you would mention.

Sample Answer

Situation: A stakeholder wants readable, maintainable dashboard SQL but worries about performance.

Recommendation (when to use CTE vs inline subquery):- Use a CTE when it improves readability — e.g., naming intermediate steps (filtered_events, monthly_active_users), breaking complex logic into logical stages, or when the same intermediate result is conceptually reused by multiple final queries. CTEs make reviews and future edits easier for analysts.- Use an inline subquery (or folded logic) when the transformation is trivial or when the DB optimizer can better push predicates into the plan; inline code can sometimes be faster because it gives the planner more scope for optimization.

Trade-offs:- Readability/Maintenance: CTEs win. They act like labeled steps, easing debugging, testing, and documentation.- Performance: Varies. Some engines (Postgres, MySQL) inline non-WITH MATERIALIZED CTEs and optimize them like subqueries; others (older Postgres versions, BigQuery historically, Redshift unless using late-binding or specific hints) may materialize CTEs, causing extra IO and slower performance for large intermediate sets.

Database-specific considerations:- PostgreSQL: Since v12 non-MATERIALIZED CTEs are inlined — good readability without always paying a perf cost. Use EXPLAIN ANALYZE to verify.- BigQuery: CTEs are inlined/optimized, but large repeated subqueries can increase scanned bytes; consider temp tables for heavy reuse.- Redshift: CTEs often materialize — prefer subqueries or temporary tables for large datasets.- Snowflake: optimizer is good at inlining; temporary/staged tables useful for very heavy intermediate work.- SQL Server: generally inlines CTEs; recursive CTEs are supported but beware performance.- MySQL: CTEs supported (8.0+) and usually inlined; check EXPLAIN.

Practical guidance:- Start with CTEs for clarity. Measure with EXPLAIN/EXPLAIN ANALYZE or query profiler. If a CTE step is a hotspot, convert to an inline subquery, add indexes, or persist to a temp/staging table in ETL.- For dashboards: balance maintainability (CTEs) with predictable performance (materialized staging tables for nightly aggregates). Document choices in the query comments so stakeholders know why a pattern was chosen.

HardTechnical

32 practiced

Discuss how CTE usage can interact with query planner statistics and cardinality estimation. Provide an example where misestimated cardinality inside a CTE leads to a bad join order, and explain steps to improve planning (ANALYZE, extended statistics, histograms, rewriting queries).

Sample Answer

CTEs interact with planner statistics and cardinality estimation because the optimizer uses row-count estimates (cardinality) to pick join algorithms and join order. Misestimates—due to outdated stats, column correlation, or insufficient histograms—can make the planner choose a poor plan. Additionally, some DBMSs (e.g., PostgreSQL pre-12) treat CTEs as optimization fences (materialized), forcing the planner to use the CTE’s estimated cardinality independently of surrounding predicates, which can amplify errors.

Example (Postgres-style):

sql

WITH heavy_users AS (
  SELECT user_id, region
  FROM events
  WHERE event_type = 'purchase'  -- selects 0.5% of rows, highly skewed
)
SELECT h.user_id, u.signup_date
FROM heavy_users h
JOIN users u ON u.user_id = h.user_id;

If ANALYZE reports heavy_users as having many rows (due to stale stats or sampling miss), the planner may pick a nested-loop with an index scan on users (good only for small result sets) or a broadcast join in distributed engines, causing huge I/O. If the true cardinality is tiny and clustered, a hash join would be far cheaper.

How to improve planning- ANALYZE / VACUUM ANALYZE: refresh table statistics so estimators reflect current distribution.- Increase statistics_target (or per-column stats) to produce finer histograms for skewed columns.- Extended statistics (Postgres CREATE STATISTICS): capture multi-column correlation (e.g., event_type + region) so combined-selectivity is estimated correctly.- Histograms / frequency lists: ensure frequent-value tracking captures heavy hitters.- Query rewriting: move selective predicates into the outer query, or convert CTE to a subquery (or use NOT MATERIALIZED / MATERIALIZED hints) so the optimizer can inline and re-estimate with outer filters.- Use planner hints sparingly (force_hash_join, join_order) or temporary session vars to test alternative plans.- Validate plans with EXPLAIN (ANALYZE) and compare estimated vs actual rows to find misestimates.

In practice: run EXPLAIN (ANALYZE, BUFFERS), look for large mismatches between "rows" and "actual rows". If correlation causes misestimation, create extended stats; if sampling missed skew, increase stats_target or add a summary table of heavy hitters and join to that. Those steps typically fix CTE-related cardinality problems and yield better join orders and join methods.

EasyTechnical

35 practiced

Using a CTE, compute a 7-day rolling average of daily active users (DAU) from an events table with schema:

sql

events(user_id INT, event_date DATE)

Instructions: first aggregate unique users per date in a CTE, then compute the 7-day rolling average. Explain how you handle missing dates in the range.

Sample Answer

Approach: first aggregate unique users per day in a CTE, generate a complete date series to fill missing dates (so days with 0 DAU are represented), then compute a 7-day rolling average using a window function.

sql

WITH daily AS (
  -- unique users per day
  SELECT
    event_date::date AS dt,
    COUNT(DISTINCT user_id) AS dau
  FROM events
  GROUP BY 1
),
calendar AS (
  -- generate continuous date range between min and max event_date
  SELECT generate_series(
    (SELECT MIN(event_date) FROM events),
    (SELECT MAX(event_date) FROM events),
    INTERVAL '1 day'
  )::date AS dt
),
dau_filled AS (
  -- left join calendar to daily to ensure missing dates appear with 0
  SELECT
    c.dt,
    COALESCE(d.dau, 0) AS dau
  FROM calendar c
  LEFT JOIN daily d USING (dt)
)
SELECT
  dt,
  dau,
  ROUND(
    AVG(dau) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
    ::numeric, 2
  ) AS dau_7day_avg
FROM dau_filled
ORDER BY dt;

Notes:- generate_series ensures missing dates are treated as zero DAU; without it the rolling average would skip days and overstate activity.- Performance: for large tables, pre-aggregate events, ensure index on event_date, and limit calendar bounds rather than full history.- Edge cases: if fewer than 7 prior days exist, AVG will compute over available days — adjust to NULL if you require full 7-day windows by using COUNT(*) filter.

MediumTechnical

30 practiced

Discuss the pitfalls of using SELECT * inside CTEs of long-lived reporting queries. Cover risks like schema drift, accidental inclusion of large columns, and maintainability. Provide concrete recommendations to mitigate these risks.

Sample Answer

Using SELECT * inside CTEs for long-lived reporting queries creates several real risks that are easy to miss in day-to-day analysis.

Risks- Schema drift / contract breakage: When upstream tables add, rename, or remove columns, SELECT * silently changes result shape. Downstream calculations, joins or BI dashboards can break or return incorrect results.- Accidental inclusion of large or sensitive columns: New text/blob/json columns (or PII) get pulled into the CTE, inflating query cost, slowing materialized views, or exposing sensitive data.- Maintainability and readability: It's unclear which columns a report actually depends on, making audits, debugging, and column-level lineage harder.- Ambiguous column ordering/duplicates: Joins with SELECT * can produce duplicate column names or ordering changes that break code expecting a specific schema.

Concrete example of the problem- Suppose report_cte uses SELECT * FROM events. Upstream adds event_payload (large JSON). Suddenly the report’s materialized view grows 10x and refresh time spikes. Or a new column named id conflicts with an existing id from a joined table, causing silent mis-joins.

Recommendations to mitigate1. Explicit column lists: Always SELECT the exact columns needed in every CTE. This is the single best protection. Example: SELECT e.event_id, e.user_id, e.event_type, e.event_time FROM events e;2. Create curated upstream views/schema contracts: Maintain a stable view (e.g., events_v1) that exposes only approved columns; update deliberately when you version.3. Column-level tests and CI: Add automated checks that fail if result column list or datatypes change (compare against expected schema using INFORMATION_SCHEMA).4. Data-size and type guards: Use sampling or queries against pg_column_size/INFORMATION_SCHEMA to alert if new columns are large or types changed.5. Use code comments and documentation: Document which downstream reports rely on the CTE and why each column is selected.6. Avoid SELECT * in production materialized views or scheduled reports; limit it to ad-hoc exploration.7. Monitoring and alerts: Track materialized view sizes, refresh times, and query latency; alert on sudden spikes.8. Defensive casting and null-handling: CAST or COALESCE important columns to expected types/sizes to prevent implicit changes breaking logic.

Trade-offs- Explicit lists require maintenance when you intentionally add needed columns, but that cost is deliberate and safer. Curated views and CI add overhead but protect production reports.

Bottom line: SELECT * speeds exploration but is hazardous for long-lived reporting. Use explicit column contracts, automated checks, and monitoring to keep reports stable, performant, and auditable.

Unlock Full Question Bank

Get access to hundreds of CTEs & Subqueries interview questions and detailed answers.

Join thousands of developers preparing for their dream job.