Context

Analysts often need to answer business questions like revenue by customer, orders by region, or average spend by product category. These problems usually require combining tables correctly and then aggregating the data at the right level.

Question

Explain how you would write a SELECT statement with JOINs and GROUP BY to answer a business question. In your answer, describe:

1. How you identify the base table and required joins
2. How you choose the correct join type
3. How you decide which columns belong in SELECT versus GROUP BY
4. How aggregate functions like COUNT(), SUM(), and AVG() fit into the query
5. Common mistakes that lead to incorrect counts or duplicated results

Scope Guidance

The interviewer expects a practical explanation, not just definitions. You should be able to describe a clear approach, discuss trade-offs such as INNER JOIN vs LEFT JOIN, and use a simple business example to show how the query is built.