SQL Case Study: ICC World Cup Match Statistics (From 100 Days of SQL by Ankit Bansal)

In this article, we are going to fully break down one of the practical problems from that series:

Find the number of matches played, won, and lost by each team in the ICC World Cup.

But we’re not just writing the solution. We’re going to understand the logic, the approach, the table schema, the transformation technique, and the final output interpretation. By the end, you’ll be able to solve similar problems confidently in interviews and real-world projects.

About the 100 Days of SQL Series

The 100 Days of SQL course by Ankit Bansal on Udemy is designed to transform beginners into confident SQL professionals. Instead of teaching only syntax, the course focuses heavily on real-world business scenarios. Every day presents a new challenge, pushing learners to think like data analysts rather than just coders.

This ICC World Cup problem is a perfect example of that teaching style. It looks simple at first glance — just count matches — but when you actually attempt it, you realize the dataset structure creates complexity. That’s exactly why this problem is powerful. It trains your brain to reshape data before aggregation, which is a critical SQL skill.

Problem Statement

We are given a table containing match results from the ICC World Cup. Each row represents one match and includes:

• Team 1
• Team 2
• Winner

The task is straightforward in wording:

Find the number of matches played, number of matches won, and number of matches lost by each team.

But solving it correctly requires careful thinking.

Understanding the Table Schema

Table Structure

create table icc_world_cup
(
Team_1 Varchar(20),
Team_2 Varchar(20),
Winner Varchar(20)
);

This table stores match-level data. Notice something important here:
Each match has two participating teams stored in two different columns.

That detail is the core challenge.

Sample Data Provided

INSERT INTO icc_world_cup values('India','SL','India');
INSERT INTO icc_world_cup values('SL','Aus','Aus');
INSERT INTO icc_world_cup values('SA','Eng','Eng');
INSERT INTO icc_world_cup values('Eng','NZ','NZ');
INSERT INTO icc_world_cup values('Aus','India','India');

So we have 5 matches in total.

Breaking Down the Business Requirement

We need to calculate for each team:

1. Matches Played
2. Matches Won
3. Matches Lost

Now think carefully — how do we count matches played?

A team can appear either in Team_1 or Team_2. That means simply grouping by one column will miss half the appearances.

This is where most beginners make mistakes.

Challenges in Solving This Problem

The main challenge is data structure.

The data is stored in a wide format:

• Team_1 column
• Team_2 column

But to aggregate correctly, we need data in a single team column format.

So before aggregation, we must reshape the dataset.

That’s the key learning from this exercise.

Approach Used to Solve the Query

Let’s break this logically.

Step 1 – Reshaping the Data Using UNION ALL

We combine both team columns into a single column using UNION ALL.

select team_1 as team, winner from icc_world_cup
union all
select team_2 as team, winner from icc_world_cup

Now every team appearance becomes a separate row.

This transformation is the backbone of the solution.

Step 2 – Calculating Matches Played

Once reshaped, we simply count how many times each team appears.

count(*)

Each appearance represents one match played.

Step 3 – Calculating Wins

To count wins:

sum(case when team = winner then 1 else 0 end)

If the team name matches the winner column, that’s a win.

Step 4 – Calculating Losses

To count losses:

sum(case when team != winner then 1 else 0 end)

If the team participated but was not the winner, that’s a loss.

Complete SQL Solution

select team,
count(*) as 'number_of_matches_played',

sum(
case when team = winner then 1 else 0 end
) as 'number_of_matches_won',

sum(
case when team != winner then 1 else 0 end
) as 'number_of_matches_loss'

from
(
select team_1 as team, winner
from icc_world_cup
union all
select team_2 as team, winner from icc_world_cup
) A

group by team
order by count(*) desc;

Explanation of the Query Logic

The inner subquery reshapes the dataset.

The outer query performs aggregation.

We:

• Group by team
• Count appearances
• Use conditional aggregation for wins and losses
• Sort by matches played

This pattern is called conditional aggregation after data normalization.

It’s extremely common in analytics projects.

Expected Output

Based on the provided dataset, the result will be:

Output Interpretation

• India played 2 matches and won both.
• SL played 2 matches but lost both.
• NZ played 1 match and won it.

Everything aligns with the inserted data.

Why UNION ALL Is Important

If you use UNION instead of UNION ALL, duplicates could be removed. That would produce incorrect match counts.

Since every match appearance matters, UNION ALL is the correct choice.

Real-World Application of This Pattern

This exact technique is used in:

• Sports analytics dashboards
• E-commerce order analytics
• Banking transaction summaries
• Customer interaction tracking

Any time data is stored in multiple columns but needs aggregated reporting, this approach applies.

Interview Perspective

Interviewers love this type of problem because it tests:

• Understanding of aggregation
• Conditional logic using CASE
• Data reshaping skills
• Query structuring

If you can confidently explain this approach, you demonstrate strong analytical thinking.

Key Takeaways

• Always analyze data structure before writing aggregation queries.
• Reshape data when necessary.
• Use conditional aggregation for categorized counts.
• Think like an analyst, not just a coder.

Conclusion

This ICC World Cup SQL challenge from the 100 Days of SQL series by Ankit Bansal is more than a simple counting problem. It teaches you how to reshape data, apply conditional logic, and generate meaningful summary statistics from transactional records.

Once you master this pattern, many real-world analytics problems become significantly easier to solve.

FAQs

1. Why not use JOIN instead of UNION ALL?

JOIN would complicate the logic and is unnecessary here since we only need to combine rows.

2. Does this work in MySQL, SQL Server, and PostgreSQL?

Yes. The query uses standard ANSI SQL.

3. What if there are ties?

You would need to modify the logic to account for draws.

4. Can we calculate win percentage?

Yes, divide wins by matches played.

5. Is this suitable for large datasets?

Yes, but indexing and optimization should be considered.