Fun With Data: A Scavenger Hunt

  • 26 July 2016
  • 2 replies
  • 846 views
Fun With Data: A Scavenger Hunt
Userlevel 6
Badge

A Looker Scavenger Hunt


One of the best ways of learning to use Looker is to try to puzzle out some answers.


We’ll start by exploring a simple table of all of the airports in the USA.


Start Exploring from Here, hit Command-K to clear the surface.


Using Measures (hint: just add a measure)


The first thing we are going to learn about is measures. Measures are computations about a set of things. In this first example, we are going to compute measure against the entire set of airports (facilities, actually, there are heliports, gliderports and more in this data set).


To answer each of the following questions you will only need to use only a single measure.




Question: How many facilities are there in the airports table (figure it out here)?


Answer (click to reveal): Most of the time, exploring data is about counting things. Find the ‘Count’ measure which adds it to the table, then hit the ‘Run’ button.

Run the Query




Question: How many facilities (airports) have control towers (figure it out here)?


Answer (click to reveal): Click on the ‘With Control Tower Count’ and run the query. Sometimes measures are filtered. In this case the measure only counts facilities with control towers. Run the Query




Question: What is the average elevation of all the of airports? (figure it out here)?


Answer (click to reveal): Click on the measure Average Elevation and run the query. This measure looks at the elevation of each of the facilities and computes and average Run the Query




Question: What is the highest elevation in of all of the airports? (figure it out here)?


Answer (click to reveal): Click on the measure *Max Elevation * and run the query. This measure looks at each of the elevations of each of the rows in the table and returns the highest one Run the Query




Question: What is the lowest elevation of all of the airports? (figure it out here)?


Answer (click to reveal): Click on the measure *Min Elevation * and run the query. This measure looks at each of the elevations of each of the rows in the table and returns the lowest one Run the Query




Using Filters


In the first set of questions, we added a simple measure and ran a query and got our answers. Often you will want to compute these measures against a subset of the data. We can add filters so the measure computations only work against some of the data. For all these answers, you will pick a measure, like we did in the first set, but also we’ll be adding a filter to limit the data, the computations work against.


How many facilities (airports of all types) are there in TX?


How many facilities are there in CA?


What is the maximum elevation of all of the airports in CA?


How many facilities are there on the west coast (CA or WA or OR)?


How many of the facilities are of type ‘AIRPORT’?


What is the maximum elevation of facilities of type ‘HELIPORT’?


How many facilities of type HELIPORT have are facilities with control towers?


How many facilities are there in SANTA CRUZ, CA (you will have to set two filters)?


Dimensions and Measures and sorting


Which state has the most facilities?


Of which type of facility are there the most?


Which state has the highest average elevation for its facilities?


Which facility type has the lowest average elevation?


Putting it all together, using Dimensions, Measures and Sorting


What city in TX has the most heliports?


Which city in NY has the most airports?


How many heliports have control towers?


Where is the highest airport?


Which state has the heliport at the highest elevation?


Are there any cities with two or more airports with control towers? Which has the most? What are their names?


Now lets explore Flights (start exploring from Here)


How many flights are in this data set?


What carrier has the most flights?


What airport has the most flights?


Which day of week generally has the least flights?


What airplane manufacturer has the most flights?


Which year(s) are the data from?


What is the busiest airport in CA, in terms of flights landing?


Which carrier flies the most to Phoenix?


Where do most flights that arrive in LAX come from?


Given that there are 3 airports in NY (jfk, lga, ewr), which carriers fly the most to which NY airports?


Which year was the busiest in terms of flights?


Which aircraft are used most commonly by Southwest?


Which particular aircraft has logged the most flights (aircraft tail numbers are constant)?


Which aircraft has flown for the most different carriers?


Which carriers existed at the beginning of the dataset but no longer exist?


2 replies

Which aircraft model are used most commonly by Southwest? how to figure out the solution for this question

Userlevel 6
Badge

If you filter flights on "Southwest’ and group by manufacturer and model measuring flights and aircraft count, you can get a pretty good idea.



https://trainembed.corp.looker.com/embed/explore/faa/flights?qid=Hyyft0bWOTlicI54VGZLJk&toggle=fil

Reply