Case Studies: Amazon, IPL Analytics

1. Amazon Case Study – Customer & Order Analysis

This case study explores how Amazon can use SQL joins and subqueries to gain deep insights into customer behavior, order patterns, and product preferences.

Scenario:

Amazon has multiple interconnected tables—like Customers, Orders, Order Items, Products, and Reviews. To gain insights, Amazon needs to pull data across these tables using joins and apply subqueries to filter and calculate meaningful patterns.

Key Concepts Explained:

• INNER JOIN: Used to combine Orders and Order Items to know which products were ordered.
• LEFT JOIN: Helps find customers who signed up but never placed an order.
• Subqueries: Used to identify top-selling products, highest-rated products, or customers with above-average spending.

Example Use Cases:

• Find all customers whose total order value exceeds the average order value across the platform (using subqueries).
• Identify the most recent order for each customer (using correlated subqueries).
• Join Orders and Order Items to calculate product-wise revenue.

Learning Outcome: Understand how to stitch data from different sources and apply logic to analyze trends, gaps, and high-value segments.

2. IPL Analytics Case Study – Performance & Match Insights

The Indian Premier League (IPL) offers a goldmine of structured sports data such as Players, Teams, Matches, Scores, and Stadiums.

Scenario:

As an analyst, your goal is to evaluate player performance, team trends, and match outcomes using relational databases. This requires combining data from multiple tables and applying condition-based logic.

Key Concepts Explained:

• Joins: To link Players with Scores, Teams with Matches, etc.
• Aggregations with GROUP BY: To find the highest run-scorers or most wickets.
• Subqueries: Used to compare a player’s score to team averages or to find top performers in each match.

Example Use Cases:

• Find all players who scored more than their team’s average score across matches.
• Identify the stadium with the highest average match score using subqueries.
• List the top 5 bowlers based on average wickets per game (using joins + filtering).

Learning Outcome: Learn how sports data can be broken down into granular performance metrics using SQL tools like joins and nested queries.

Summary: What Students Gain