IBM Backend Developer Test 2024 | Previous Year Questions & Answers

In This Article We Are Solving IBM Backend Developer Test 2024 Questions From Previous Year Examination Along With That You Will Get Solutions And Explanation.

Question #1 – IBM Backend Developer Test 2024

Write a query to select the names of the customers who live in New York and have ordered a product with a total cost of more than $100 (Total cost = Price + Shipping).

Table Schema:

Orders

Products

Customers

Sample Output:

Answer In My SQL

SELECT c.CustomerName AS Customer
FROM Customers c
JOIN Orders o ON c.Customerid = o.Customerid
WHERE c.City = 'New York' AND (o.Price + o.Shipping) > 100;

Explanation – IBM Backend Developer Test 2024

Join Tables:

• We need information from both the Customers and Orders tables. We use an INNER JOIN to combine these tables on the Customerid column, which is common to both tables.

Filter by City:

• We filter the combined results to include only those customers who live in New York using the condition c.City = 'New York'.

Filter by Total Cost:

• We further filter the results to include only those orders where the total cost (Price + Shipping) is greater than $100 using the condition (o.Price + o.Shipping) > 100.

Select the Required Column:

• Finally, we select the CustomerName from the Customers table.

Given the provided data, only Jack (Customerid 101) meets the criteria of living in New York and having an order with a total cost greater than $100 (70 + 50 = 120). Therefore, the output will be:

Output:

Problem: IBM Backend Developer Test 2024

Objective: Write a SQL query to fetch the number of wins for the years 2021, 2022, and 2023, for teams that:

1. Achieved a minimum of 2 wins in both 2021 and 2022.
2. Failed to secure at least 2 wins in 2023.

Expected output schema:

• team: Name of the team.
• year: Year of the win count (2021, 2022, or 2023).
• no_of_wins: Number of wins in that year.

Database structure:

• Table name: team_match_results
• Columns:
• team: VARCHAR(20) – Name of the team.
• match_id: INTEGER – Unique identifier for each match.
• year: INTEGER – Year the match was held.
• winning_status: VARCHAR(20) – Status showing whether the team “Won” or “Lost” the match.

Solution In My SQL:

Here is the SQL query to achieve this:

WITH yearly_wins AS (
    SELECT
        team,
        year,
        COUNT(*) AS no_of_wins
    FROM
        team_match_results
    WHERE
        winning_status = 'Won' AND year IN (2021, 2022, 2023)
    GROUP BY
        team, year
),
teams_with_min_wins AS (
    SELECT
        team
    FROM
        yearly_wins
    GROUP BY
        team
    HAVING
        SUM(CASE WHEN year = 2021 AND no_of_wins >= 2 THEN 1 ELSE 0 END) > 0
        AND SUM(CASE WHEN year = 2022 AND no_of_wins >= 2 THEN 1 ELSE 0 END) > 0
        AND SUM(CASE WHEN year = 2023 AND no_of_wins >= 2 THEN 1 ELSE 0 END) = 0
)
SELECT
    y.team,
    y.year,
    y.no_of_wins
FROM
    yearly_wins y
JOIN
    teams_with_min_wins t ON y.team = t.team
ORDER BY
    y.team, y.year;

Explanation:

CTE yearly_wins: This Common Table Expression calculates the number of wins for each team for the years 2021, 2022, and 2023.

• It filters the rows where winning_status is ‘Won’ and the year is one of 2021, 2022, or 2023.
• It groups the results by team and year and counts the number of wins (no_of_wins).

CTE teams_with_min_wins: This Common Table Expression filters the teams that meet the specific win criteria:

• Ensures the team has at least 2 wins in both 2021 and 2022.
• Ensures the team has less than 2 wins in 2023.
• Uses HAVING to apply these conditions with SUM and CASE statements.

Final Select: This part joins the yearly_wins CTE with the teams_with_min_wins CTE to get the relevant teams and their win counts.

• It orders the results by team and year in ascending order.

Sample Data and Expected Output: IBM Backend Developer Test 2024

Assume the team_match_results table has the following data:

team    match_id  year  winning_status
------  --------  ----  --------------
Team A  1         2021  Won
Team A  2         2021  Won
Team A  3         2021  Lost
Team A  4         2022  Won
Team A  5         2022  Won
Team A  6         2023  Won
Team A  7         2023  Lost
Team B  8         2021  Won
Team B  9         2021  Won
Team B  10        2022  Won
Team B  11        2022  Won
Team B  12        2023  Lost
Team B  13        2023  Lost

Expected output:

team    year  no_of_wins
------  ----  ----------
Team A  2021  2
Team A  2022  2
Team A  2023  1
Team B  2021  2
Team B  2022  2
Team B  2023  0

In this example, both Team A and Team B have at least 2 wins in 2021 and 2022, and fewer than 2 wins in 2023, thus meeting the criteria specified in the problem.

Problem Understanding: IBM Backend Developer Test 2024

An ISP needs a query to generate a report for billing purposes. The report should list each client’s MAC address along with their total traffic and the corresponding cost for the current billing period, which is defined as May 2022. Each client has a unique tariff rate per megabyte of data consumed. The result should be sorted in descending order based on the total cost.

Schema: IBM Backend Developer Test 2024

clients table:

• id (SMALLINT): Client ID
• mac (VARCHAR(17)): MAC address of the client
• tariff (DECIMAL(6,5)): Tariff rate per MB for the client

traffic table:

• client_id (SMALLINT): Client ID (foreign key referencing clients.id)
• dt (VARCHAR(19)): Date of the traffic record
• amount (INT): Amount of traffic in megabytes

Sample Data: IBM Backend Developer Test 2024

clients table:

| id | mac               | tariff  |
|----|-------------------|---------|
| 1  | A2-53-FC-0C-3E-B4 | 0.00007 |
| 2  | DC-80-42-E9-AE-FC | 0.00003 |
| 3  | 3F-9B-A9-2A-B1-7B | 0.00007 |
| 4  | D4-6F-E4-AF-47-D5 | 0.00004 |
| 5  | B9-65-FC-8E-F0-15 | 0.00007 |

traffic table:

| client_id | dt         | amount |
|-----------|------------|--------|
| 1         | 2022-05-22 | 9127   |
| 1         | 2022-06-07 | 62203  |
| 1         | 2022-06-10 | 88227  |
| 2         | 2022-05-31 | 99874  |

Expected Output: IBM Backend Developer Test 2024

| mac               | traffic | cost  |
|-------------------|---------|-------|
| B9-65-FC-8E-F0-15 | 147927  | 10.35 |
| D4-6F-E4-AF-47-D5 | 93743   | 3.75  |
| DC-80-42-E9-AE-FC | 99874   | 3.00  |
| A2-53-FC-0C-3E-B4 | 9127    | 0.64  |
| 3F-9B-A9-2A-B1-7B | 8386    | 0.59  |

SQL Query Explanation: IBM Backend Developer Test 2024

SELECT 
    c.mac,
    SUM(t.amount) AS traffic,
    ROUND(SUM(t.amount) * c.tariff, 2) AS cost
FROM 
    clients c
JOIN 
    traffic t ON c.id = t.client_id
WHERE 
    t.dt LIKE '2022-05%'
GROUP BY 
    c.mac
ORDER BY 
    cost DESC;

SELECT Clause:

• c.mac: Selects the MAC address from the clients table.
• SUM(t.amount) AS traffic: Calculates the total traffic (sum of amount from traffic table) for each client.
• ROUND(SUM(t.amount) * c.tariff, 2) AS cost: Computes the total cost by multiplying the total traffic by the client’s tariff rate (tariff column in clients table), rounded to two decimal places.

FROM Clause:

• clients c: Aliases the clients table as c.
• JOIN traffic t ON c.id = t.client_id: Joins clients and traffic tables using client_id from traffic and id from clients.

WHERE Clause:

• t.dt LIKE '2022-05%': Filters records from traffic table for May 2022 (dt column starts with ‘2022-05’).

GROUP BY Clause:

• c.mac: Groups the results by mac address to aggregate traffic and cost calculations per client.

ORDER BY Clause:

• cost DESC: Sorts the result set in descending order based on the calculated cost.

Summary: IBM Backend Developer Test 2024

This SQL query effectively retrieves the required information from the database, calculates the total traffic and billing cost for each client in May 2022 based on their tariff rate, and presents the results sorted by cost in descending order. It efficiently uses SQL aggregation functions (SUM), arithmetic operations (ROUND), joins (JOIN), and filtering (WHERE) to achieve the desired outcome.

Problem Statement: IBM Backend Developer Test 2024

A shipping company needs a query to generate a report listing all customer packages that are in the process of being delivered. The report should include:

• Customer email address (email)
• Total number of packages (total_packages) for each customer
• Total weight of all packages (total_weight) for each customer

The result should be sorted in descending order by the total weight of packages.

Schema:

Customers Table:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(255)
);

Packages Table:

CREATE TABLE packages (
    id INT PRIMARY KEY,
    status VARCHAR(50),
    weight DECIMAL(10, 2)
);

Customer_Packages Table:

CREATE TABLE customer_packages (
    customer_id INT,
    package_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (package_id) REFERENCES packages(id)
);

Sample Data: IBM Backend Developer Test 2024

Customers Table Data:

INSERT INTO customers (id, email) VALUES
(1, 'mail@gmail.com'),
(2, 'another@email.com');

Packages Table Data:

INSERT INTO packages (id, status, weight) VALUES
(1, 'delivered', 93.37),
(2, 'created', 86.16),
(3, 'delivered', 66.24),
(4, 'delivered', 78.63),
(5, 'onhold', 78.63),
(6, 'cancelled', 75.63),
(7, 'created', 74.63),
(8, 'shipped', 76.63);

Customer_Packages Table Data (Associations):

INSERT INTO customer_packages (customer_id, package_id) VALUES
(1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
(2, 6), (2, 7), (2, 8);

SQL Solution: IBM Backend Developer Test 2024

SELECT
    c.email AS email,
    COUNT(cp.package_id) AS total_packages,
    SUM(p.weight) AS total_weight
FROM
    customers c
JOIN
    customer_packages cp ON c.id = cp.customer_id
JOIN
    packages p ON cp.package_id = p.id
WHERE
    p.status IN ('created', 'shipped', 'onhold')
GROUP BY
    c.email
ORDER BY
    total_weight DESC;

Explanation: IBM Backend Developer Test 2024

Joining Tables:

• We start by joining the customers table (c) with the customer_packages table (cp) using customer_id.

Further Join:

• We then join the packages table (p) using package_id from customer_packages.

Filtering Data:

• The WHERE clause filters packages based on their status (created, shipped, onhold), ensuring we only include packages that are in the process of being delivered.

Aggregation:

• Using COUNT(cp.package_id), we count the number of packages (total_packages) associated with each customer.
• Using SUM(p.weight), we calculate the total weight (total_weight) of all packages associated with each customer.

Grouping:

• We group the results by c.email to ensure each row in the result set corresponds to a unique customer email address.

Sorting:

• Finally, we sort the result set in descending order (ORDER BY total_weight DESC) based on the total weight of packages.

This SQL query will generate a report listing each customer’s email, the total number of packages they have, and the total weight of those packages, sorted by the total weight in descending order.

Question: IBM Backend Developer Test 2024

While developing a dashboard for a large cloud hosting company, you need to generate a report listing Cloud Virtual Machine (CVM) configurations along with their total number of deployments in the year 2021. Write an SQL query to achieve this.

Answer:

SELECT
    c.name AS configuration,
    COUNT(d.configuration_id) AS deployments
FROM
    configurations c
JOIN
    deployments d ON c.id = d.configuration_id
WHERE
    d.dt >= '2021-01-01' AND d.dt < '2022-01-01'
GROUP BY
    c.name
ORDER BY
    deployments DESC;

Explanation:

SELECT Statement:

• c.name AS configuration: Selects the name of the configuration from the configurations table, aliased as configuration.
• COUNT(d.configuration_id) AS deployments: Counts the number of deployments by counting occurrences of configuration_id in the deployments table, aliased as deployments.

FROM Clause:

• configurations c: Specifies the configurations table with alias c to be used in the query.
• JOIN deployments d ON c.id = d.configuration_id: Joins the configurations and deployments tables based on configuration_id, ensuring that only matching rows are included.

WHERE Clause:

• d.dt >= '2021-01-01' AND d.dt < '2022-01-01': Filters deployments to include only those that occurred in the year 2021. This range ensures that deployments from January 1, 2021, to December 31, 2021, are considered.

GROUP BY Clause:

• c.name: Groups the result set by the name column of configurations. This ensures that each unique configuration is represented only once in the result set.

ORDER BY Clause:

• deployments DESC: Orders the result set in descending order based on the deployments count. Configurations with more deployments appear first.

This query efficiently retrieves the desired information by joining relevant tables, applying appropriate filters, aggregating data using COUNT, and sorting the results as per the specified requirements.

Home

Subscribe On Youtube