Team Match Statistics

SQL coding challenge · Difficulty: medium · Topic: Aggregation · +100 XP

Problem

You are given matches, one row per completed match between two teams.

For every team that played at least one match, report its record:

| Column | Meaning |
| --- | --- |
| `team` | the team name |
| `total_match` | matches played, whether it appeared as `team1` or `team2` |
| `win_count` | matches where `team = winner` |
| `lose_count` | matches it played but did not win |

Table

Table: matches

| Column | Type | Description |
| --- | --- | --- |
| `team1` | VARCHAR | first participating team |
| `team2` | VARCHAR | second participating team |
| `winner` | VARCHAR | the winning team |

Rules

1. A team can appear in either team1 or team2.

2. Every match counts once toward the total of both participants.

3. A team wins when team = winner; the other participant takes the loss.

4. Include every team that played at least one match.

5. Do not hardcode team names. The query must work for any set of teams.

6. Return rows sorted by team ascending.

Example

Input matches:

| team1 | team2 | winner |
| --- | --- | --- |
| Ind | Aus | Ind |
| Sr | Ind | Ind |
| Ind | Eng | Eng |
| Aus | Sr | Sr |
| Sr | Eng | Sr |
| Eng | Aus | Eng |
| Aus | Sr | Aus |

Expected output:

| team | total_match | win_count | lose_count |
| --- | --- | --- | --- |
| Aus | 4 | 1 | 3 |
| Eng | 3 | 2 | 1 |
| Ind | 3 | 2 | 1 |
| Sr | 4 | 2 | 2 |

Why Aus is 4 and not 3

Aus appears in four rows, and it is easy to miss one:

• row 1 Ind vs Aus -> loss
• row 4 Aus vs Sr -> loss
• row 6 Eng vs Aus -> loss
• row 7 Aus vs Sr -> win

So Aus is 4 / 1 / 3. Two arithmetic checks catch most mistakes:

the sum of total_match must equal 2 x number of matches (here 14), and

the sums of win_count and lose_count must each equal the number of

matches (here 7).

Approach

A team lives in two different columns, so normalise them into one column

first, then aggregate conditionally. Keep duplicates while you do it: the

same pair may play more than once, and collapsing identical rows silently

undercounts every repeated fixture.

Constraints

• 1 <= number of matches <= 100000
• team1, team2 and winner are never NULL
• winner is always one of the two participating teams
• the same pair of teams may play more than once

What this SQL challenge teaches you

“Team Match Statistics” is a medium-level SQL challenge focused on Aggregation. Working through it gives you hands-on practice with UNION ALL, conditional aggregation, GROUP BY, CASE WHEN, self-union — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

Concepts covered

• UNION ALL
• conditional aggregation
• GROUP BY
• CASE WHEN
• self-union

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

1. A team can be in team1 or team2. Build one column that contains both: select team1 from matches, then stack the team2 rows underneath it.
2. Use UNION ALL, not UNION. UNION removes duplicate rows, so a fixture played three times with the same winner collapses into one and every count for that pair is wrong.
3. Carry winner along into the stacked set. Then per team: COUNT(*) is total_match, SUM(CASE WHEN team = winner THEN 1 ELSE 0 END) is win_count, and the <> version is lose_count.

Where this comes up

Variations of this problem have been reported in interviews at Deloitte. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

How to practise it on PySpark.in

Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related SQL challenges

• Find Duplicate Emails
• HR: Average Salary by Department
• Logistics: Count Shipments by Status
• Count Total Orders Placed by Each Customer
• Find Average Order Amount for Each Customer
• Find Customers Who Placed More Than 5 Orders (HAVING)

Helpful resources

• Practice this topic
• Open the notebook

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated medium and covers Aggregation.

Solve this challenge free on PySpark.in