⚠️ 20. Double Counting in Multiple JOINs

If both "orders" and "payments" have multiple rows per customer, joining them directly can create a many-to-many multiplication.

Example: 2 orders × 3 payments = 6 joined rows. SUM() will overcount.

Understand the grain of each table before joining.

🧠 21. JOINs and Table Grain

Before writing a JOIN, identify:

Table 1 - One row = one customer,

Table 2 - One row = one order → One-to-Many relationship.

Understanding table grain helps prevent: duplicate counts, inflated revenue, incorrect averages, incorrect KPIs.

🎤 SQL Interview Questions

Q1. What is a JOIN?

Combines rows from multiple tables using a related condition.

Q2. What is the difference between INNER JOIN and LEFT JOIN?

INNER returns only matching, LEFT returns all from left + matching from right.

Q3. How do you find customers who never placed an order?

LEFT JOIN + WHERE o.customer_id IS NULL

Q4. What is a SELF JOIN?

Joins a table to itself, for hierarchical relationships.

Q5. What is a CROSS JOIN?

Creates every possible combination.

Q6. Why can JOINs create duplicate rows?

Because of one-to-many or many-to-many relationships.

Q7. Why should you understand table grain?

Because grain determines how rows multiply and whether aggregations become inaccurate.

Q8. What happens when there is no match in a LEFT JOIN?

Columns from right become NULL.

Q9. How do you count unique customers after a JOIN?

COUNT(DISTINCT customer_id)

Q10. Can a query contain multiple JOINs?

Yes.

📝 Practice Questions

Practice 1: Return customer names and their orders.

SELECT c.customer_name, o.order_id 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id;

Practice 2: Find customers who have never ordered.

SELECT c.customer_id, c.customer_name 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
WHERE o.customer_id IS NULL;

Practice 3: Calculate total spending per customer.

SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

Practice 4: Return all customers and their order counts, including zero orders.

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

Practice 5: Find number of unique customers who placed orders.

SELECT COUNT(DISTINCT c.customer_id) AS unique_customers 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id;

🧪 Mini SQL Challenge

Write a query that returns: Customer name, Product name, Category, Amount - Only orders > ₹1,000.

Solution:

SELECT c.customer_name, p.product_name, p.category, o.amount 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
JOIN products p ON o.product_id = p.product_id 
WHERE o.amount > 1000 
ORDER BY o.amount DESC;

📌 JOINs are the bridge between database tables. But writing a JOIN is only half the skill. A strong Data Analyst also understands: What each table represents → How tables are related → How rows will multiply → How that affects the KPI.

Double Tap ❤️ For More