TechGeekXpro
SQL Interview Prep - DAY 1

SQL Interview Prep - DAY 1

Tanmay Modh's photo
Tanmay Modh
·

2 min read

Table of contents

🏏 Building a Dynamic Points Table for an ICC Cricket Tournament Using SQL

🚀 Problem Statement

Imagine you're managing data for an ICC Cricket Tournament, where teams compete fiercely in a series of One-Day International (ODI) matches. Your task? To create a that updates seamlessly based on match results. This table should display key performance metrics for each team, such as:

  • Team Name

  • Matches Played

  • Wins

  • Losses

  • Total Points

  • Rankings

💡 The Challenge

To solve this problem, you need to address the following:

  1. Aggregate Match Data: Extract and summarize match results for each team.

  2. Calculate Points Dynamically: Assign points based on match outcomes (e.g., 2 points for a win, 0 for a loss).

  3. Rank Teams: Sort teams in descending order of their total points to determine their standings.

  4. Handle Edge Cases: Ensure the solution accounts for tied matches, no results, or incomplete data.

INPUT

Use the below queries to create the INPUTT table.

Create Table icc_world_cup:

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

Insert records into the table created:

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');

Final Input Table:

Expected Output:

Solution

Approach

First we will take the UNION ALL along with CASE statements to include TEAM_1 and TEAM_2 and it will be a subquery. Next we will use Aggregations functions to count total matches played, won, lost and calculate points.

Solution Query using SubQuery:

select team_name, 
count(*) as no_of_mathes_played, 
SUM(win_flag) as no_of_matches_won, 
count(*) - SUM(win_flag) as no_of_losses, 
sum(win_flag) * 2 as points 
from 
(select team_1 as team_name, CASE WHEN team_1 = Winner THEN 1 ELSE 0 end as win_flag 
    from icc_world_cup
    union all
    select team_2 as team_name, CASE WHEN team_2 = Winner THEN 1 ELSE 0 end as win_flag
    from icc_world_cup
) union_table_columns
group by team_name
order by points desc;

Solution Query using CTE:


with cte as 
(select team_1 as team_name, CASE WHEN team_1 = Winner THEN 1 ELSE 0 end as win_flag,
    CASE WHEN Winner='Draw' then 1 else 0 end as draw_flag
    from icc_world_cup
    union all
    select team_2 as team_name, CASE WHEN team_2 = Winner THEN 1 ELSE 0 end as win_flag,
    CASE WHEN Winner='Draw' then 1 else 0 end as draw_flag
    from icc_world_cup
)
select team_name, count(*) as no_of_mathes_played, SUM(win_flag) as no_of_matches_won, 
count(*) - SUM(win_flag) as no_of_losses, sum(win_flag) * 2 as points,
sum(draw_flag) as no_of_draws 
from cte
group by team_name
order by points desc;

Thank you for reading the article.

Reference: https://www.youtube.com/watch?v=qyAgWL066Vo&list=PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb&ab_channel=AnkitBansal

SQLinterview