Intermediate SQL Coding Challenges

Write a Query Using Multiple Joins

Retrieve a list of orders including customer name and product details using at least two joins.

Expected output: A result set with order ID, customer name, product name, and order date.

Hint: Join orders with customers and products tables on their respective keys.

Use Window Functions to Rank Sales

Write a query to rank salespersons by total sales within each region.

Expected output: A table showing salesperson, region, total sales, and rank.

Hint: Use SUM() as an aggregation and RANK() as a window function partitioned by region.

Aggregate and Filter Groups

Find products with average order quantity greater than 10.

Expected output: List of product IDs meeting the criteria.

Hint: Use GROUP BY product_id and HAVING AVG(quantity) > 10.

INNER JOIN returns only matching rows between tables, while LEFT JOIN returns all rows from the left table and matching rows from the right table or NULL if no match exists.

Use window functions when you need to perform calculations across rows without collapsing the result set, such as ranking or running totals, whereas GROUP BY aggregates rows into fewer rows.

Subqueries break complex queries into smaller parts, making them easier to understand and maintain by isolating filtering or aggregation logic.