Team Match Statistics

PYSPARK 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:

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

What this PYSPARK challenge teaches you

“Team Match Statistics” is a medium-level PYSPARK challenge focused on Aggregation. Working through it gives you hands-on practice with union, groupBy, agg, when/otherwise, conditional aggregation — 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

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. Select each column separately, alias both to the same name, and stack them with union().
  2. DataFrame.union is UNION ALL - it keeps duplicates. That is what you want: the same fixture can be played more than once.
  3. Keep winner in both halves of the union, then groupBy('team') and aggregate: count('*'), and sum(when(col('team') == col('winner'), 1).otherwise(0)) for wins.

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 PySpark code 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 PYSPARK challenges

Helpful resources

Frequently asked questions

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

No. The PYSPARK 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