Fisseha Berhane, PhD

Data Scientist

443-970-2353 fisseha@jhu.edu CV Resume Linkedin GitHub twitter twitter

MySQL Excercise 2

In [ ]:
%load_ext sql
%sql mysql://studentuser:studentpw@mysqlserver/dognitiondb
%sql USE dognitiondb

SELECT FROM WHERE

Let's say we want to know which Dognition customers received access to Dognition's first four tests for free. These customers have a 1 in the "free_start_user" column of the users table. The syntax you would use to select the data for these customers would be:

In [2]:
%%sql 
SELECT user_guid, free_start_user
FROM users
WHERE free_start_user=1
LIMIT 10;
10 rows affected.
Out[2]:
user_guid free_start_user
ce28a468-7144-11e5-ba71-058fbc01cf0b 1
ce28ac4c-7144-11e5-ba71-058fbc01cf0b 1
ce28acba-7144-11e5-ba71-058fbc01cf0b 1
ce28ad1e-7144-11e5-ba71-058fbc01cf0b 1
ce28ad82-7144-11e5-ba71-058fbc01cf0b 1
ce28b098-7144-11e5-ba71-058fbc01cf0b 1
ce28b1c4-7144-11e5-ba71-058fbc01cf0b 1
ce28b58e-7144-11e5-ba71-058fbc01cf0b 1
ce28b9bc-7144-11e5-ba71-058fbc01cf0b 1
ce28ba20-7144-11e5-ba71-058fbc01cf0b 1

Question 1 : How would you select the Dog IDs for the dogs in the Dognition data set that were DNA tested (these should have a 1 in the dna_tested field of the dogs table)? Try it below (if you do not limit your output, your query should output data from 1433 dogs):

In [3]:
%%sql
SELECT dog_guid
FROM dogs
WHERE dna_tested=1
LIMIT 10;
10 rows affected.
Out[3]:
dog_guid
fd27b6b4-7144-11e5-ba71-058fbc01cf0b
fd27cd98-7144-11e5-ba71-058fbc01cf0b
fd27ce1a-7144-11e5-ba71-058fbc01cf0b
fd27d144-7144-11e5-ba71-058fbc01cf0b
fd27d1c6-7144-11e5-ba71-058fbc01cf0b
fd27d9fa-7144-11e5-ba71-058fbc01cf0b
fd27dc52-7144-11e5-ba71-058fbc01cf0b
fd27e454-7144-11e5-ba71-058fbc01cf0b
fd27e9a4-7144-11e5-ba71-058fbc01cf0b
fd3cd40e-7144-11e5-ba71-058fbc01cf0b

The SELECT statement can be used to interact with all data types, and there are many operators and functions that allow you to interact with the data in different ways. Here are some resources that describe these operators and functions:

http://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
http://www.w3resource.com/mysql/mysql-functions-and-operators.php

Some of the most common operators include: =,<,>,<=, and >=. If you want to select something that is NOT a specific value, use != or <>. You can also use logical operators, such as AND and OR.

Let's start by examining how operators can be used with numerical data.

If you wanted to examine the Dog IDs of dogs who weighed between 10 and 50 pounds, you could query:

SELECT dog_guid, weight
FROM dogs
WHERE weight BETWEEN 10 AND 50;

The above query provided an example of how to use the BETWEEN operator (described in the links provided above), as well as an example of how AND can be used to specify multiple criteria. If you wanted to examine the Dog IDs of dogs who were "fixed" (neutered) OR DNA tested, you could use OR in the following query:

SELECT dog_guid, dog_fixed, dna_tested
FROM dogs
WHERE dog_fixed=1 OR dna_tested=1;

If you wanted to examine the Dog IDs of dogs who were fixed but NOT DNA tested, you could query:

SELECT dog_guid, dog_fixed, dna_tested
FROM dogs
WHERE dog_fixed=1 AND dna_tested!=1;

Question 2: How would you query the User IDs of customers who bought annual subscriptions, indicated by a "2" in the membership_type field of the users table? (If you do not limit the output of this query, your output should contain 4919 rows.)

In [4]:
%%sql 
SELECT user_guid
FROM users
WHERE membership_type=2
LIMIT 10;
10 rows affected.
Out[4]:
user_guid
ce134e42-7144-11e5-ba71-058fbc01cf0b
ce135e14-7144-11e5-ba71-058fbc01cf0b
ce135e14-7144-11e5-ba71-058fbc01cf0b
ce136ac6-7144-11e5-ba71-058fbc01cf0b
ce136c24-7144-11e5-ba71-058fbc01cf0b
ce136e36-7144-11e5-ba71-058fbc01cf0b
ce136ee0-7144-11e5-ba71-058fbc01cf0b
ce136f94-7144-11e5-ba71-058fbc01cf0b
ce134be0-7144-11e5-ba71-058fbc01cf0b
ce1371a6-7144-11e5-ba71-058fbc01cf0b

Now let's try using the WHERE statement to interact with text data (called "strings").

Strings need to be surrounded by quotation marks in SQL. MySQL accepts both double and single quotation marks, but some database systems only accept single quotation marks. Whenever a string contains an SQL keyword, the string must be enclosed in backticks instead of quotation marks.

'the marks that surrounds this phrase are single quotation marks'
"the marks that surrounds this phrase are double quotation marks"
`the marks that surround this phrase are backticks`

Strings enclosed in quotation or backticks can be used with many of the same operators as numerical data. For example, imagine that you only wanted to look at data from dogs of the breed "Golden Retrievers." You could query (note that double quotation marks could have been used in this example is well):

SELECT dog_guid, breed
FROM dogs
WHERE breed='golden retriever';

The IN operator allows you to specify multiple values in a WHERE clause. Each of these values must be separated by a comma from the other values, and the entire list of values should be enclosed in parentheses. If you wanted to look at all the data from Golden Retrievers and Poodles, you could certainly use the OR operator, but the IN operator would be even more efficient (note that single quotation marks could have been used in this example, too):

SELECT dog_guid, breed
FROM dogs
WHERE breed IN ("golden retriever","poodle");

The LIKE operator allows you to specify a pattern that the textual data you query has to match. For example, if you wanted to look at all the data from breeds whose names started with "s", you could query:

SELECT dog_guid, breed
FROM dogs
WHERE breed LIKE ("s%");

In this syntax, the percent sign indicates a wild card. Wild cards represent unlimited numbers of missing letters. This is how the placement of the percent sign would affect the results of the query:

  • WHERE breed LIKE ("s%") = the breed must start with "s", but can have any number of letters after the "s"
  • WHERE breed LIKE ("%s") = the breed must end with "s", but can have any number of letters before the "s"
  • WHERE breed LIKE ("%s%") = the breed must contain an "s" somewhere in its name, but can have any number of letters before or after the "s"

Question 3: How would you query all the data from customers located in the state of North Carolina (abbreviated "NC") or New York (abbreviated "NY")? If you do not limit the output of this query, your output should contain 1333 rows.

In [5]:
%%sql
SELECT *
FROM users
WHERE state IN ("NC","NY")
LIMIT 10;
10 rows affected.
Out[5]:
sign_in_count created_at updated_at max_dogs membership_id subscribed exclude free_start_user last_active_at membership_type user_guid city state zip country utc_correction
181 2013-02-05 17:54:42 2015-01-28 20:51:49 13 2 1 1 0 None 2 ce135e14-7144-11e5-ba71-058fbc01cf0b Raleigh NC 27606 US -5
181 2013-02-05 17:54:42 2015-01-28 20:51:49 13 2 1 1 0 None 2 ce135e14-7144-11e5-ba71-058fbc01cf0b Raleigh NC 27606 US -5
65 2013-02-05 00:52:16 2015-01-28 20:51:49 3 2 1 None None None 2 ce134be0-7144-11e5-ba71-058fbc01cf0b Hillsborough NC 27278 US -5
7 2013-02-06 00:40:59 2015-01-28 20:51:50 1 2 1 None None None 2 ce1371a6-7144-11e5-ba71-058fbc01cf0b New York NY 10023 US -5
15 2013-02-06 14:13:42 2015-01-28 20:51:50 1 2 1 None None None 2 ce137c78-7144-11e5-ba71-058fbc01cf0b Durham NC 27713 US -5
181 2013-02-05 17:54:42 2015-01-28 20:51:49 13 2 1 1 0 None 2 ce135e14-7144-11e5-ba71-058fbc01cf0b Raleigh NC 27606 US -5
2 2013-02-06 19:50:16 2015-01-28 20:51:50 2 2 1 None None None 2 ce138722-7144-11e5-ba71-058fbc01cf0b Charlotte NC 27371 US -5
181 2013-02-05 17:54:42 2015-01-28 20:51:49 13 2 1 1 0 None 2 ce135e14-7144-11e5-ba71-058fbc01cf0b Raleigh NC 27606 US -5
4 2013-02-07 04:19:57 2015-01-28 20:51:50 2 1 0 None None 2014-06-23 20:42:36 1 ce13919a-7144-11e5-ba71-058fbc01cf0b Oakland NY 11730 US -5
2 2013-02-06 01:54:46 2015-01-28 20:51:50 1 1 0 None None None 1 ce137458-7144-11e5-ba71-058fbc01cf0b Durham NC 27707 US -5

Next, let's try using the WHERE statement to interact with datetime data. Time-related data is a little more complicated to work with than other types of data, because it must have a very specific format. MySQL comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY

One of the interesting things about time-related data is that SQL has commands to break the data into different "time parts" or "date parts" as described here:

http://www.tutorialspoint.com/mysql/mysql-date-time-functions.htm

A time stamp stored in one row of data might look like this:

2013-02-07 02:50:52

The year part of that entry would be 2013, the month part would be "02" or "February" (depending on the requested format), the seconds part would be "52", and so on. SQL functions easily allow you to convert those parts into formats you might need for specific analyses. For example, imagine you wanted to know how many tests Dognition customers complete on different days of the week. To complete this analysis, you would need to convert the time stamps of each completed test to a variable that outputted the correct day of the week for that date. DAYNAME is a function that will do this for you. You can combine DAYNAME with WHERE to select data from only a single day of the week:

SELECT dog_guid, created_at
FROM complete_tests
WHERE DAYNAME(created_at)="Tuesday"

You can also use common operators like =,<,>,<=,>=,!=, or <> with dates just like you would with other types of data, but whether you refer to the date as a number or text will depend on whether you are selecting individual date parts or treating the date/time entry as a single clause. For example, you could select all the Dog IDs and time stamps of tests completed after the 15 of every month with this command that extracts the "DAY" date part out of each time stamp:

SELECT dog_guid, created_at
FROM complete_tests
WHERE DAY(created_at) > 15

You could also select all the Dog IDs and time stamps of completed tests from after February 4, 2014 by treating date entries as text clauses with the following query:

SELECT dog_guid, created_at
FROM complete_tests
WHERE created_at > '2014-02-04'

Note that you have to use a different set of functions than you would use for regular numerical data to add or subtract time from any values in these datetime formats. For example, instead of using a minus sign to find the difference in time between two time stamps or dates, you would use the TIMEDIFF or DATEDIFF function. See the references provided above for a list of these functions.

Question 4: Now that you have seen how datetime data can be used to impose criteria on the data you select, how would you select all the Dog IDs and time stamps of Dognition tests completed before October 15, 2015 (your output should have 193,246 rows)?

In [7]:
%%sql

SELECT dog_guid,created_at
FROM complete_tests
WHERE  created_at < '2015-101-5'
LIMIT 10;
10 rows affected.
Out[7]:
dog_guid created_at
fd27b86c-7144-11e5-ba71-058fbc01cf0b 2013-02-05 18:26:54
fd27b86c-7144-11e5-ba71-058fbc01cf0b 2013-02-05 18:31:03
fd27b86c-7144-11e5-ba71-058fbc01cf0b 2013-02-05 18:32:04
fd27b86c-7144-11e5-ba71-058fbc01cf0b 2013-02-05 18:32:25
fd27b86c-7144-11e5-ba71-058fbc01cf0b 2013-02-05 18:32:56
fd27b86c-7144-11e5-ba71-058fbc01cf0b 2013-02-05 18:33:15
fd27b86c-7144-11e5-ba71-058fbc01cf0b 2013-02-05 18:33:33
fd27b86c-7144-11e5-ba71-058fbc01cf0b 2013-02-05 18:33:59
fd27b86c-7144-11e5-ba71-058fbc01cf0b 2013-02-05 18:34:25
fd27b86c-7144-11e5-ba71-058fbc01cf0b 2013-02-05 18:34:39

Last, let's use the WHERE statement in combination with two very important operators: IS NULL and IS NOT NULL. IS NULL will indicate rows of data that have null values. IS NOT NULL will indicate rows that do not have null values. We saw in previous exercises that many of the entries in the free_start_user field of the user table in the Dognition data set had NULL values. To select only the rows that have non-null data you could query:

SELECT user_guid
FROM users
WHERE free_start_user IS NOT NULL;

To select only the rows that have null data so that you can examine if these rows share something else in common, you could query:

SELECT user_guid
FROM users
WHERE free_start_user IS NULL;

Question 5: How would you select all the User IDs of customers who do not have null values in the State field of their demographic information (if you do not limit the output, you should get 17,985 from this query -- there are a lot of null values in the state field!)?

In [8]:
%%sql
SELECT membership_id
FROM users
WHERE state IS NOT NULL
LIMIT 10;
10 rows affected.
Out[8]:
membership_id
2
1
1
1
2
1
2
1
1
2
In [ ]:
%%sql 

SELECT dog_guid,subcategory_name,test_name
FROM complete_tests
WHERE YEAR(created_at)=2014
LIMIT 10;

Question 7: How would you select all of the User IDs of customers who have female dogs whose breed includes the word "terrier" somewhere in its name (if you don't limit your output, you should have 1771 rows in your output)?

In [9]:
%%sql
SELECT user_guid
FROM dogs
WHERE gender ="female" AND breed LIKE ("%terrier%")
LIMIT 10;
10 rows affected.
Out[9]:
user_guid
ce138722-7144-11e5-ba71-058fbc01cf0b
ce13b152-7144-11e5-ba71-058fbc01cf0b
ce21d7d2-7144-11e5-ba71-058fbc01cf0b
ce2202e8-7144-11e5-ba71-058fbc01cf0b
ce2203f6-7144-11e5-ba71-058fbc01cf0b
ce221774-7144-11e5-ba71-058fbc01cf0b
ce221a76-7144-11e5-ba71-058fbc01cf0b
ce22234a-7144-11e5-ba71-058fbc01cf0b
ce222fa2-7144-11e5-ba71-058fbc01cf0b
ce223628-7144-11e5-ba71-058fbc01cf0b

Question 8: How would you select the Dog ID, test name, and subcategory associated with each completed test for the first 100 tests entered in October, 2014?

In [11]:
%%sql
SELECT dog_guid,test_name,subcategory_name
FROM complete_tests
WHERE created_at LIKE ("2014-10-%")
LIMIT 100;
100 rows affected.
Out[11]:
dog_guid test_name subcategory_name
fd6a3480-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd6a3480-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Warm-up Reasoning
fd6a3480-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Game Reasoning
fd6a3480-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Warm-up Reasoning
fd6a3480-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Game Reasoning
fd6a2350-7144-11e5-ba71-058fbc01cf0b Memory versus Smell Memory
fd6924aa-7144-11e5-ba71-058fbc01cf0b Yawn Warm-up Empathy
fd6924aa-7144-11e5-ba71-058fbc01cf0b Yawn Game Empathy
fd6924aa-7144-11e5-ba71-058fbc01cf0b Eye Contact Warm-up Empathy
fd6924aa-7144-11e5-ba71-058fbc01cf0b Eye Contact Game Empathy
fd69acd6-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Warm-up Reasoning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Game Reasoning
fd69e75a-7144-11e5-ba71-058fbc01cf0b One Cup Warm-up Memory
fd69e75a-7144-11e5-ba71-058fbc01cf0b Two Cup Warm-up Memory
fd69e75a-7144-11e5-ba71-058fbc01cf0b Memory versus Pointing Memory
fd69d4f4-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd69e75a-7144-11e5-ba71-058fbc01cf0b Memory versus Smell Memory
fd69e75a-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd69e674-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd69e8a4-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd69e674-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd69e8a4-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Treat Warm-up Communication
fd69e674-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69e674-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69e8a4-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Arm Pointing Communication
fd69e8a4-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Foot Pointing Communication
fd699386-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd699386-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd699386-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69a5ba-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd699386-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69a5ba-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd69e75a-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Warm-up Reasoning
fd69a5ba-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd699b60-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd69a5ba-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69e75a-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Game Reasoning
fd699b60-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd699b60-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd699b60-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69e75a-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Warm-up Reasoning
fd69e75a-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Game Reasoning
fd69e444-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd69e444-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd69e444-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69e444-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69e444-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd6a3e8a-7144-11e5-ba71-058fbc01cf0b One Cup Warm-up Memory
fd6a3e8a-7144-11e5-ba71-058fbc01cf0b Two Cup Warm-up Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Treat Warm-up Communication
fd6a3e8a-7144-11e5-ba71-058fbc01cf0b Memory versus Pointing Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Arm Pointing Communication
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Treat Warm-up Communication
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Arm Pointing Communication
fd699908-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd699908-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd699908-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd699908-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Foot Pointing Communication
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Foot Pointing Communication
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b One Cup Warm-up Memory
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b One Cup Warm-up Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Two Cup Warm-up Memory
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Two Cup Warm-up Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Memory versus Pointing Memory
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Memory versus Pointing Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Memory versus Smell Memory
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Memory versus Smell Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Warm-up Reasoning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Warm-up Reasoning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Game Reasoning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Game Reasoning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Warm-up Reasoning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Warm-up Reasoning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Game Reasoning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Game Reasoning
fd6998a4-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd69a7fe-7144-11e5-ba71-058fbc01cf0b Impossible Task Warm-up Impossible Task
fd69a7fe-7144-11e5-ba71-058fbc01cf0b Impossible Task Game Impossible Task
fd69aaa6-7144-11e5-ba71-058fbc01cf0b Yawn Warm-up Empathy
fd69f524-7144-11e5-ba71-058fbc01cf0b Impossible Task Warm-up Impossible Task
fd69f524-7144-11e5-ba71-058fbc01cf0b Impossible Task Game Impossible Task
fd69f420-7144-11e5-ba71-058fbc01cf0b Impossible Task Warm-up Impossible Task

We can also use this:

In [12]:
%%sql
SELECT dog_guid,test_name,subcategory_name
FROM complete_tests
WHERE YEAR(created_at)=2014 AND MONTH(created_at)=10
LIMIT 100;
100 rows affected.
Out[12]:
dog_guid test_name subcategory_name
fd6a3480-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd6a3480-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Warm-up Reasoning
fd6a3480-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Game Reasoning
fd6a3480-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Warm-up Reasoning
fd6a3480-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Game Reasoning
fd6a2350-7144-11e5-ba71-058fbc01cf0b Memory versus Smell Memory
fd6924aa-7144-11e5-ba71-058fbc01cf0b Yawn Warm-up Empathy
fd6924aa-7144-11e5-ba71-058fbc01cf0b Yawn Game Empathy
fd6924aa-7144-11e5-ba71-058fbc01cf0b Eye Contact Warm-up Empathy
fd6924aa-7144-11e5-ba71-058fbc01cf0b Eye Contact Game Empathy
fd69acd6-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Warm-up Reasoning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Game Reasoning
fd69e75a-7144-11e5-ba71-058fbc01cf0b One Cup Warm-up Memory
fd69e75a-7144-11e5-ba71-058fbc01cf0b Two Cup Warm-up Memory
fd69e75a-7144-11e5-ba71-058fbc01cf0b Memory versus Pointing Memory
fd69d4f4-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd69e75a-7144-11e5-ba71-058fbc01cf0b Memory versus Smell Memory
fd69e75a-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd69e674-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd69e8a4-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd69e674-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd69e8a4-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Treat Warm-up Communication
fd69e674-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69e674-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69e8a4-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Arm Pointing Communication
fd69e8a4-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Foot Pointing Communication
fd699386-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd699386-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd699386-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69a5ba-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd699386-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69a5ba-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd69e75a-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Warm-up Reasoning
fd69a5ba-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd699b60-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd69a5ba-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69e75a-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Game Reasoning
fd699b60-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd699b60-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd699b60-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69e75a-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Warm-up Reasoning
fd69e75a-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Game Reasoning
fd69e444-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd69e444-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd69e444-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69e444-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69e444-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd69acd6-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd6a3e8a-7144-11e5-ba71-058fbc01cf0b One Cup Warm-up Memory
fd6a3e8a-7144-11e5-ba71-058fbc01cf0b Two Cup Warm-up Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Treat Warm-up Communication
fd6a3e8a-7144-11e5-ba71-058fbc01cf0b Memory versus Pointing Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Arm Pointing Communication
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Treat Warm-up Communication
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Arm Pointing Communication
fd699908-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd699908-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd699908-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd699908-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Foot Pointing Communication
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Foot Pointing Communication
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Watching Cunning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Turn Your Back Cunning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Cover Your Eyes Cunning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Watching - Part 2 Cunning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b One Cup Warm-up Memory
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b One Cup Warm-up Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Two Cup Warm-up Memory
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Two Cup Warm-up Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Memory versus Pointing Memory
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Memory versus Pointing Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Memory versus Smell Memory
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Memory versus Smell Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Warm-up Reasoning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Warm-up Reasoning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Game Reasoning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Inferential Reasoning Game Reasoning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Warm-up Reasoning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Warm-up Reasoning
fd6a4b46-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Game Reasoning
fd6a4ae2-7144-11e5-ba71-058fbc01cf0b Physical Reasoning Game Reasoning
fd6998a4-7144-11e5-ba71-058fbc01cf0b Delayed Cup Game Memory
fd69a7fe-7144-11e5-ba71-058fbc01cf0b Impossible Task Warm-up Impossible Task
fd69a7fe-7144-11e5-ba71-058fbc01cf0b Impossible Task Game Impossible Task
fd69aaa6-7144-11e5-ba71-058fbc01cf0b Yawn Warm-up Empathy
fd69f524-7144-11e5-ba71-058fbc01cf0b Impossible Task Warm-up Impossible Task
fd69f524-7144-11e5-ba71-058fbc01cf0b Impossible Task Game Impossible Task
fd69f420-7144-11e5-ba71-058fbc01cf0b Impossible Task Warm-up Impossible Task




comments powered by Disqus