Must Know Data Interview Question | MULTIPLE TABLES JOIN & AGGREGATE | INTERVIEW TIPS | SQL | PART 2

Welcome to series on Data Interview questions and answers! In this video, we'll cover the most asked and most common data questions that help you prepare nextgen data professionals for data engineer, data analyst, & BIE position. We'll explore technical and real-life case-based questions covering SQL, DAX, POWER BI, Python, ETL, data warehousing, machine learning, Microsoft Fabric, Azure & Snowflake Cloud platforms, Big Data frameworks (Databricks, Spark). Want more similar videos- hit like, comment, share and subscribe ❤️Do Like, Share and Comment ❤️ Like Aim 1000 likes! ➖➖➖➖➖➖➖➖➖➖➖➖➖ Please like & share the video. ➖➖➖➖➖➖➖➖➖➖➖➖➖ Sample Data script CREATE TABLE dbo.customers ( customer_id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE dbo.orders ( order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10, 2), order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); -- Customers INSERT INTO customers (customer_id, name) VALUES (1, 'Matt'), (2, 'Chris'), (3, 'Cheryl'), (4, 'David'), (5, 'John'); -- Orders INSERT INTO orders (order_id, customer_id, amount, order_date) VALUES (101, 1, 2500.00, '2024-01-10'), (102, 2, 3000.00, '2024-01-15'), (103, 1, 1500.00, '2024-02-20'), (104, 3, 5000.00, '2024-03-05'), (105, 2, 1000.00, '2024-03-10'), (106, 3, 2000.00, '2024-04-01'), (107, 5, 2000.00, '2024-07-01'), (108, 1, 2000.00, '2023-07-01'); ➖➖➖➖➖➖➖➖➖➖➖➖➖ what we have covered in this video: GROUP BY & JOIN & NULL Problem Statements - Solved using LEFT JOIN, GROUP BY AND COALESCE FUNCTION (MEDIUM & ADVANCE SQL DATA INTERVIEW CONCEPTS) We also looked as some tips and strategies for the standing out in your next data interview! Summary: MOST ASKED AND MOST IMPORTANT MUST KNOW INTERVIEW PROBLEM STATEMENT - Show no of orders placed and total spent per year by each customer Table 1 : Customers(customer_id, name) Table 2 : Orders (order_id, customer_id, amount, order_date ) GROUP BY, JOIN QUESTIONS CATEGORY TIP 1 - Use GROUP BY, JOIN TIP 2 - IDENTIFY KEYWORDS Words like 'Per' Year 'Each' customer indicate these fields need to be used in group by clause Words like 'no of orders' indicate use of aggregate function Count & 'Total amount' for Sum TIP 3 - Ask FOLLOW UP questions to the interviewer regarding the data & output. Do they need customers who have not ordered so far TIP 4 - STAND OUT!! - Walkthrough the code and explain why you used which JOIN ➖➖➖➖➖➖➖➖➖➖➖➖➖ Hope this video was useful and you learned something new :) See you in next video, until then Bye!