1841. League Statistics

Problem Explanation

In this problem, we are given information about football teams and their performance in various matches. We need to generate a statistics table containing information about the teams' performance, such as the number of matches played, points earned, goals scored, goals conceded, and goal difference.

We have two tables:

Teams: This table contains the team_id and the respective team_name.
Matches: This table contains information about the matches, such as the home_team_id, away_team_id, home_team_goals, and away_team_goals.

Our goal is to write an SQL query to generate a statistics table with team_name, matches_played, points, goal_for, goal_against, and goal_diff columns.

Let's walk through an example:

Example

Teams:

team_id	team_name
1	Ajax
4	Dortmund
6	Arsenal

Matches:

home_team_id	away_team_id	home_team_goals	away_team_goals
1	4	0	1
1	6	3	3
4	1	5	2
6	1	0	0

Result:

team_name	matches_played	points	goal_for	goal_against	goal_diff
Dortmund	2	6	6	2	4
Arsenal	2	2	3	3	0
Ajax	4	2	5	9	-4

Approach

To generate the required statistics table, we can use the following approach:

Calculate matches played, goals scored, and goals conceded for each team in the home and away matches.
Combine the home and away statistics for each team.
Calculate total points and goal difference for each team.
Order the teams by total points, goal difference, and team_name.

Solution

SQL

1WITH home_stats AS (
2  SELECT
3    home_team_id AS team_id,
4    COUNT(*) AS matches_played,
5    SUM(home_team_goals) AS goal_for,
6    SUM(away_team_goals) AS goal_against,
7    SUM(CASE
8      WHEN home_team_goals > away_team_goals THEN 3
9      WHEN home_team_goals = away_team_goals THEN 1
10      ELSE 0
11    END) AS points
12  FROM
13    Matches
14  GROUP BY
15    home_team_id
16),
17away_stats AS (
18  SELECT
19    away_team_id AS team_id,
20    COUNT(*) AS matches_played,
21    SUM(away_team_goals) AS goal_for,
22    SUM(home_team_goals) AS goal_against,
23    SUM(CASE
24      WHEN away_team_goals > home_team_goals THEN 3
25      WHEN away_team_goals = home_team_goals THEN 1
26      ELSE 0
27    END) AS points
28  FROM
29    Matches
30  GROUP BY
31    away_team_id
32),
33combined_stats AS (
34  SELECT
35    T.team_id,
36    T.team_name,
37    COALESCE(H.matches_played, 0) + COALESCE(A.matches_played, 0) AS matches_played,
38    COALESCE(H.goal_for, 0) + COALESCE(A.goal_for, 0) AS goal_for,
39    COALESCE(H.goal_against, 0) + COALESCE(A.goal_against, 0) AS goal_against,
40    COALESCE(H.points, 0) + COALESCE(A.points, 0) AS points
41  FROM
42    Teams T
43    LEFT JOIN home_stats H ON T.team_id = H.team_id
44    LEFT JOIN away_stats A ON T.team_id = A.team_id
45)
46SELECT
47  team_name,
48  matches_played,
49  points,
50  goal_for,
51  goal_against,
52  goal_for - goal_against AS goal_diff
53FROM
54  combined_stats
55ORDER BY
56  points DESC,
57  goal_diff DESC,
58  team_name ASC;

Not Sure What to Study? Take the 2-min Quiz to Find Your Missing Piece：

What's the output of running the following function using input [30, 20, 10, 100, 33, 12]?

1def fun(arr: List[int]) -> List[int]:
2    import heapq
3    heapq.heapify(arr)
4    res = []
5    for i in range(3):
6        res.append(heapq.heappop(arr))
7    return res
8

1public static int[] fun(int[] arr) {
2    int[] res = new int[3];
3    PriorityQueue<Integer> heap = new PriorityQueue<>();
4    for (int i = 0; i < arr.length; i++) {
5        heap.add(arr[i]);
6    }
7    for (int i = 0; i < 3; i++) {
8        res[i] = heap.poll();
9    }
10    return res;
11}
12

1class HeapItem {
2    constructor(item, priority = item) {
3        this.item = item;
4        this.priority = priority;
5    }
6}
7
8class MinHeap {
9    constructor() {
10        this.heap = [];
11    }
12
13    push(node) {
14        // insert the new node at the end of the heap array
15        this.heap.push(node);
16        // find the correct position for the new node
17        this.bubble_up();
18    }
19
20    bubble_up() {
21        let index = this.heap.length - 1;
22
23        while (index > 0) {
24            const element = this.heap[index];
25            const parentIndex = Math.floor((index - 1) / 2);
26            const parent = this.heap[parentIndex];
27
28            if (parent.priority <= element.priority) break;
29            // if the parent is bigger than the child then swap the parent and child
30            this.heap[index] = parent;
31            this.heap[parentIndex] = element;
32            index = parentIndex;
33        }
34    }
35
36    pop() {
37        const min = this.heap[0];
38        this.heap[0] = this.heap[this.size() - 1];
39        this.heap.pop();
40        this.bubble_down();
41        return min;
42    }
43
44    bubble_down() {
45        let index = 0;
46        let min = index;
47        const n = this.heap.length;
48
49        while (index < n) {
50            const left = 2 * index + 1;
51            const right = left + 1;
52
53            if (left < n && this.heap[left].priority < this.heap[min].priority) {
54                min = left;
55            }
56            if (right < n && this.heap[right].priority < this.heap[min].priority) {
57                min = right;
58            }
59            if (min === index) break;
60            [this.heap[min], this.heap[index]] = [this.heap[index], this.heap[min]];
61            index = min;
62        }
63    }
64
65    peek() {
66        return this.heap[0];
67    }
68
69    size() {
70        return this.heap.length;
71    }
72}
73
74function fun(arr) {
75    const heap = new MinHeap();
76    for (const x of arr) {
77        heap.push(new HeapItem(x));
78    }
79    const res = [];
80    for (let i = 0; i < 3; i++) {
81        res.push(heap.pop().item);
82    }
83    return res;
84}
85

[30, 20, 10][100, 33, 30][10, 12, 20]

I don’t know

Discover Your Strengths and Weaknesses: Take Our 2-Minute Quiz to Tailor Your Study Plan:

Solution Implementation

Not Sure What to Study? Take the 2-min Quiz：

Which of the following is the prefix sum of array [1, 2, 3, 4, 5]?

[1, 2, 3, 4, 5][1, 3, 6, 10, 15][15, 14, 12, 9, 5][5, 4, 3, 2, 1]

I don’t know

Fast Track Your Learning with Our Quick Skills Quiz:

Problem: Given a list of tasks and a list of requirements, compute a sequence of tasks that can be performed, such that we complete every task once while satisfying all the requirements.

Which of the following method should we use to solve this problem?

Topological SortUniform Cost SearchMonotonic StackMemoization

I don’t know

Overview

Basic Data Structures

Basic Algorithms

Overview

Sorted Array

Implicitly Sorted Array

Advanced

Speedrun

Introduction

DFS on Tree

Binary Search Tree

Advanced

Combinatorial Search

Pruning

Additional States

Aggregation and Memoization

Dedup

Additional Practices

Speedrun

Introduction

BFS on Tree

Introduction

Vanilla BFS

Matrix as Graph

Implicit Graph

Directed Graph / Topological Sort

Weighted Graph

Minimum Spanning Tree

Speedrun

Introduction

Same Direction

Opposite Direction

Sliding Window

Prefix Sum

Cycle Finding

Advanced

Speedrun

Introduction

Top K

Moving Best

Multiple Heaps

Introduction

Grid

Dynamic number of subproblems

Interval

Dual-Sequence

Knapsack

Bitmask

Additional Practices

Disjoint Set Union | Union Find

Trie

Data Structure Design

Segment Tree

Interval

Monotonic Stack

Divide and Conquer

Line Sweep

Tree Traversal without Recursion

Greedy

Math

Matrix

Classics

Concepts

Projects

Amazon OA

Microsoft OA

Google OA

Twitter OA

Editorials

1841. League Statistics

Problem Explanation

Example

Approach

Solution

SQL

Solution Implementation

Recommended Readings