81. What is a CTE and why use it?

Answer: A Common Table Expression (WITH) is a temporary, named result set used to simplify complex queries, improve readability, and support recursion.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e JOIN dept_avg d ON e.dept_id = d.dept_id;
        

82. What is a recursive CTE?

Answer: A CTE that references itself to traverse hierarchies (e.g., org charts, category trees).

WITH RECURSIVE chain AS (
  SELECT emp_id, manager_id, 0 AS lvl FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.manager_id, c.lvl+1
  FROM employees e JOIN chain c ON e.manager_id = c.emp_id
)
SELECT * FROM chain;
        

83. Difference between CTE and subquery?

Answer: Both produce derived result sets; CTEs are named and reusable within a statement, improve readability, and support recursion. Subqueries are inline and not reusable.

84. Use LAG/LEAD to compare rows

Answer: Window functions to access prior/next row values without self-joins.

SELECT order_id, order_date, amount,
       LAG(amount)  OVER (ORDER BY order_date) AS prev_amt,
       LEAD(amount) OVER (ORDER BY order_date) AS next_amt
FROM orders;
        

85. Nth highest salary pattern

Answer: Use DENSE_RANK() over salary and filter by rank.

WITH r AS (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, salary FROM r WHERE rnk = 3; -- 3rd highest
        

86. Find customers with no orders (anti-join)

Answer: Use LEFT JOIN ... WHERE o.id IS NULL or NOT EXISTS.

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;
-- OR
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
        

87. Window frame clauses (RANGE/ROWS) use?

Answer: Define which rows participate in the window calculation (e.g., rolling 7 days vs all prior rows).

SELECT dt, amount,
  SUM(amount) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7
FROM daily_sales;
        

88. Star vs Snowflake schema

Answer: Star: denormalized dimensions around a fact table (simpler, faster queries). Snowflake: normalized dimensions (less redundancy, more joins). Common in analytics SQL interview questions.

89. OLTP vs OLAP

Answer: OLTP: transactional, many small writes; OLAP: analytical, large reads across history. Different indexing and schema strategies.

90. Materialized view vs view

Answer: View is virtual; materialized view stores results physically for faster reads (needs refresh).

91. Query to get monthly active users (MAU)

Answer: Count distinct users per month from activity table.

SELECT DATE_TRUNC('month', activity_at) AS mth,
       COUNT(DISTINCT user_id) AS mau
FROM user_activity
GROUP BY 1
ORDER BY 1;
        

92. Cohort retention query pattern

Answer: Build cohorts by first activity month and measure active users in subsequent months.

WITH first_seen AS (
  SELECT user_id, DATE_TRUNC('month', MIN(activity_at)) AS cohort
  FROM user_activity GROUP BY 1
), activity AS (
  SELECT user_id, DATE_TRUNC('month', activity_at) AS act_month
  FROM user_activity
)
SELECT f.cohort, a.act_month, COUNT(DISTINCT a.user_id) AS retained
FROM first_seen f JOIN activity a USING (user_id)
GROUP BY 1,2
ORDER BY 1,2;
        

93. JSON handling in SQL (example)

Answer: Use JSON operators/functions to extract fields (DB-specific).

-- Postgres example
SELECT payload->>'event' AS event_type,
       (payload->>'amount')::NUMERIC AS amount
FROM events;
        

94. Date/time pitfalls to watch

Answer: Time zones, DST shifts, inclusive/exclusive ranges, and using DATE_TRUNC for grouping. Always store UTC and display local time in apps.

95. Database testing checks for freshers

Answer: Validate counts, duplicates, referential integrity, NULL policy, and boundary values.

-- Count mismatch
SELECT 'orders' AS tbl, COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(DISTINCT order_id) FROM order_items;
        

96. SQL queries for employee data — examples

Answer: Common interview mini-tasks.

-- Avg salary by department
SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id;

-- Employees hired last 30 days
SELECT * FROM employees WHERE hire_date >= CURRENT_DATE - INTERVAL '30 day';
        

97. Transaction isolation levels

Answer: Read Uncommitted, Read Committed, Repeatable Read, Serializable — control phenomena like dirty/non-repeatable reads and phantom rows.

98. Why check the execution plan?

Answer: To understand how the optimizer executes a query (scans vs seeks, join strategies, index usage) and to fix bottlenecks.

99. UPSERT pattern (MERGE / ON CONFLICT)

Answer: Insert if not exists, else update — keeps data idempotent and consistent.

-- Postgres
INSERT INTO customers (customer_id, name, email)
VALUES (1, 'Asha', 'asha@x.com')
ON CONFLICT (customer_id)
DO UPDATE SET email = EXCLUDED.email;
        

100. End-to-end analytics case: top product by revenue per month

Answer: Combine aggregation + window ranking to pick the monthly winner (classic sql interview questions for analytics).

WITH monthly AS (
  SELECT DATE_TRUNC('month', o.order_date) AS mth,
         oi.product_id,
         SUM(oi.qty * oi.price) AS revenue
  FROM orders o JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY 1,2
), ranked AS (
  SELECT *, RANK() OVER (PARTITION BY mth ORDER BY revenue DESC) AS rnk
  FROM monthly
)
SELECT mth, product_id, revenue
FROM ranked
WHERE rnk = 1
ORDER BY mth;