CTE — 100 Interview Questions (Data Engineer Edition)
Har question ke saath demo table diya gaya hai jitna zaroori ho — koi bhi query bina data dekhe mat samjho.
🎯 Pehle samjho: CTE hai kya (analogy)
Socho tum ek factory assembly line bana rahe ho. Har station pe ek intermediate part banta hai, phir agla station usko use karta hai. CTE (WITH clause) bilkul waisa hi hai — ek temporary, named result set jo query ke andar hi define hota hai aur agli line mein use ho jata hai. Subquery jaisa hi kaam karta hai, but readable, reusable (within the same query), aur recursive bhi ho sakta hai — jo subquery nahi kar sakta.
WITH station1 AS (
SELECT ... FROM raw_table WHERE ...
)
SELECT * FROM station1 WHERE ...;
📦 DEMO TABLES (used across most questions)
-- Table 1: Employees
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
manager_id INT,
dept_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Ravi', NULL, 10, 90000, '2018-01-15'),
(2, 'Anita', 1, 10, 75000, '2019-03-10'),
(3, 'Suresh', 1, 20, 72000, '2019-07-20'),
(4, 'Priya', 2, 10, 60000, '2020-01-05'),
(5, 'Kiran', 2, 10, 62000, '2020-06-15'),
(6, 'Meena', 3, 20, 58000, '2021-02-11'),
(7, 'Arjun', 3, 20, 61000, '2021-09-01'),
(8, 'Divya', 5, 10, 50000, '2022-03-19');
-- Table 2: Departments
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'HR');
-- Table 3: Orders (for data-engineering style aggregation questions)
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(10,2),
status VARCHAR(20)
);
INSERT INTO orders VALUES
(101, 1, '2024-01-05', 500, 'COMPLETED'),
(102, 1, '2024-01-20', 300, 'COMPLETED'),
(103, 2, '2024-01-10', 700, 'CANCELLED'),
(104, 2, '2024-02-01', 200, 'COMPLETED'),
(105, 3, '2024-02-15', 1000, 'COMPLETED'),
(106, 3, '2024-03-01', 450, 'COMPLETED'),
(107, 1, '2024-03-10', 150, 'COMPLETED');
-- Table 4: Product Events (log-style table, for pipeline dedup / gaps-islands questions)
CREATE TABLE product_events (
event_id INT PRIMARY KEY,
product_id INT,
event_type VARCHAR(20),
event_time TIMESTAMP
);
INSERT INTO product_events VALUES
(1, 100, 'VIEW', '2024-05-01 10:00:00'),
(2, 100, 'VIEW', '2024-05-01 10:00:00'), -- duplicate row
(3, 100, 'CLICK', '2024-05-01 10:05:00'),
(4, 100, 'BUY', '2024-05-01 10:10:00'),
(5, 200, 'VIEW', '2024-05-01 11:00:00'),
(6, 200, 'CLICK', '2024-05-01 11:02:00');
SECTION A — CTE Fundamentals (Q1–15)
Q1. Simple CTE se saare employees dikhao jinki salary 65000 se zyada hai.
WITH high_earners AS (
SELECT emp_id, emp_name, salary FROM employees WHERE salary > 65000
)
SELECT * FROM high_earners;
Q2. CTE vs Subquery — dono se same output nikaalo (dept-wise avg salary > overall avg).
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_sal > (SELECT AVG(salary) FROM employees);
Interview point: CTE readability better hai jab query multi-step ho; subquery deeply nested ho jaati hai.
Q3. CTE ko ek se zyada baar reference karo (same query mein).
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
)
SELECT e.emp_name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;
Q4. Non-recursive CTE ka syntax explain karo aur ek dummy example do (no table needed — conceptual).
Q5. CTE ki scope kya hoti hai? (query khatam hote hi CTE gayab ho jaata hai — table create nahi hota) — Conceptual, demo table optional.
Q6. Multiple CTEs ek saath chain karo — dept avg nikaalo, phir usse top dept find karo.
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
),
top_dept AS (
SELECT dept_id FROM dept_avg ORDER BY avg_sal DESC LIMIT 1
)
SELECT * FROM employees WHERE dept_id = (SELECT dept_id FROM top_dept);
Q7. CTE ke andar CTE (nested reference) — 3 stage pipeline banao (filter → aggregate → rank).
WITH filtered AS (
SELECT * FROM employees WHERE hire_date >= '2019-01-01'
),
aggregated AS (
SELECT dept_id, COUNT(*) AS cnt FROM filtered GROUP BY dept_id
)
SELECT * FROM aggregated ORDER BY cnt DESC;
Q8. orders table se monthly revenue CTE banao.
WITH monthly_revenue AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders WHERE status = 'COMPLETED'
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT * FROM monthly_revenue ORDER BY month;
Q9. CTE mein WHERE vs bahar WHERE — dono ka fark dikhao (filter before vs after aggregation).
Q10. CTE ka use karke duplicate rows detect karo (product_events table).
WITH dup_check AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY product_id, event_type, event_time ORDER BY event_id
) AS rn
FROM product_events
)
SELECT * FROM dup_check WHERE rn > 1;
Q11. Above se duplicates delete karo (data engineering must-know).
WITH dup_check AS (
SELECT event_id, ROW_NUMBER() OVER (
PARTITION BY product_id, event_type, event_time ORDER BY event_id
) AS rn
FROM product_events
)
DELETE FROM product_events
WHERE event_id IN (SELECT event_id FROM dup_check WHERE rn > 1);
Q12. CTE ke saath INSERT INTO ... SELECT combine karo (staging → target table load pattern).
WITH cleaned AS (
SELECT DISTINCT product_id, event_type, event_time FROM product_events
)
INSERT INTO product_events_clean
SELECT * FROM cleaned;
Q13. CTE column alias explicitly define karo.
WITH dept_summary (dept_id, total_emp) AS (
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id
)
SELECT * FROM dept_summary;
Q14. CTE mein JOIN use karo — employee + department name.
WITH emp_dept AS (
SELECT e.emp_name, d.dept_name, e.salary
FROM employees e JOIN departments d ON e.dept_id = d.dept_id
)
SELECT * FROM emp_dept ORDER BY salary DESC;
Q15. CTE performance ka trade-off explain karo — materialize hota hai ya not (DB dependent: Postgres <12 always materialized, 12+ inlines by default; SQL Server/Oracle mostly inline/optimizer-decided).
SECTION B — CTE with Window Functions (Q16–35)
Q16. Har department ka top earner nikaalo RANK() + CTE se.
WITH ranked AS (
SELECT *, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT * FROM ranked WHERE rnk = 1;
Q17. Nth highest salary (N=2) CTE se.
WITH ranked AS (
SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT * FROM ranked WHERE rnk = 2;
Q18. Dept-wise Nth highest salary.
WITH ranked AS (
SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT * FROM ranked WHERE rnk = 2;
Q19. Running total of salary (order by hire_date).
WITH running AS (
SELECT emp_name, hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees
)
SELECT * FROM running;
Q20. Moving average (last 2 orders) per customer.
WITH movavg AS (
SELECT customer_id, order_date, amount,
AVG(amount) OVER (
PARTITION BY customer_id ORDER BY order_date
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM orders
)
SELECT * FROM movavg;
Q21. LAG() se pichle order ka amount compare karo.
WITH lag_amt AS (
SELECT customer_id, order_date, amount,
LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount
FROM orders
)
SELECT *, amount - prev_amount AS diff FROM lag_amt;
Q22. LEAD() se agla order date nikaalo, phir gap days calculate karo.
WITH lead_date AS (
SELECT customer_id, order_date,
LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_date
FROM orders
)
SELECT *, next_date - order_date AS gap_days FROM lead_date;
Q23. Cumulative distinct customers count per month (COUNT(DISTINCT) window trick via CTE + subquery).
Q24. NTILE(4) se employees ko 4 salary buckets mein baato.
WITH buckets AS (
SELECT emp_name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees
)
SELECT * FROM buckets;
Q25. Percentage of total salary contributed by each employee within dept.
WITH dept_total AS (
SELECT dept_id, SUM(salary) AS total_sal FROM employees GROUP BY dept_id
)
SELECT e.emp_name, e.dept_id, e.salary,
ROUND(e.salary * 100.0 / d.total_sal, 2) AS pct_of_dept
FROM employees e JOIN dept_total d ON e.dept_id = d.dept_id;
Q26. First and last hired employee per dept (FIRST_VALUE/LAST_VALUE).
WITH fv AS (
SELECT dept_id, emp_name, hire_date,
FIRST_VALUE(emp_name) OVER (PARTITION BY dept_id ORDER BY hire_date) AS first_hired,
LAST_VALUE(emp_name) OVER (
PARTITION BY dept_id ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_hired
FROM employees
)
SELECT DISTINCT dept_id, first_hired, last_hired FROM fv;
Q27. Salary gap between consecutive ranked employees (dept-wise).
Q28. Median salary per dept using CTE + PERCENTILE_CONT.
WITH med AS (
SELECT dept_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept_id) AS median_sal
FROM employees
)
SELECT DISTINCT dept_id, median_sal FROM med;
Q29. Employees earning above their manager's salary (self-join + CTE).
WITH mgr_sal AS (
SELECT emp_id, salary AS mgr_salary FROM employees
)
SELECT e.emp_name, e.salary, m.mgr_salary
FROM employees e
JOIN mgr_sal m ON e.manager_id = m.emp_id
WHERE e.salary > m.mgr_salary;
Q30. Second most recent order per customer.
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn = 2;
Q31–35. (Practice variants — same pattern, different metric): top-3 orders per customer (rn<=3); dept with highest salary variance (STDDEV + CTE); % month-over-month revenue growth (LAG on monthly CTE from Q8); employees hired in the same year as their manager; rank departments by total headcount.
SECTION C — Recursive CTE (Q36–55)
Analogy: Recursive CTE ek loop jaisa hai jo apna hi output next iteration ka input bana leta hai, jab tak stop condition (anchor se disconnect) na aa jaaye. Do parts hote hain: anchor member (starting point) + recursive member (jo khud ko call karta hai) UNION ALL se joined.
WITH RECURSIVE cte_name AS (
-- Anchor
SELECT ...
UNION ALL
-- Recursive part (references cte_name itself)
SELECT ... FROM table JOIN cte_name ON ...
)
SELECT * FROM cte_name;
Q36. Employee-Manager hierarchy — Ravi (CEO) se top-down poora org chart print karo.
WITH RECURSIVE org_chart AS (
SELECT emp_id, emp_name, manager_id, 1 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT * FROM org_chart ORDER BY level;
Q37. Same hierarchy mein indentation ke saath naam print karo (visual tree).
WITH RECURSIVE org_chart AS (
SELECT emp_id, emp_name, manager_id, 1 AS level, emp_name::TEXT AS path
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name, e.manager_id, oc.level + 1, oc.path || ' -> ' || e.emp_name
FROM employees e JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT REPEAT(' ', level - 1) || emp_name AS org_tree FROM org_chart ORDER BY path;
Q38. Ek specific employee (Meena, id=6) ke saare managers (bottom-up chain) nikaalo.
WITH RECURSIVE manager_chain AS (
SELECT emp_id, emp_name, manager_id FROM employees WHERE emp_id = 6
UNION ALL
SELECT e.emp_id, e.emp_name, e.manager_id
FROM employees e JOIN manager_chain mc ON e.emp_id = mc.manager_id
)
SELECT * FROM manager_chain;
Q39. Har employee ke direct + indirect reports ki total count nikaalo.
WITH RECURSIVE reports AS (
SELECT emp_id AS root_id, emp_id, manager_id FROM employees
UNION ALL
SELECT r.root_id, e.emp_id, e.manager_id
FROM employees e JOIN reports r ON e.manager_id = r.emp_id
)
SELECT root_id, COUNT(*) - 1 AS total_reports
FROM reports GROUP BY root_id;
Q40. Generate a number series 1 to 10 (classic recursive CTE — no table needed).
WITH RECURSIVE nums AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;
Q41. Generate a date series for Jan 2024 (calendar dimension — bahut common data-engineering pattern).
WITH RECURSIVE date_series AS (
SELECT DATE '2024-01-01' AS dt
UNION ALL
SELECT dt + INTERVAL '1 day' FROM date_series WHERE dt < DATE '2024-01-31'
)
SELECT * FROM date_series;
Q42. Fibonacci series first 10 numbers using recursive CTE (no table).
WITH RECURSIVE fib AS (
SELECT 0 AS a, 1 AS b
UNION ALL
SELECT b, a + b FROM fib WHERE b < 100
)
SELECT a FROM fib;
Q43. Infinite loop se bachne ka tarika — MAXRECURSION/depth guard kaise lagayein.
WITH RECURSIVE org_chart AS (
SELECT emp_id, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.manager_id, oc.level + 1
FROM employees e JOIN org_chart oc ON e.manager_id = oc.emp_id
WHERE oc.level < 100 -- 👈 safety guard against cycles
)
SELECT * FROM org_chart;
Interview point: cyclic data (A manages B, B manages A) infinite loop bana sakta hai — hamesha depth-limit ya visited-check lagao.
Q44. Recursive CTE se factorial of a number (n=5) nikaalo.
WITH RECURSIVE fact AS (
SELECT 1 AS n, 1 AS result
UNION ALL
SELECT n + 1, result * (n + 1) FROM fact WHERE n < 5
)
SELECT * FROM fact WHERE n = 5;
Q45. Bill of Materials (BOM) style — ek naya demo table lo (parts explosion, common data-eng interview Q).
CREATE TABLE parts (
part_id INT,
part_name VARCHAR(50),
parent_id INT
);
INSERT INTO parts VALUES
(1, 'Car', NULL),
(2, 'Engine', 1),
(3, 'Wheel', 1),
(4, 'Piston', 2),
(5, 'Bolt', 3);
WITH RECURSIVE bom AS (
SELECT part_id, part_name, parent_id, 1 AS level
FROM parts WHERE parent_id IS NULL
UNION ALL
SELECT p.part_id, p.part_name, p.parent_id, b.level + 1
FROM parts p JOIN bom b ON p.parent_id = b.part_id
)
SELECT * FROM bom ORDER BY level;
Q46. Total leaf-level part count per top-level assembly.
Q47. Employee hierarchy mein sirf level 2 tak restrict karo.
WITH RECURSIVE org_chart AS (
SELECT emp_id, emp_name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name, e.manager_id, oc.level + 1
FROM employees e JOIN org_chart oc ON e.manager_id = oc.emp_id
WHERE oc.level < 2
)
SELECT * FROM org_chart;
Q48. Recursive CTE se cumulative running salary budget per level of hierarchy.
Q49. Graph traversal — connected components find karo (ek "friends" edge table lo).
CREATE TABLE friendships (person_a INT, person_b INT);
INSERT INTO friendships VALUES (1,2), (2,3), (4,5);
WITH RECURSIVE network AS (
SELECT person_a AS start_person, person_a, person_b FROM friendships WHERE person_a = 1
UNION ALL
SELECT n.start_person, f.person_a, f.person_b
FROM friendships f
JOIN network n ON f.person_a = n.person_b OR f.person_b = n.person_a
WHERE f.person_a != n.person_a
)
SELECT DISTINCT person_a, person_b FROM network;
Q50. UNION vs UNION ALL recursive CTE mein kyun UNION ALL prefer karte hain (performance + duplicate control ka trade-off).
Q51–55. (Practice variants): recursive CTE se hierarchical path string banao (emp1 > emp2 > emp3); category → sub-category tree (e-commerce schema); recursive CTE se 1-100 ke saare even numbers; recursive CTE se pagination-style batch id generate karo; recursive CTE se employee count per hierarchy depth level.
SECTION D — Data Engineering Scenario CTEs (Q56–80)
Q56. Gaps and Islands — product_events mein consecutive event groups banao (session detection).
WITH ordered AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY event_time) AS rn
FROM product_events
),
islands AS (
SELECT *, event_time - (rn * INTERVAL '1 minute') AS grp
FROM ordered
)
SELECT product_id, MIN(event_time) AS session_start, MAX(event_time) AS session_end
FROM islands GROUP BY product_id, grp;
Q57. Slowly Changing Dimension (SCD Type 2) — latest active record per key nikaalo.
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn = 1;
Q58. Incremental load pattern — CTE se "new rows since last load" nikaalo.
WITH last_loaded AS (
SELECT MAX(order_date) AS max_dt FROM orders_target
)
SELECT o.* FROM orders o, last_loaded l WHERE o.order_date > l.max_dt;
Q59. Data quality check — CTE se null/invalid rows flag karo.
WITH validation AS (
SELECT *,
CASE WHEN amount IS NULL OR amount <= 0 THEN 'INVALID' ELSE 'VALID' END AS dq_flag
FROM orders
)
SELECT dq_flag, COUNT(*) FROM validation GROUP BY dq_flag;
Q60. Pivot-style CTE — dept-wise headcount ko columns mein convert karo.
WITH dept_count AS (
SELECT dept_id, COUNT(*) AS cnt FROM employees GROUP BY dept_id
)
SELECT
SUM(CASE WHEN dept_id = 10 THEN cnt END) AS engineering,
SUM(CASE WHEN dept_id = 20 THEN cnt END) AS sales
FROM dept_count;
Q61. Unpivot-style CTE (columns ko rows mein — reverse of Q60).
Q62. Deduplicate keeping latest record by updated_at (CDC pattern) — new demo table.
CREATE TABLE customer_cdc (
customer_id INT, name VARCHAR(50), updated_at TIMESTAMP
);
INSERT INTO customer_cdc VALUES
(1, 'Rahul', '2024-01-01 10:00:00'),
(1, 'Rahul Kumar','2024-02-01 10:00:00'),
(2, 'Sneha', '2024-01-15 10:00:00');
WITH latest AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
FROM customer_cdc
)
SELECT customer_id, name, updated_at FROM latest WHERE rn = 1;
Q63. Fan-out check — CTE se detect karo ki join se rows duplicate to nahi ho rahi.
Q64. Late-arriving data handle — CTE se "records jo apne batch window ke baad aaye" nikaalo.
Q65. Rolling 7-day active customers CTE se.
WITH daily AS (
SELECT DISTINCT customer_id, order_date FROM orders
)
SELECT d1.order_date,
COUNT(DISTINCT d2.customer_id) AS active_last_7d
FROM daily d1
JOIN daily d2 ON d2.order_date BETWEEN d1.order_date - 6 AND d1.order_date
GROUP BY d1.order_date;
Q66. Funnel analysis CTE — VIEW → CLICK → BUY conversion per product.
WITH funnel AS (
SELECT product_id,
MAX(CASE WHEN event_type='VIEW' THEN 1 ELSE 0 END) AS viewed,
MAX(CASE WHEN event_type='CLICK' THEN 1 ELSE 0 END) AS clicked,
MAX(CASE WHEN event_type='BUY' THEN 1 ELSE 0 END) AS bought
FROM product_events GROUP BY product_id
)
SELECT SUM(viewed) AS total_view, SUM(clicked) AS total_click, SUM(bought) AS total_buy
FROM funnel;
Q67. Cohort retention — CTE se first-order-month cohort banao, phir retention track karo.
Q68. CTE se orphan records find karo (orders jinka customer employees/customers table mein nahi hai — referential integrity check).
Q69. CTE se schema drift detect karna (conceptual — column count/type check via information_schema CTE).
Q70. Partition pruning-friendly CTE likho (date filter early CTE mein daalo, late nahi).
Q71. CTE se top-N per group (top 2 orders by amount per customer) — classic interview favorite.
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn <= 2;
Q72. CTE se anti-join pattern (customers with zero completed orders).
WITH completed AS (
SELECT DISTINCT customer_id FROM orders WHERE status = 'COMPLETED'
)
SELECT e.emp_id FROM employees e
LEFT JOIN completed c ON e.emp_id = c.customer_id
WHERE c.customer_id IS NULL;
Q73. CTE se semi-join pattern (EXISTS vs CTE + IN comparison).
Q74. CTE se data skew check (group sizes ka distribution — bahut bade partitions dhoondo).
Q75. ETL staging pattern — raw → cleaned → aggregated, teen CTE stages mein.
WITH raw AS (
SELECT * FROM orders
),
cleaned AS (
SELECT * FROM raw WHERE status = 'COMPLETED' AND amount > 0
),
aggregated AS (
SELECT customer_id, SUM(amount) AS total_spent FROM cleaned GROUP BY customer_id
)
SELECT * FROM aggregated ORDER BY total_spent DESC;
Q76. CTE se percentile-based outlier detection (orders jinka amount 95th percentile se upar hai).
Q77. CTE se year-over-year growth calculate karo.
Q78. CTE se surrogate key generate karna (ROW_NUMBER() as synthetic PK during load).
Q79. CTE + MERGE/upsert pattern (conceptual — SCD Type 1 upsert).
Q80. CTE se partitioned table ke liye daily row-count audit report banao.
SECTION E — Tricky / Conceptual / Trade-off Questions (Q81–100)
Q81. CTE vs Temp Table vs View — teeno mein fark (lifetime, materialization, reusability).
Q82. CTE vs Subquery — kab kaunsa use karna chahiye (readability vs optimizer behavior).
Q83. Recursive CTE mein UNION ALL mandatory kyun hai (UNION allowed nahi har DB mein, aur duplicates ka issue).
Q84. Kya CTE ko index kiya ja sakta hai? (Nahi — CTE physical object nahi hai, temp table use karo agar indexing chahiye)
Q85. Multiple CTEs ek dusre ko reference kar sakte hain kya? (Haan, agar order mein defined ho — forward reference allowed nahi)
Q86. Postgres 12+ mein CTE MATERIALIZED keyword ka use kya hai.
WITH filtered AS MATERIALIZED (
SELECT * FROM orders WHERE status = 'COMPLETED'
)
SELECT * FROM filtered;
Q87. CTE ke andar DISTINCT, GROUP BY, ORDER BY allowed hain kya — kaunsa restriction hai (ORDER BY bina LIMIT/TOP/OFFSET ke kai DB mein CTE ke andar ignore/error ho sakta hai).
Q88. Recursive CTE mein anchor query mein ORDER BY allowed hai kya (generally not, sirf outer query mein).
Q89. CTE se large dataset process karte waqt performance pitfall kya ho sakta hai (non-materialized CTE = repeated computation agar multiple baar reference ho).
Q90. CTE ka execution plan kaise dekhein (EXPLAIN ANALYZE) aur kya batayega.
Q91. CTE se self-referencing table update karna directly possible hai kya (nahi directly — CTE read-only hota hai target ke against, workaround: join based update).
Q92. Recursive CTE ki max depth limit kya hoti hai (SQL Server default 100 via MAXRECURSION, Postgres by default unlimited but memory-bound).
Q93. CTE aur window function ka combination kyun powerful hota hai ETL mein (filter + rank + dedupe ek hi readable flow mein).
Q94. CTE ke andar dynamic SQL possible hai kya (nahi directly — CTE static hai, dynamic SQL alag se banake execute karo).
Q95. CTE naming collision ka rule (agar CTE naam existing table se match kare to CTE priority leta hai usi query scope mein).
Q96. Spark SQL / BigQuery mein CTE ka behavior traditional RDBMS se kaise differ karta hai (lazy evaluation, Catalyst optimizer CTE ko predicate pushdown ke saath inline kar sakta hai).
Q97. CTE se recursive hierarchy mein cycle detect karna (visited array/path track karke).
WITH RECURSIVE org_chart AS (
SELECT emp_id, manager_id, ARRAY[emp_id] AS visited
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.manager_id, oc.visited || e.emp_id
FROM employees e JOIN org_chart oc ON e.manager_id = oc.emp_id
WHERE NOT (e.emp_id = ANY(oc.visited)) -- 👈 cycle guard
)
SELECT * FROM org_chart;
Q98. CTE data pipelines mein dbt jaise tools kaise CTE ka heavy use karte hain (modular models = compiled CTEs).
Q99. Ek real interview trap: "CTE hamesha subquery se fast hota hai" — sach hai ya myth? (Myth — depends on optimizer, materialization strategy, aur data size).
Q100. Final case study: employees + orders + product_events teeno tables combine karke ek CTE pipeline banao jo — dept-wise total revenue-influencing customers dikhaye (multi-CTE capstone).
WITH completed_orders AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders WHERE status = 'COMPLETED'
GROUP BY customer_id
),
active_products AS (
SELECT DISTINCT product_id FROM product_events WHERE event_type = 'BUY'
),
summary AS (
SELECT co.customer_id, co.total_spent
FROM completed_orders co
WHERE co.total_spent > 300
)
SELECT * FROM summary ORDER BY total_spent DESC;
🧠 Interview-ready one-liner (agar poochein "CTE explain karo 30 seconds mein")
"CTE ek named temporary result set hai jo
WITHclause se define hota hai, query ke scope tak hi live rehta hai, readability improve karta hai complex multi-step logic mein, aur recursive CTE ke through hierarchical ya graph data (org charts, BOM, category trees) handle karne ka standard tarika hai. Trade-off yeh hai ki yeh index nahi ho sakta aur DB-dependent hai ki materialize hoga ya inline optimize hoga."
Top comments (0)