Answer for Hive Assignment

Hive-Assignment.txt

1. SELECT SUM(ConsumerCount) FROM(SELECT bc.BeverageName, bc.ConsumerCount FROM Beverages_Consumers_Internal AS bc RIGHT OUTER JOIN Beverages_Branches_Internal AS bb ON (bc.BeverageName=bb.BeverageName AND bb.BranchName='Branch1')) totalConsumer; 


2. SELECT DISTINCT bc.BeverageName, SUM(bc.ConsumerCount) OVER (PARTITION BY bc.BeverageName ORDER BY bc.BeverageName ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS totalCount FROM Beverages_Consumers_Internal AS bc RIGHT OUTER JOIN Beverages_Branches_Internal AS bb ON bc.BeverageName=bb.BeverageName WHERE bb.BranchName='Branch1' ORDER BY totalCount DESC LIMIT 10; 

3. SELECT DISTINCT bb.BranchName, bc.BeverageName, SUM(bc.ConsumerCount) OVER (PARTITION BY bc.BeverageName ORDER BY bc.BeverageName ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS totalCount FROM Beverages_Consumers_Internal AS bc RIGHT OUTER JOIN Beverages_Branches_Internal AS bb ON bc.BeverageName=bb.BeverageName WHERE bb.BranchName IN ('Branch1', 'Branch2') ORDER BY totalCount DESC LIMIT 2; 

4. SELECT DISTINCT bb.BranchName, bc.BeverageName FROM Beverages_Consumers_Internal AS bc RIGHT OUTER JOIN Beverages_Branches_Internal AS bb ON bc.BeverageName=bb.BeverageName WHERE bb.BranchName IN ('Branch1', 'Branch8', 'Branch10') ORDER BY bb.BranchName;