Post #2558

Data Analyst Interview Questions with Answers: Part-3 21. What is SELECT used for? SELECT is used to fetch specific columns or data from a table. Example: SELECT customer_name, sales FROM orders; This query returns customer names and their sales from the orders table. 22. Difference between WHERE and HAVING? WHERE filters rows before aggregation. HAVING filters results after aggregation. Example: SELECT product, SUM(sales) AS total_sales FROM orders WHERE region = 'East' GROUP BY product HAVING SUM(sales) > 100000; Here, WHERE filters region first, HAVING filters aggregated sales. 23. What is GROUP BY? GROUP BY groups rows with the same values so aggregate functions can be applied. Example: SELECT region, SUM(sales) AS total_sales FROM orders GROUP BY region; This gives total sales per region. 24. What are aggregate functions? Aggregate functions perform calculations on multiple rows. Common examples: • COUNT → total rows • SUM → total value • AVG → average • MIN / MAX → smallest or largest value Example: SELECT COUNT(order_id), AVG(sales) FROM orders; 25. Difference between INNER JOIN and LEFT JOIN? INNER JOIN: Returns only matching records. LEFT JOIN: Returns all rows from left table and matching rows from right table. Example: SELECT o.order_id, c.customer_name FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id; All orders appear even if customer info is missing. 26. What are subqueries? A subquery is a query inside another query. Example: SELECT * FROM orders WHERE sales > (SELECT AVG(sales) FROM orders); Returns orders with sales above average. 27. What is a CTE? CTE (Common Table Expression) is a temporary named result set that improves readability. Example: WITH sales_summary AS ( SELECT region, SUM(sales) AS total_sales FROM orders GROUP BY region ) SELECT * FROM sales_summary WHERE total_sales > 500000; 28. How do you handle duplicates in SQL? Identify duplicates: SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 1; Remove duplicates (using ROW_NUMBER): DELETE FROM orders WHERE order_id IN ( SELECT order_id FROM ( SELECT order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) rn FROM orders ) t WHERE rn > 1 ); 29. How do you handle NULL values? Check NULL: SELECT * FROM orders WHERE sales IS NULL; Replace NULL: SELECT COALESCE(sales, 0) AS sales_amount FROM orders; 30. What are window functions? Window functions perform calculations across rows without grouping them. Example: SELECT customer_id, sales, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sales DESC) AS rn FROM orders; This ranks sales per customer without collapsing rows. Double Tap ♥️ For Part-4