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.