Created
April 16, 2019 02:23
Answer for Hive Assignment
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment