GROUP and JOIN operations in Hadoop - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a GROUP BY operation in Hive

Given the following Hive query on a sales table, what is the output?

Hadoop

SELECT product_id, COUNT(*) AS total_sales FROM sales GROUP BY product_id;

A [{'product_id': 'P1', 'total_sales': 3}, {'product_id': 'P2', 'total_sales': 4}]

B [{'product_id': 'P1', 'total_sales': 5}, {'product_id': 'P2', 'total_sales': 2}]

C [{'product_id': 'P1', 'total_sales': 3}, {'product_id': 'P2', 'total_sales': 2}]

D [{'product_id': 'P1', 'total_sales': 2}, {'product_id': 'P2', 'total_sales': 2}]

💡 Hint

Count how many times each product_id appears in the sales table.

The GROUP BY groups rows by product_id, and COUNT(*) counts rows per group. The correct counts match option C.

❓ data_output

intermediate

Result of INNER JOIN between two Hive tables

What is the result of this Hive query joining customers and orders on customer_id?

Hadoop

SELECT c.customer_id, c.name, o.order_id FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id;

A [{'customer_id': 1, 'name': 'Alice', 'order_id': 101}, {'customer_id': 2, 'name': 'Bob', 'order_id': 102}, {'customer_id': 3, 'name': 'Charlie', 'order_id': 103}]

B [{'customer_id': 1, 'name': 'Alice', 'order_id': 101}, {'customer_id': 3, 'name': 'Charlie', 'order_id': 103}]

C [{'customer_id': 2, 'name': 'Bob', 'order_id': 102}, {'customer_id': 3, 'name': 'Charlie', 'order_id': 103}]

D [{'customer_id': 1, 'name': 'Alice', 'order_id': 101}, {'customer_id': 2, 'name': 'Bob', 'order_id': 102}]

💡 Hint

INNER JOIN returns rows with matching customer_id in both tables.

Only customers with matching orders appear. Option A correctly shows customers 1, 2, and 3 with their orders.

🔧 Debug

advanced

Identify the error in this Hive JOIN query

What error does this Hive query produce?

Hadoop

SELECT c.customer_id, o.order_id FROM customers c JOIN orders o WHERE c.customer_id = o.customer_id;

A No error, query runs successfully

B SyntaxError: Missing ON clause in JOIN

C RuntimeError: Table not found

D SemanticError: Ambiguous column reference

💡 Hint

JOIN requires ON clause to specify join condition.

Hive JOIN syntax requires ON clause. Using WHERE instead causes syntax error.

🚀 Application

advanced

Choosing the right JOIN type for data analysis

You want to find all customers and their orders, including customers with no orders. Which JOIN type should you use in Hive?

A FULL OUTER JOIN

B INNER JOIN

C RIGHT OUTER JOIN

D LEFT OUTER JOIN

💡 Hint

Think about including all customers even if they have no matching orders.

LEFT OUTER JOIN returns all rows from left table (customers) and matching rows from right (orders).

🧠 Conceptual

expert

Effect of GROUP BY with multiple columns in Hive

What is the effect of this Hive query grouping by two columns?

Hadoop

SELECT product_id, store_id, SUM(quantity) FROM sales GROUP BY product_id, store_id;

A Aggregates quantity for each unique product and store combination

B Aggregates quantity for each store across all products

C Aggregates quantity for each product across all stores

D Aggregates quantity for all products and stores combined

💡 Hint

GROUP BY multiple columns groups by unique pairs of those columns.

Grouping by product_id and store_id creates groups for each unique pair, summing quantity per pair.

Practice

1. What does the GROUP BY operation do in Hadoop data processing?

easy

A. It organizes data by keys to summarize or analyze groups.

B. It merges two datasets based on matching keys.

C. It deletes duplicate records from a dataset.

D. It sorts data in ascending order.

Solution

1. Step 1: Understand the purpose of GROUP BY

   The GROUP BY operation groups rows that have the same values in specified columns into summary rows.

2. Step 2: Differentiate from JOIN

   JOIN merges datasets based on keys, but GROUP BY organizes data within one dataset by keys.

3. Final Answer:

   It organizes data by keys to summarize or analyze groups. -> Option A

4. Quick Check:

   GROUP BY = Organize by keys [OK]

Hint: GROUP BY groups data by keys for summaries [OK]

Common Mistakes:

• Confusing GROUP BY with JOIN
• Thinking GROUP BY deletes duplicates
• Assuming GROUP BY sorts data

2. Which of the following is the correct syntax to join two datasets table1 and table2 on the key id in HiveQL (Hadoop SQL)?

easy

A. SELECT * FROM table1 GROUP BY table2.id;

B. SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;

C. SELECT * FROM table1 WHERE table1.id = table2.id;

D. SELECT * FROM table1, table2 ON table1.id = table2.id;

Solution

1. Step 1: Identify correct JOIN syntax

   The standard JOIN syntax in HiveQL uses ON to specify the join condition: JOIN table2 ON table1.id = table2.id.

2. Step 2: Eliminate incorrect options

   SELECT * FROM table1 WHERE table1.id = table2.id; uses WHERE incorrectly for join; SELECT * FROM table1 GROUP BY table2.id; uses GROUP BY wrongly; SELECT * FROM table1, table2 ON table1.id = table2.id; incorrectly uses ON with comma-separated tables.

3. Final Answer:

   SELECT * FROM table1 JOIN table2 ON table1.id = table2.id; -> Option B

4. Quick Check:

   JOIN with ON condition = SELECT * FROM table1 JOIN table2 ON table1.id = table2.id; [OK]

Hint: Use JOIN ... ON for correct Hadoop SQL joins [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN
• Confusing GROUP BY with JOIN
• Using ON clause with old-style comma join

3. Given two datasets in Hive:
table1(id INT, value INT)
table2(id INT, category STRING)
What is the output of this query?

SELECT t2.category, SUM(t1.value) FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id GROUP BY t2.category;

medium

A. Sum of values grouped by id from table1.

B. List of categories with no aggregation.

C. Sum of values grouped by category from table2.

D. Error due to missing GROUP BY column.

Solution

1. Step 1: Understand JOIN and GROUP BY

   The JOIN merges rows from table1 and table2 where ids match. Then GROUP BY groups results by category.

2. Step 2: Analyze aggregation

   SUM(t1.value) calculates total value per category from joined data.

3. Final Answer:

   Sum of values grouped by category from table2. -> Option C

4. Quick Check:

   JOIN + GROUP BY category = sum per category [OK]

Hint: JOIN then GROUP BY aggregates data by joined keys [OK]

Common Mistakes:

• Ignoring GROUP BY column in SELECT
• Confusing grouping by id vs category
• Expecting list without aggregation

4. You wrote this Hive query:

SELECT t1.id, COUNT(*) FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id;

But it gives an error. What is the likely cause?

medium

A. Missing GROUP BY clause for aggregation.

B. Incorrect JOIN syntax.

C. COUNT(*) cannot be used with JOIN.

D. Table aliases are not allowed.

Solution

1. Step 1: Identify aggregation usage

   COUNT(*) is an aggregate function and requires GROUP BY for non-aggregated columns in SELECT.

2. Step 2: Check query structure

   Since t1.id is selected without aggregation, GROUP BY t1.id is needed.

3. Final Answer:

   Missing GROUP BY clause for aggregation. -> Option A

4. Quick Check:

   Aggregate needs GROUP BY [OK]

Hint: Use GROUP BY when selecting non-aggregated columns with COUNT [OK]

Common Mistakes:

• Forgetting GROUP BY with aggregate functions
• Thinking JOIN syntax is wrong
• Assuming COUNT(*) disallowed with JOIN

5. You have two large datasets:
sales(order_id, product_id, amount)
products(product_id, category)
You want to find total sales amount per category but only for categories with total sales above 1000. Which Hive query correctly achieves this?

hard

A. SELECT p.category, SUM(s.amount) FROM sales s JOIN products p ON s.product_id = p.product_id HAVING SUM(s.amount) > 1000;

B. SELECT p.category, SUM(s.amount) FROM sales s JOIN products p ON s.product_id = p.product_id WHERE SUM(s.amount) > 1000 GROUP BY p.category;

C. SELECT p.category, SUM(s.amount) FROM sales s JOIN products p ON s.product_id = p.product_id GROUP BY p.category WHERE SUM(s.amount) > 1000;

D. SELECT p.category, SUM(s.amount) FROM sales s JOIN products p ON s.product_id = p.product_id GROUP BY p.category HAVING SUM(s.amount) > 1000;

Solution

1. Step 1: Join sales and products on product_id

   This merges sales with product categories.

2. Step 2: Group by category and sum amounts

   GROUP BY p.category with SUM(s.amount) calculates total sales per category.

3. Step 3: Filter groups with HAVING

   HAVING filters groups after aggregation; WHERE cannot filter aggregated sums.

4. Final Answer:

   SELECT p.category, SUM(s.amount) FROM sales s JOIN products p ON s.product_id = p.product_id GROUP BY p.category HAVING SUM(s.amount) > 1000; -> Option D

5. Quick Check:

   Use HAVING to filter aggregated groups [OK]

Hint: Use HAVING to filter after GROUP BY aggregation [OK]

Common Mistakes:

• Using WHERE instead of HAVING for aggregation filter
• Omitting GROUP BY when aggregating
• Incorrect JOIN condition