Hive Bike Share Assignment Passed

1. Utilizing the Bay Area Bike Share database (both Year 1 & 2, Aug. 2013- Aug. 2015)- what is the most popular start station based on trip data?
  1. Embarcadero at Sansome
  2. Market at 4th
  3. San Francisco Caltrain
  4. Townsend at 7th
-- Run this in HIVE Editor --
SELECT startterminal, startstation, COUNT(1) AS count 
FROM bay_area_bike_share
GROUP BY startterminal, startstation 
ORDER BY count DESC LIMIT 10

2. Utilizing the Bay Area Bike Share database (Year 1 only, Aug. 2013- Feb 2014) - Which is the least popular(least used) start station in the Bike share trips data?
(Hint: Use the count of start station, group and order in ascending order)
  1. Townsend at 7th
  2. Mezes Park
  3. Market at 4th
  4. Embarcadero at Sansome
-- Run this in HIVE Editor --
SELECT startstation, COUNT(1) AS count 
FROM bay_area_bike_share
GROUP BY startstation 
ORDER BY count ASC LIMIT 10


3. Utilizing the Bay Area Bike Share database (for Year 1 only, Aug. 2013 - Aug. 2014 only) - what is the SECOND MOST popular end station based on trip data?
(Hint: Use the count of end station, group and order in descending order)

  1. Steuart at Market
  2. Market at Sansome
  3. Embarcadero at Sansome
  4. Harry Bridges Plaza(Ferry Building)
-- Run this in HIVE Editor --
SELECT endstation, COUNT(1) AS count 
FROM bay_area_bike_share
GROUP BY endstation 
ORDER BY count DESC LIMIT 10

Comments

  1. Unknown4 May 2016 at 01:59

    Hi Vinay, hoping you can help! With regards to the Bike Sharing Assignment.... " Utilizing the Bay Area Bike Share database (both Year 1 & 2, Aug. 2013- Aug. 2015)- what is the most popular start station based on trip data?

    Can you tell me how you joined both Year 1 & 2 to query at the same time? Your table is "bay_area_bike_share". Does this table contain both Year 1 and 2 data?"

    Thanks!

    ReplyDelete
    Replies
    1. J21 May 2016 at 00:51

      Not sure if your still wondering about this but you should be able to do it with the command UNION....

      SELECT t.startstation, COUNT(t.startstation) AS c
      FROM (
      SELECT startstation FROM 201402_tripdata
      UNION ALL
      SELECT startstation FROM 201408_tripdata
      UNION ALL
      SELECT startstation FROM 201508_tripdata
      ) t
      GROUP BY t.startstation
      ORDER BY c DESC
      LIMIT 10

      Q3 uses both Dataset 1 and 2
      Q2 looks at only the first dataset
      Q1 looks at all 3 data sets.

      At least that is how I read it.

      Delete

Post a Comment

Popular posts from this blog

Java Practice Questions MCQ

1. What is the output? class point1 { static void a (int x, int y) { this.x=x; //non-static variable this cannot be referenced from a static context this.y=y; System.out.println(x+ “,”+y); } } class point { public static void main (String[] args) { point1 pp=new point1(); pp.a(4,3); } } a. 4 ,3 b. 3 ,4 c. Compile Error d. None of the above ------------------------------------------------------------------------------------------------------- 2. Local variables cannot be declared as final.(True / False) .... False ----------------------------------------------------------------------------------------------------- 3. What is the output? class A { A(int x, int y) { this.x=x; this.y=y; } A() { this(2,3); } public static void main(String[] args) { A a1 = new A(); System.out.println(“x=”+a1.x+”,y=”+a1.y); // cannot find symbol x and y } } a. x=2 y=3 b. x=-1...
Read more

Linux Assignments - Practise

UNIX ASSIGNMENTS: Day 1 Concept : Basic commands in UNIX, Filters, Pipes Objective : Participant will be able to: ·          Execute Basic Unix commands ·          Implement the concepts of Pipes and Filters ·          Work with vi editor Problems: Section 1: 1.        List all the files and sub directories of the directory /bin. 2.        List all the files including hidden files in your current directory. 3.        List all the files starting with letter ‘r’ in your current directory. 4.        List all the files having three characters in their names, from your current directory. 5.        List all the files with extension ‘.doc’ in your current directory. 6.     ...
Read more

HackerRank Introduction Challenges - Java

HackerRank Introduction Challenges - Java Welcome to Java! Problem Statement Welcome to the world of Java! Just print "Hello World." and "Hello Java." in two separate lines to complete this challenge. The code stub in the editor already creates the main function and solution class. All you have to do is copy and paste the following lines inside the main function. System.out.println("Hello World."); System.out.println("Hello Java."); Sample Output Hello World. Hello Java. Download Code Here Java Stdin and Stdout 1 Problem Statement In most of the Hackerrank challenges, you need to read input from stdin (standard input) and write your output in stdout (standard output). One way to take input from stdin is to use Scanner class and reading from System.in. You can write your output to stdout simply using System.out.printf function. In this problem you need to read 3 integers from stdin and print them ...
Read more