Copyright Jana Schaich Borg/Attribution-NonCommercial 4.0 International (CC BY-NC 4.0)
So far you've learned how to select, reformat, manipulate, order, and summarize data from a single table in database. In this lesson, you are going to learn how to summarize multiple subsets of your data in the same query. The method for doing this is to include a "GROUP BY" clause in your SQL query.
The GROUP BY clause comes after the WHERE clause, but before ORDER BY or LIMIT:
The GROUP BY clause is easy to incorporate into your queries. In fact, it might be a little too easy to incorporate into MySQL queries, because it can be used incorrectly in MySQL queries even when no error message is displayed. As a consequence, I suggest you adopt a healthy dose of caution every time you use the GROUP BY clause. By the end of this lesson, you will understand why. When used correctly, though, GROUP BY is one of the most useful and efficient parts of an SQL query, and once you are comfortable using it, you will use it very frequently.
To get started, load the SQL library and the Dognition database, and set the dognition database as the default:
%load_ext sql
%sql mysql://studentuser:studentpw@mysqlserver/dognitiondb
%sql USE dognitiondb
0 rows affected.
[]
Let's return to a question from MySQL Exercise 4. How would you query the average rating for each of the 40 tests in the Reviews table? As we discussed, one very inefficient method to do that would be to write 40 separate queries with each one having a different test name in the WHERE conditional clause. Then you could copy or transcribe the results from all 40 queries into one place. But that wouldn't be very pleasant. Here's how you could do the same thing using one query that has a GROUP BY clause:
SELECT test_name, AVG(rating) AS AVG_Rating
FROM reviews
GROUP BY test_name
This query will output the average rating for each test. More technically, this query will instruct MySQL to average all the rows that have the same value in the test_name column.
Notice that I included test_name in the SELECT statement. As a strong rule of thumb, if you are grouping by a column, you should also include that column in the SELECT statement. If you don't do this, you won't know to which group each row of your output corresponds.
To see what I mean, try the query above without test_name included in the SELECT statement:
%%sql
SELECT test_name, AVG(rating) AS AVG_Rating
FROM reviews
GROUP BY test_name
40 rows affected.
test_name | AVG_Rating |
---|---|
1 vs 1 Game | 3.9206 |
3 vs 1 Game | 4.2857 |
5 vs 1 Game | 3.9272 |
Arm Pointing | 4.2153 |
Cover Your Eyes | 2.6741 |
Delayed Cup Game | 3.3514 |
Different Perspective | 2.7647 |
Expression Game | 4.0000 |
Eye Contact Game | 2.9372 |
Eye Contact Warm-up | 0.9632 |
Foot Pointing | 4.0093 |
Impossible Task Game | 3.0965 |
Impossible Task Warm-up | 0.2174 |
Inferential Reasoning Game | 4.5223 |
Inferential Reasoning Warm-up | 4.3066 |
Memory versus Pointing | 3.5584 |
Memory versus Smell | 4.2623 |
Navigation Game | 2.9841 |
Navigation Learning | 2.0303 |
Navigation Warm-up | 1.9805 |
Numerosity Warm-Up | 2.6173 |
One Cup Warm-up | 1.3693 |
Physical Reasoning Game | 3.8492 |
Physical Reasoning Warm-up | 1.6625 |
Self Control Game | 3.8519 |
Shaker Game | 4.6667 |
Shaker Warm-Up | 2.1818 |
Shared Perspective | 3.2778 |
Slide | 4.5111 |
Smell Game | 4.2857 |
Stair Game | 4.2857 |
Switch | 5.5676 |
Treat Warm-up | 0.7909 |
Turn Your Back | 3.1293 |
Two Cup Warm-up | 1.6737 |
Warm-Up | 1.2020 |
Watching | 2.4594 |
Watching - Part 2 | 2.6570 |
Yawn Game | 2.8477 |
Yawn Warm-up | 2.0035 |
You can form groups using derived values as well as original columns. To illustrate this, let's address another question: how many tests were completed during each month of the year?
To answer this question, we need to take advantage of another datetime function described in the website below:
http://www.w3resource.com/mysql/date-and-time-functions/date-and-time-functions.php
MONTH() will return a number representing the month of a date entry. To get the total number of tests completed each month, you could put the MONTH function into the GROUP BY clause, in this case through an alias:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY Month;
You can also group by multiple columns or derived fields. If we wanted to determine the total number of each type of test completed each month, you could include both "test_name" and the derived "Month" field in the GROUP BY clause, separated by a comma.
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, Month;
MySQL allows you to use aliases in a GROUP BY clause, but some database systems do not. If you are using a database system that does NOT accept aliases in GROUP BY clauses, you can still group by derived fields, but you have to duplicate the calculation for the derived field in the GROUP BY clause in addition to including the derived field in the SELECT clause:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, MONTH(created_at);
Try the query once with test_name first in the GROUP BY list, and once with Month first in the GROUP BY list below. Inspect the outputs:
%%sql
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, MONTH(created_at);
480 rows affected.
test_name | Month | Num_Completed_Tests |
---|---|---|
1 vs 1 Game | 1 | 25 |
1 vs 1 Game | 2 | 28 |
1 vs 1 Game | 3 | 22 |
1 vs 1 Game | 4 | 12 |
1 vs 1 Game | 5 | 13 |
1 vs 1 Game | 6 | 18 |
1 vs 1 Game | 7 | 36 |
1 vs 1 Game | 8 | 17 |
1 vs 1 Game | 9 | 28 |
1 vs 1 Game | 10 | 27 |
1 vs 1 Game | 11 | 15 |
1 vs 1 Game | 12 | 14 |
3 vs 1 Game | 1 | 35 |
3 vs 1 Game | 2 | 28 |
3 vs 1 Game | 3 | 34 |
3 vs 1 Game | 4 | 16 |
3 vs 1 Game | 5 | 34 |
3 vs 1 Game | 6 | 42 |
3 vs 1 Game | 7 | 37 |
3 vs 1 Game | 8 | 23 |
3 vs 1 Game | 9 | 24 |
3 vs 1 Game | 10 | 28 |
3 vs 1 Game | 11 | 22 |
3 vs 1 Game | 12 | 45 |
5 vs 1 Game | 1 | 59 |
5 vs 1 Game | 2 | 54 |
5 vs 1 Game | 3 | 57 |
5 vs 1 Game | 4 | 29 |
5 vs 1 Game | 5 | 50 |
5 vs 1 Game | 6 | 61 |
5 vs 1 Game | 7 | 69 |
5 vs 1 Game | 8 | 38 |
5 vs 1 Game | 9 | 50 |
5 vs 1 Game | 10 | 54 |
5 vs 1 Game | 11 | 40 |
5 vs 1 Game | 12 | 59 |
Arm Pointing | 1 | 622 |
Arm Pointing | 2 | 498 |
Arm Pointing | 3 | 507 |
Arm Pointing | 4 | 381 |
Arm Pointing | 5 | 1149 |
Arm Pointing | 6 | 1424 |
Arm Pointing | 7 | 913 |
Arm Pointing | 8 | 779 |
Arm Pointing | 9 | 1243 |
Arm Pointing | 10 | 2535 |
Arm Pointing | 11 | 735 |
Arm Pointing | 12 | 666 |
Cover Your Eyes | 1 | 452 |
Cover Your Eyes | 2 | 373 |
Cover Your Eyes | 3 | 416 |
Cover Your Eyes | 4 | 291 |
Cover Your Eyes | 5 | 753 |
Cover Your Eyes | 6 | 767 |
Cover Your Eyes | 7 | 521 |
Cover Your Eyes | 8 | 408 |
Cover Your Eyes | 9 | 574 |
Cover Your Eyes | 10 | 1653 |
Cover Your Eyes | 11 | 595 |
Cover Your Eyes | 12 | 447 |
Delayed Cup Game | 1 | 356 |
Delayed Cup Game | 2 | 319 |
Delayed Cup Game | 3 | 350 |
Delayed Cup Game | 4 | 256 |
Delayed Cup Game | 5 | 539 |
Delayed Cup Game | 6 | 525 |
Delayed Cup Game | 7 | 407 |
Delayed Cup Game | 8 | 355 |
Delayed Cup Game | 9 | 424 |
Delayed Cup Game | 10 | 1019 |
Delayed Cup Game | 11 | 422 |
Delayed Cup Game | 12 | 299 |
Different Perspective | 1 | 2 |
Different Perspective | 2 | 3 |
Different Perspective | 3 | 4 |
Different Perspective | 4 | 8 |
Different Perspective | 5 | 12 |
Different Perspective | 6 | 11 |
Different Perspective | 7 | 8 |
Different Perspective | 8 | 10 |
Different Perspective | 9 | 12 |
Different Perspective | 10 | 4 |
Different Perspective | 11 | 6 |
Different Perspective | 12 | 9 |
Expression Game | 1 | 6 |
Expression Game | 2 | 9 |
Expression Game | 3 | 12 |
Expression Game | 4 | 10 |
Expression Game | 5 | 9 |
Expression Game | 6 | 11 |
Expression Game | 7 | 12 |
Expression Game | 8 | 11 |
Expression Game | 9 | 17 |
Expression Game | 10 | 3 |
Expression Game | 11 | 11 |
Expression Game | 12 | 13 |
Eye Contact Game | 1 | 624 |
Eye Contact Game | 2 | 564 |
Eye Contact Game | 3 | 589 |
Eye Contact Game | 4 | 449 |
Eye Contact Game | 5 | 1408 |
Eye Contact Game | 6 | 1812 |
Eye Contact Game | 7 | 1194 |
Eye Contact Game | 8 | 1130 |
Eye Contact Game | 9 | 1674 |
Eye Contact Game | 10 | 3452 |
Eye Contact Game | 11 | 889 |
Eye Contact Game | 12 | 760 |
Eye Contact Warm-up | 1 | 707 |
Eye Contact Warm-up | 2 | 616 |
Eye Contact Warm-up | 3 | 616 |
Eye Contact Warm-up | 4 | 454 |
Eye Contact Warm-up | 5 | 1636 |
Eye Contact Warm-up | 6 | 2152 |
Eye Contact Warm-up | 7 | 1379 |
Eye Contact Warm-up | 8 | 1256 |
Eye Contact Warm-up | 9 | 1967 |
Eye Contact Warm-up | 10 | 3675 |
Eye Contact Warm-up | 11 | 941 |
Eye Contact Warm-up | 12 | 839 |
Foot Pointing | 1 | 506 |
Foot Pointing | 2 | 433 |
Foot Pointing | 3 | 477 |
Foot Pointing | 4 | 339 |
Foot Pointing | 5 | 926 |
Foot Pointing | 6 | 1125 |
Foot Pointing | 7 | 759 |
Foot Pointing | 8 | 653 |
Foot Pointing | 9 | 992 |
Foot Pointing | 10 | 2290 |
Foot Pointing | 11 | 685 |
Foot Pointing | 12 | 566 |
Impossible Task Game | 1 | 49 |
Impossible Task Game | 2 | 48 |
Impossible Task Game | 3 | 36 |
Impossible Task Game | 4 | 43 |
Impossible Task Game | 5 | 49 |
Impossible Task Game | 6 | 59 |
Impossible Task Game | 7 | 50 |
Impossible Task Game | 8 | 32 |
Impossible Task Game | 9 | 35 |
Impossible Task Game | 10 | 37 |
Impossible Task Game | 11 | 48 |
Impossible Task Game | 12 | 46 |
Impossible Task Warm-up | 1 | 51 |
Impossible Task Warm-up | 2 | 49 |
Impossible Task Warm-up | 3 | 37 |
Impossible Task Warm-up | 4 | 43 |
Impossible Task Warm-up | 5 | 49 |
Impossible Task Warm-up | 6 | 58 |
Impossible Task Warm-up | 7 | 51 |
Impossible Task Warm-up | 8 | 34 |
Impossible Task Warm-up | 9 | 36 |
Impossible Task Warm-up | 10 | 36 |
Impossible Task Warm-up | 11 | 49 |
Impossible Task Warm-up | 12 | 46 |
Inferential Reasoning Game | 1 | 410 |
Inferential Reasoning Game | 2 | 297 |
Inferential Reasoning Game | 3 | 335 |
Inferential Reasoning Game | 4 | 254 |
Inferential Reasoning Game | 5 | 533 |
Inferential Reasoning Game | 6 | 544 |
Inferential Reasoning Game | 7 | 432 |
Inferential Reasoning Game | 8 | 350 |
Inferential Reasoning Game | 9 | 398 |
Inferential Reasoning Game | 10 | 785 |
Inferential Reasoning Game | 11 | 362 |
Inferential Reasoning Game | 12 | 280 |
Inferential Reasoning Warm-up | 1 | 425 |
Inferential Reasoning Warm-up | 2 | 302 |
Inferential Reasoning Warm-up | 3 | 351 |
Inferential Reasoning Warm-up | 4 | 257 |
Inferential Reasoning Warm-up | 5 | 544 |
Inferential Reasoning Warm-up | 6 | 550 |
Inferential Reasoning Warm-up | 7 | 450 |
Inferential Reasoning Warm-up | 8 | 354 |
Inferential Reasoning Warm-up | 9 | 399 |
Inferential Reasoning Warm-up | 10 | 794 |
Inferential Reasoning Warm-up | 11 | 376 |
Inferential Reasoning Warm-up | 12 | 296 |
Memory versus Pointing | 1 | 464 |
Memory versus Pointing | 2 | 395 |
Memory versus Pointing | 3 | 381 |
Memory versus Pointing | 4 | 299 |
Memory versus Pointing | 5 | 711 |
Memory versus Pointing | 6 | 704 |
Memory versus Pointing | 7 | 511 |
Memory versus Pointing | 8 | 415 |
Memory versus Pointing | 9 | 574 |
Memory versus Pointing | 10 | 1218 |
Memory versus Pointing | 11 | 477 |
Memory versus Pointing | 12 | 376 |
Memory versus Smell | 1 | 465 |
Memory versus Smell | 2 | 374 |
Memory versus Smell | 3 | 382 |
Memory versus Smell | 4 | 280 |
Memory versus Smell | 5 | 667 |
Memory versus Smell | 6 | 666 |
Memory versus Smell | 7 | 491 |
Memory versus Smell | 8 | 402 |
Memory versus Smell | 9 | 529 |
Memory versus Smell | 10 | 1111 |
Memory versus Smell | 11 | 444 |
Memory versus Smell | 12 | 352 |
Navigation Game | 1 | 57 |
Navigation Game | 2 | 41 |
Navigation Game | 3 | 47 |
Navigation Game | 4 | 41 |
Navigation Game | 5 | 336 |
Navigation Game | 6 | 81 |
Navigation Game | 7 | 70 |
Navigation Game | 8 | 41 |
Navigation Game | 9 | 52 |
Navigation Game | 10 | 69 |
Navigation Game | 11 | 47 |
Navigation Game | 12 | 45 |
Navigation Learning | 1 | 59 |
Navigation Learning | 2 | 43 |
Navigation Learning | 3 | 47 |
Navigation Learning | 4 | 45 |
Navigation Learning | 5 | 444 |
Navigation Learning | 6 | 90 |
Navigation Learning | 7 | 74 |
Navigation Learning | 8 | 34 |
Navigation Learning | 9 | 54 |
Navigation Learning | 10 | 70 |
Navigation Learning | 11 | 47 |
Navigation Learning | 12 | 47 |
Navigation Warm-up | 1 | 62 |
Navigation Warm-up | 2 | 43 |
Navigation Warm-up | 3 | 50 |
Navigation Warm-up | 4 | 42 |
Navigation Warm-up | 5 | 625 |
Navigation Warm-up | 6 | 114 |
Navigation Warm-up | 7 | 88 |
Navigation Warm-up | 8 | 40 |
Navigation Warm-up | 9 | 58 |
Navigation Warm-up | 10 | 76 |
Navigation Warm-up | 11 | 52 |
Navigation Warm-up | 12 | 49 |
Numerosity Warm-Up | 1 | 36 |
Numerosity Warm-Up | 2 | 28 |
Numerosity Warm-Up | 3 | 35 |
Numerosity Warm-Up | 4 | 17 |
Numerosity Warm-Up | 5 | 37 |
Numerosity Warm-Up | 6 | 44 |
Numerosity Warm-Up | 7 | 37 |
Numerosity Warm-Up | 8 | 23 |
Numerosity Warm-Up | 9 | 24 |
Numerosity Warm-Up | 10 | 29 |
Numerosity Warm-Up | 11 | 26 |
Numerosity Warm-Up | 12 | 46 |
One Cup Warm-up | 1 | 476 |
One Cup Warm-up | 2 | 388 |
One Cup Warm-up | 3 | 388 |
One Cup Warm-up | 4 | 313 |
One Cup Warm-up | 5 | 757 |
One Cup Warm-up | 6 | 745 |
One Cup Warm-up | 7 | 536 |
One Cup Warm-up | 8 | 416 |
One Cup Warm-up | 9 | 602 |
One Cup Warm-up | 10 | 1272 |
One Cup Warm-up | 11 | 491 |
One Cup Warm-up | 12 | 401 |
Physical Reasoning Game | 1 | 317 |
Physical Reasoning Game | 2 | 252 |
Physical Reasoning Game | 3 | 321 |
Physical Reasoning Game | 4 | 224 |
Physical Reasoning Game | 5 | 430 |
Physical Reasoning Game | 6 | 424 |
Physical Reasoning Game | 7 | 360 |
Physical Reasoning Game | 8 | 316 |
Physical Reasoning Game | 9 | 309 |
Physical Reasoning Game | 10 | 711 |
Physical Reasoning Game | 11 | 341 |
Physical Reasoning Game | 12 | 250 |
Physical Reasoning Warm-up | 1 | 384 |
Physical Reasoning Warm-up | 2 | 290 |
Physical Reasoning Warm-up | 3 | 337 |
Physical Reasoning Warm-up | 4 | 247 |
Physical Reasoning Warm-up | 5 | 510 |
Physical Reasoning Warm-up | 6 | 504 |
Physical Reasoning Warm-up | 7 | 411 |
Physical Reasoning Warm-up | 8 | 347 |
Physical Reasoning Warm-up | 9 | 364 |
Physical Reasoning Warm-up | 10 | 742 |
Physical Reasoning Warm-up | 11 | 349 |
Physical Reasoning Warm-up | 12 | 276 |
Self Control Game | 1 | 8 |
Self Control Game | 2 | 8 |
Self Control Game | 3 | 11 |
Self Control Game | 4 | 9 |
Self Control Game | 5 | 9 |
Self Control Game | 6 | 11 |
Self Control Game | 7 | 14 |
Self Control Game | 8 | 13 |
Self Control Game | 9 | 19 |
Self Control Game | 10 | 9 |
Self Control Game | 11 | 15 |
Self Control Game | 12 | 6 |
Shaker Game | 1 | 2 |
Shaker Game | 2 | 9 |
Shaker Game | 3 | 8 |
Shaker Game | 4 | 4 |
Shaker Game | 5 | 10 |
Shaker Game | 6 | 7 |
Shaker Game | 7 | 5 |
Shaker Game | 8 | 6 |
Shaker Game | 9 | 12 |
Shaker Game | 10 | 10 |
Shaker Game | 11 | 3 |
Shaker Game | 12 | 1 |
Shaker Warm-Up | 1 | 2 |
Shaker Warm-Up | 2 | 11 |
Shaker Warm-Up | 3 | 8 |
Shaker Warm-Up | 4 | 4 |
Shaker Warm-Up | 5 | 10 |
Shaker Warm-Up | 6 | 7 |
Shaker Warm-Up | 7 | 5 |
Shaker Warm-Up | 8 | 6 |
Shaker Warm-Up | 9 | 13 |
Shaker Warm-Up | 10 | 11 |
Shaker Warm-Up | 11 | 3 |
Shaker Warm-Up | 12 | 1 |
Shared Perspective | 1 | 1 |
Shared Perspective | 2 | 3 |
Shared Perspective | 3 | 3 |
Shared Perspective | 4 | 8 |
Shared Perspective | 5 | 11 |
Shared Perspective | 6 | 11 |
Shared Perspective | 7 | 7 |
Shared Perspective | 8 | 10 |
Shared Perspective | 9 | 12 |
Shared Perspective | 10 | 4 |
Shared Perspective | 11 | 6 |
Shared Perspective | 12 | 7 |
Slide | 1 | 17 |
Slide | 2 | 12 |
Slide | 3 | 16 |
Slide | 4 | 11 |
Slide | 5 | 17 |
Slide | 6 | 10 |
Slide | 7 | 13 |
Slide | 8 | 6 |
Slide | 9 | 18 |
Slide | 10 | 7 |
Slide | 11 | 13 |
Slide | 12 | 7 |
Smell Game | 1 | 13 |
Smell Game | 2 | 18 |
Smell Game | 3 | 16 |
Smell Game | 4 | 6 |
Smell Game | 5 | 19 |
Smell Game | 6 | 9 |
Smell Game | 7 | 21 |
Smell Game | 8 | 10 |
Smell Game | 9 | 16 |
Smell Game | 10 | 15 |
Smell Game | 11 | 12 |
Smell Game | 12 | 13 |
Stair Game | 1 | 12 |
Stair Game | 2 | 8 |
Stair Game | 3 | 7 |
Stair Game | 4 | 4 |
Stair Game | 5 | 10 |
Stair Game | 6 | 7 |
Stair Game | 7 | 14 |
Stair Game | 8 | 15 |
Stair Game | 9 | 17 |
Stair Game | 10 | 7 |
Stair Game | 11 | 3 |
Stair Game | 12 | 8 |
Switch | 1 | 15 |
Switch | 2 | 12 |
Switch | 3 | 14 |
Switch | 4 | 11 |
Switch | 5 | 15 |
Switch | 6 | 11 |
Switch | 7 | 11 |
Switch | 8 | 10 |
Switch | 9 | 13 |
Switch | 10 | 9 |
Switch | 11 | 10 |
Switch | 12 | 6 |
Treat Warm-up | 1 | 626 |
Treat Warm-up | 2 | 495 |
Treat Warm-up | 3 | 515 |
Treat Warm-up | 4 | 393 |
Treat Warm-up | 5 | 1182 |
Treat Warm-up | 6 | 1444 |
Treat Warm-up | 7 | 951 |
Treat Warm-up | 8 | 791 |
Treat Warm-up | 9 | 1303 |
Treat Warm-up | 10 | 2626 |
Treat Warm-up | 11 | 744 |
Treat Warm-up | 12 | 667 |
Turn Your Back | 1 | 462 |
Turn Your Back | 2 | 372 |
Turn Your Back | 3 | 418 |
Turn Your Back | 4 | 298 |
Turn Your Back | 5 | 769 |
Turn Your Back | 6 | 778 |
Turn Your Back | 7 | 535 |
Turn Your Back | 8 | 418 |
Turn Your Back | 9 | 599 |
Turn Your Back | 10 | 1711 |
Turn Your Back | 11 | 601 |
Turn Your Back | 12 | 467 |
Two Cup Warm-up | 1 | 490 |
Two Cup Warm-up | 2 | 386 |
Two Cup Warm-up | 3 | 389 |
Two Cup Warm-up | 4 | 308 |
Two Cup Warm-up | 5 | 747 |
Two Cup Warm-up | 6 | 732 |
Two Cup Warm-up | 7 | 537 |
Two Cup Warm-up | 8 | 419 |
Two Cup Warm-up | 9 | 578 |
Two Cup Warm-up | 10 | 1234 |
Two Cup Warm-up | 11 | 482 |
Two Cup Warm-up | 12 | 379 |
Warm-up | 1 | 70 |
Warm-up | 2 | 77 |
Warm-Up | 3 | 79 |
Warm-Up | 4 | 54 |
Warm-Up | 5 | 71 |
Warm-Up | 6 | 64 |
Warm-Up | 7 | 101 |
Warm-Up | 8 | 64 |
Warm-Up | 9 | 103 |
Warm-up | 10 | 69 |
Warm-Up | 11 | 65 |
Warm-up | 12 | 56 |
Watching | 1 | 594 |
Watching | 2 | 443 |
Watching | 3 | 472 |
Watching | 4 | 322 |
Watching | 5 | 980 |
Watching | 6 | 1035 |
Watching | 7 | 679 |
Watching | 8 | 511 |
Watching | 9 | 809 |
Watching | 10 | 1797 |
Watching | 11 | 611 |
Watching | 12 | 555 |
Watching - Part 2 | 1 | 445 |
Watching - Part 2 | 2 | 372 |
Watching - Part 2 | 3 | 406 |
Watching - Part 2 | 4 | 282 |
Watching - Part 2 | 5 | 734 |
Watching - Part 2 | 6 | 747 |
Watching - Part 2 | 7 | 504 |
Watching - Part 2 | 8 | 405 |
Watching - Part 2 | 9 | 568 |
Watching - Part 2 | 10 | 1578 |
Watching - Part 2 | 11 | 565 |
Watching - Part 2 | 12 | 438 |
Yawn Game | 1 | 828 |
Yawn Game | 2 | 709 |
Yawn Game | 3 | 681 |
Yawn Game | 4 | 525 |
Yawn Game | 5 | 2065 |
Yawn Game | 6 | 2906 |
Yawn Game | 7 | 1775 |
Yawn Game | 8 | 1520 |
Yawn Game | 9 | 2563 |
Yawn Game | 10 | 4073 |
Yawn Game | 11 | 989 |
Yawn Game | 12 | 978 |
Yawn Warm-up | 1 | 834 |
Yawn Warm-up | 2 | 712 |
Yawn Warm-up | 3 | 698 |
Yawn Warm-up | 4 | 541 |
Yawn Warm-up | 5 | 2143 |
Yawn Warm-up | 6 | 3071 |
Yawn Warm-up | 7 | 1909 |
Yawn Warm-up | 8 | 1694 |
Yawn Warm-up | 9 | 2771 |
Yawn Warm-up | 10 | 4387 |
Yawn Warm-up | 11 | 1060 |
Yawn Warm-up | 12 | 1043 |
%%sql
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY MONTH(created_at), test_name;
480 rows affected.
test_name | Month | Num_Completed_Tests |
---|---|---|
1 vs 1 Game | 1 | 25 |
3 vs 1 Game | 1 | 35 |
5 vs 1 Game | 1 | 59 |
Arm Pointing | 1 | 622 |
Cover Your Eyes | 1 | 452 |
Delayed Cup Game | 1 | 356 |
Different Perspective | 1 | 2 |
Expression Game | 1 | 6 |
Eye Contact Game | 1 | 624 |
Eye Contact Warm-up | 1 | 707 |
Foot Pointing | 1 | 506 |
Impossible Task Game | 1 | 49 |
Impossible Task Warm-up | 1 | 51 |
Inferential Reasoning Game | 1 | 410 |
Inferential Reasoning Warm-up | 1 | 425 |
Memory versus Pointing | 1 | 464 |
Memory versus Smell | 1 | 465 |
Navigation Game | 1 | 57 |
Navigation Learning | 1 | 59 |
Navigation Warm-up | 1 | 62 |
Numerosity Warm-Up | 1 | 36 |
One Cup Warm-up | 1 | 476 |
Physical Reasoning Game | 1 | 317 |
Physical Reasoning Warm-up | 1 | 384 |
Self Control Game | 1 | 8 |
Shaker Game | 1 | 2 |
Shaker Warm-Up | 1 | 2 |
Shared Perspective | 1 | 1 |
Slide | 1 | 17 |
Smell Game | 1 | 13 |
Stair Game | 1 | 12 |
Switch | 1 | 15 |
Treat Warm-up | 1 | 626 |
Turn Your Back | 1 | 462 |
Two Cup Warm-up | 1 | 490 |
Warm-up | 1 | 70 |
Watching | 1 | 594 |
Watching - Part 2 | 1 | 445 |
Yawn Game | 1 | 828 |
Yawn Warm-up | 1 | 834 |
1 vs 1 Game | 2 | 28 |
3 vs 1 Game | 2 | 28 |
5 vs 1 Game | 2 | 54 |
Arm Pointing | 2 | 498 |
Cover Your Eyes | 2 | 373 |
Delayed Cup Game | 2 | 319 |
Different Perspective | 2 | 3 |
Expression Game | 2 | 9 |
Eye Contact Game | 2 | 564 |
Eye Contact Warm-up | 2 | 616 |
Foot Pointing | 2 | 433 |
Impossible Task Game | 2 | 48 |
Impossible Task Warm-up | 2 | 49 |
Inferential Reasoning Game | 2 | 297 |
Inferential Reasoning Warm-up | 2 | 302 |
Memory versus Pointing | 2 | 395 |
Memory versus Smell | 2 | 374 |
Navigation Game | 2 | 41 |
Navigation Learning | 2 | 43 |
Navigation Warm-up | 2 | 43 |
Numerosity Warm-Up | 2 | 28 |
One Cup Warm-up | 2 | 388 |
Physical Reasoning Game | 2 | 252 |
Physical Reasoning Warm-up | 2 | 290 |
Self Control Game | 2 | 8 |
Shaker Game | 2 | 9 |
Shaker Warm-Up | 2 | 11 |
Shared Perspective | 2 | 3 |
Slide | 2 | 12 |
Smell Game | 2 | 18 |
Stair Game | 2 | 8 |
Switch | 2 | 12 |
Treat Warm-up | 2 | 495 |
Turn Your Back | 2 | 372 |
Two Cup Warm-up | 2 | 386 |
Warm-up | 2 | 77 |
Watching | 2 | 443 |
Watching - Part 2 | 2 | 372 |
Yawn Game | 2 | 709 |
Yawn Warm-up | 2 | 712 |
1 vs 1 Game | 3 | 22 |
3 vs 1 Game | 3 | 34 |
5 vs 1 Game | 3 | 57 |
Arm Pointing | 3 | 507 |
Cover Your Eyes | 3 | 416 |
Delayed Cup Game | 3 | 350 |
Different Perspective | 3 | 4 |
Expression Game | 3 | 12 |
Eye Contact Game | 3 | 589 |
Eye Contact Warm-up | 3 | 616 |
Foot Pointing | 3 | 477 |
Impossible Task Game | 3 | 36 |
Impossible Task Warm-up | 3 | 37 |
Inferential Reasoning Game | 3 | 335 |
Inferential Reasoning Warm-up | 3 | 351 |
Memory versus Pointing | 3 | 381 |
Memory versus Smell | 3 | 382 |
Navigation Game | 3 | 47 |
Navigation Learning | 3 | 47 |
Navigation Warm-up | 3 | 50 |
Numerosity Warm-Up | 3 | 35 |
One Cup Warm-up | 3 | 388 |
Physical Reasoning Game | 3 | 321 |
Physical Reasoning Warm-up | 3 | 337 |
Self Control Game | 3 | 11 |
Shaker Game | 3 | 8 |
Shaker Warm-Up | 3 | 8 |
Shared Perspective | 3 | 3 |
Slide | 3 | 16 |
Smell Game | 3 | 16 |
Stair Game | 3 | 7 |
Switch | 3 | 14 |
Treat Warm-up | 3 | 515 |
Turn Your Back | 3 | 418 |
Two Cup Warm-up | 3 | 389 |
Warm-Up | 3 | 79 |
Watching | 3 | 472 |
Watching - Part 2 | 3 | 406 |
Yawn Game | 3 | 681 |
Yawn Warm-up | 3 | 698 |
1 vs 1 Game | 4 | 12 |
3 vs 1 Game | 4 | 16 |
5 vs 1 Game | 4 | 29 |
Arm Pointing | 4 | 381 |
Cover Your Eyes | 4 | 291 |
Delayed Cup Game | 4 | 256 |
Different Perspective | 4 | 8 |
Expression Game | 4 | 10 |
Eye Contact Game | 4 | 449 |
Eye Contact Warm-up | 4 | 454 |
Foot Pointing | 4 | 339 |
Impossible Task Game | 4 | 43 |
Impossible Task Warm-up | 4 | 43 |
Inferential Reasoning Game | 4 | 254 |
Inferential Reasoning Warm-up | 4 | 257 |
Memory versus Pointing | 4 | 299 |
Memory versus Smell | 4 | 280 |
Navigation Game | 4 | 41 |
Navigation Learning | 4 | 45 |
Navigation Warm-up | 4 | 42 |
Numerosity Warm-Up | 4 | 17 |
One Cup Warm-up | 4 | 313 |
Physical Reasoning Game | 4 | 224 |
Physical Reasoning Warm-up | 4 | 247 |
Self Control Game | 4 | 9 |
Shaker Game | 4 | 4 |
Shaker Warm-Up | 4 | 4 |
Shared Perspective | 4 | 8 |
Slide | 4 | 11 |
Smell Game | 4 | 6 |
Stair Game | 4 | 4 |
Switch | 4 | 11 |
Treat Warm-up | 4 | 393 |
Turn Your Back | 4 | 298 |
Two Cup Warm-up | 4 | 308 |
Warm-Up | 4 | 54 |
Watching | 4 | 322 |
Watching - Part 2 | 4 | 282 |
Yawn Game | 4 | 525 |
Yawn Warm-up | 4 | 541 |
1 vs 1 Game | 5 | 13 |
3 vs 1 Game | 5 | 34 |
5 vs 1 Game | 5 | 50 |
Arm Pointing | 5 | 1149 |
Cover Your Eyes | 5 | 753 |
Delayed Cup Game | 5 | 539 |
Different Perspective | 5 | 12 |
Expression Game | 5 | 9 |
Eye Contact Game | 5 | 1408 |
Eye Contact Warm-up | 5 | 1636 |
Foot Pointing | 5 | 926 |
Impossible Task Game | 5 | 49 |
Impossible Task Warm-up | 5 | 49 |
Inferential Reasoning Game | 5 | 533 |
Inferential Reasoning Warm-up | 5 | 544 |
Memory versus Pointing | 5 | 711 |
Memory versus Smell | 5 | 667 |
Navigation Game | 5 | 336 |
Navigation Learning | 5 | 444 |
Navigation Warm-up | 5 | 625 |
Numerosity Warm-Up | 5 | 37 |
One Cup Warm-up | 5 | 757 |
Physical Reasoning Game | 5 | 430 |
Physical Reasoning Warm-up | 5 | 510 |
Self Control Game | 5 | 9 |
Shaker Game | 5 | 10 |
Shaker Warm-Up | 5 | 10 |
Shared Perspective | 5 | 11 |
Slide | 5 | 17 |
Smell Game | 5 | 19 |
Stair Game | 5 | 10 |
Switch | 5 | 15 |
Treat Warm-up | 5 | 1182 |
Turn Your Back | 5 | 769 |
Two Cup Warm-up | 5 | 747 |
Warm-Up | 5 | 71 |
Watching | 5 | 980 |
Watching - Part 2 | 5 | 734 |
Yawn Game | 5 | 2065 |
Yawn Warm-up | 5 | 2143 |
1 vs 1 Game | 6 | 18 |
3 vs 1 Game | 6 | 42 |
5 vs 1 Game | 6 | 61 |
Arm Pointing | 6 | 1424 |
Cover Your Eyes | 6 | 767 |
Delayed Cup Game | 6 | 525 |
Different Perspective | 6 | 11 |
Expression Game | 6 | 11 |
Eye Contact Game | 6 | 1812 |
Eye Contact Warm-up | 6 | 2152 |
Foot Pointing | 6 | 1125 |
Impossible Task Game | 6 | 59 |
Impossible Task Warm-up | 6 | 58 |
Inferential Reasoning Game | 6 | 544 |
Inferential Reasoning Warm-up | 6 | 550 |
Memory versus Pointing | 6 | 704 |
Memory versus Smell | 6 | 666 |
Navigation Game | 6 | 81 |
Navigation Learning | 6 | 90 |
Navigation Warm-up | 6 | 114 |
Numerosity Warm-Up | 6 | 44 |
One Cup Warm-up | 6 | 745 |
Physical Reasoning Game | 6 | 424 |
Physical Reasoning Warm-up | 6 | 504 |
Self Control Game | 6 | 11 |
Shaker Game | 6 | 7 |
Shaker Warm-Up | 6 | 7 |
Shared Perspective | 6 | 11 |
Slide | 6 | 10 |
Smell Game | 6 | 9 |
Stair Game | 6 | 7 |
Switch | 6 | 11 |
Treat Warm-up | 6 | 1444 |
Turn Your Back | 6 | 778 |
Two Cup Warm-up | 6 | 732 |
Warm-Up | 6 | 64 |
Watching | 6 | 1035 |
Watching - Part 2 | 6 | 747 |
Yawn Game | 6 | 2906 |
Yawn Warm-up | 6 | 3071 |
1 vs 1 Game | 7 | 36 |
3 vs 1 Game | 7 | 37 |
5 vs 1 Game | 7 | 69 |
Arm Pointing | 7 | 913 |
Cover Your Eyes | 7 | 521 |
Delayed Cup Game | 7 | 407 |
Different Perspective | 7 | 8 |
Expression Game | 7 | 12 |
Eye Contact Game | 7 | 1194 |
Eye Contact Warm-up | 7 | 1379 |
Foot Pointing | 7 | 759 |
Impossible Task Game | 7 | 50 |
Impossible Task Warm-up | 7 | 51 |
Inferential Reasoning Game | 7 | 432 |
Inferential Reasoning Warm-up | 7 | 450 |
Memory versus Pointing | 7 | 511 |
Memory versus Smell | 7 | 491 |
Navigation Game | 7 | 70 |
Navigation Learning | 7 | 74 |
Navigation Warm-up | 7 | 88 |
Numerosity Warm-Up | 7 | 37 |
One Cup Warm-up | 7 | 536 |
Physical Reasoning Game | 7 | 360 |
Physical Reasoning Warm-up | 7 | 411 |
Self Control Game | 7 | 14 |
Shaker Game | 7 | 5 |
Shaker Warm-Up | 7 | 5 |
Shared Perspective | 7 | 7 |
Slide | 7 | 13 |
Smell Game | 7 | 21 |
Stair Game | 7 | 14 |
Switch | 7 | 11 |
Treat Warm-up | 7 | 951 |
Turn Your Back | 7 | 535 |
Two Cup Warm-up | 7 | 537 |
Warm-Up | 7 | 101 |
Watching | 7 | 679 |
Watching - Part 2 | 7 | 504 |
Yawn Game | 7 | 1775 |
Yawn Warm-up | 7 | 1909 |
1 vs 1 Game | 8 | 17 |
3 vs 1 Game | 8 | 23 |
5 vs 1 Game | 8 | 38 |
Arm Pointing | 8 | 779 |
Cover Your Eyes | 8 | 408 |
Delayed Cup Game | 8 | 355 |
Different Perspective | 8 | 10 |
Expression Game | 8 | 11 |
Eye Contact Game | 8 | 1130 |
Eye Contact Warm-up | 8 | 1256 |
Foot Pointing | 8 | 653 |
Impossible Task Game | 8 | 32 |
Impossible Task Warm-up | 8 | 34 |
Inferential Reasoning Game | 8 | 350 |
Inferential Reasoning Warm-up | 8 | 354 |
Memory versus Pointing | 8 | 415 |
Memory versus Smell | 8 | 402 |
Navigation Game | 8 | 41 |
Navigation Learning | 8 | 34 |
Navigation Warm-up | 8 | 40 |
Numerosity Warm-Up | 8 | 23 |
One Cup Warm-up | 8 | 416 |
Physical Reasoning Game | 8 | 316 |
Physical Reasoning Warm-up | 8 | 347 |
Self Control Game | 8 | 13 |
Shaker Game | 8 | 6 |
Shaker Warm-Up | 8 | 6 |
Shared Perspective | 8 | 10 |
Slide | 8 | 6 |
Smell Game | 8 | 10 |
Stair Game | 8 | 15 |
Switch | 8 | 10 |
Treat Warm-up | 8 | 791 |
Turn Your Back | 8 | 418 |
Two Cup Warm-up | 8 | 419 |
Warm-Up | 8 | 64 |
Watching | 8 | 511 |
Watching - Part 2 | 8 | 405 |
Yawn Game | 8 | 1520 |
Yawn Warm-up | 8 | 1694 |
1 vs 1 Game | 9 | 28 |
3 vs 1 Game | 9 | 24 |
5 vs 1 Game | 9 | 50 |
Arm Pointing | 9 | 1243 |
Cover Your Eyes | 9 | 574 |
Delayed Cup Game | 9 | 424 |
Different Perspective | 9 | 12 |
Expression Game | 9 | 17 |
Eye Contact Game | 9 | 1674 |
Eye Contact Warm-up | 9 | 1967 |
Foot Pointing | 9 | 992 |
Impossible Task Game | 9 | 35 |
Impossible Task Warm-up | 9 | 36 |
Inferential Reasoning Game | 9 | 398 |
Inferential Reasoning Warm-up | 9 | 399 |
Memory versus Pointing | 9 | 574 |
Memory versus Smell | 9 | 529 |
Navigation Game | 9 | 52 |
Navigation Learning | 9 | 54 |
Navigation Warm-up | 9 | 58 |
Numerosity Warm-Up | 9 | 24 |
One Cup Warm-up | 9 | 602 |
Physical Reasoning Game | 9 | 309 |
Physical Reasoning Warm-up | 9 | 364 |
Self Control Game | 9 | 19 |
Shaker Game | 9 | 12 |
Shaker Warm-Up | 9 | 13 |
Shared Perspective | 9 | 12 |
Slide | 9 | 18 |
Smell Game | 9 | 16 |
Stair Game | 9 | 17 |
Switch | 9 | 13 |
Treat Warm-up | 9 | 1303 |
Turn Your Back | 9 | 599 |
Two Cup Warm-up | 9 | 578 |
Warm-Up | 9 | 103 |
Watching | 9 | 809 |
Watching - Part 2 | 9 | 568 |
Yawn Game | 9 | 2563 |
Yawn Warm-up | 9 | 2771 |
1 vs 1 Game | 10 | 27 |
3 vs 1 Game | 10 | 28 |
5 vs 1 Game | 10 | 54 |
Arm Pointing | 10 | 2535 |
Cover Your Eyes | 10 | 1653 |
Delayed Cup Game | 10 | 1019 |
Different Perspective | 10 | 4 |
Expression Game | 10 | 3 |
Eye Contact Game | 10 | 3452 |
Eye Contact Warm-up | 10 | 3675 |
Foot Pointing | 10 | 2290 |
Impossible Task Game | 10 | 37 |
Impossible Task Warm-up | 10 | 36 |
Inferential Reasoning Game | 10 | 785 |
Inferential Reasoning Warm-up | 10 | 794 |
Memory versus Pointing | 10 | 1218 |
Memory versus Smell | 10 | 1111 |
Navigation Game | 10 | 69 |
Navigation Learning | 10 | 70 |
Navigation Warm-up | 10 | 76 |
Numerosity Warm-Up | 10 | 29 |
One Cup Warm-up | 10 | 1272 |
Physical Reasoning Game | 10 | 711 |
Physical Reasoning Warm-up | 10 | 742 |
Self Control Game | 10 | 9 |
Shaker Game | 10 | 10 |
Shaker Warm-Up | 10 | 11 |
Shared Perspective | 10 | 4 |
Slide | 10 | 7 |
Smell Game | 10 | 15 |
Stair Game | 10 | 7 |
Switch | 10 | 9 |
Treat Warm-up | 10 | 2626 |
Turn Your Back | 10 | 1711 |
Two Cup Warm-up | 10 | 1234 |
Warm-up | 10 | 69 |
Watching | 10 | 1797 |
Watching - Part 2 | 10 | 1578 |
Yawn Game | 10 | 4073 |
Yawn Warm-up | 10 | 4387 |
1 vs 1 Game | 11 | 15 |
3 vs 1 Game | 11 | 22 |
5 vs 1 Game | 11 | 40 |
Arm Pointing | 11 | 735 |
Cover Your Eyes | 11 | 595 |
Delayed Cup Game | 11 | 422 |
Different Perspective | 11 | 6 |
Expression Game | 11 | 11 |
Eye Contact Game | 11 | 889 |
Eye Contact Warm-up | 11 | 941 |
Foot Pointing | 11 | 685 |
Impossible Task Game | 11 | 48 |
Impossible Task Warm-up | 11 | 49 |
Inferential Reasoning Game | 11 | 362 |
Inferential Reasoning Warm-up | 11 | 376 |
Memory versus Pointing | 11 | 477 |
Memory versus Smell | 11 | 444 |
Navigation Game | 11 | 47 |
Navigation Learning | 11 | 47 |
Navigation Warm-up | 11 | 52 |
Numerosity Warm-Up | 11 | 26 |
One Cup Warm-up | 11 | 491 |
Physical Reasoning Game | 11 | 341 |
Physical Reasoning Warm-up | 11 | 349 |
Self Control Game | 11 | 15 |
Shaker Game | 11 | 3 |
Shaker Warm-Up | 11 | 3 |
Shared Perspective | 11 | 6 |
Slide | 11 | 13 |
Smell Game | 11 | 12 |
Stair Game | 11 | 3 |
Switch | 11 | 10 |
Treat Warm-up | 11 | 744 |
Turn Your Back | 11 | 601 |
Two Cup Warm-up | 11 | 482 |
Warm-Up | 11 | 65 |
Watching | 11 | 611 |
Watching - Part 2 | 11 | 565 |
Yawn Game | 11 | 989 |
Yawn Warm-up | 11 | 1060 |
1 vs 1 Game | 12 | 14 |
3 vs 1 Game | 12 | 45 |
5 vs 1 Game | 12 | 59 |
Arm Pointing | 12 | 666 |
Cover Your Eyes | 12 | 447 |
Delayed Cup Game | 12 | 299 |
Different Perspective | 12 | 9 |
Expression Game | 12 | 13 |
Eye Contact Game | 12 | 760 |
Eye Contact Warm-up | 12 | 839 |
Foot Pointing | 12 | 566 |
Impossible Task Game | 12 | 46 |
Impossible Task Warm-up | 12 | 46 |
Inferential Reasoning Game | 12 | 280 |
Inferential Reasoning Warm-up | 12 | 296 |
Memory versus Pointing | 12 | 376 |
Memory versus Smell | 12 | 352 |
Navigation Game | 12 | 45 |
Navigation Learning | 12 | 47 |
Navigation Warm-up | 12 | 49 |
Numerosity Warm-Up | 12 | 46 |
One Cup Warm-up | 12 | 401 |
Physical Reasoning Game | 12 | 250 |
Physical Reasoning Warm-up | 12 | 276 |
Self Control Game | 12 | 6 |
Shaker Game | 12 | 1 |
Shaker Warm-Up | 12 | 1 |
Shared Perspective | 12 | 7 |
Slide | 12 | 7 |
Smell Game | 12 | 13 |
Stair Game | 12 | 8 |
Switch | 12 | 6 |
Treat Warm-up | 12 | 667 |
Turn Your Back | 12 | 467 |
Two Cup Warm-up | 12 | 379 |
Warm-up | 12 | 56 |
Watching | 12 | 555 |
Watching - Part 2 | 12 | 438 |
Yawn Game | 12 | 978 |
Yawn Warm-up | 12 | 1043 |
Notice that in the first case, the first block of rows share the same test_name, but are broken up into separate months (for those of you who took the "Data Visualization and Communication with Tableau" course of this specialization, this is similar to what would happen if you put test_name first and created_at second on the rows or columns shelf in Tableau).
In the second case, the first block of rows share the same month, but are broken up into separate tests (this is similar to what would happen if you put created_at first and test_name second on the rows or columns shelf in Tableau). If you were to visualize these outputs, they would look like the charts below.
Different database servers might default to ordering the outputs in a certain way, but you shouldn't rely on that being the case. To ensure the output is ordered in a way you intend, add an ORDER BY clause to your grouped query using the syntax you already know and have practiced:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, Month
ORDER BY test_name ASC, Month ASC;
Question 1: Output a table that calculates the number of distinct female and male dogs in each breed group of the Dogs table, sorted by the total number of dogs in descending order (the sex/breed_group pair with the greatest number of dogs should have 8466 unique Dog_Guids):
%%sql
SELECT gender, breed_group, COUNT(dog_guid) AS dog_count
FROM dogs
GROUP BY gender, breed_group
ORDER BY dog_count DESC
18 rows affected.
gender | breed_group | dog_count |
---|---|---|
male | None | 8466 |
female | None | 8367 |
male | Sporting | 2584 |
female | Sporting | 2262 |
male | Herding | 1736 |
female | Herding | 1704 |
male | Toy | 1473 |
female | Toy | 1145 |
male | Non-Sporting | 1098 |
male | Working | 1075 |
female | Non-Sporting | 919 |
male | Terrier | 919 |
female | Working | 895 |
female | Terrier | 794 |
male | Hound | 725 |
female | Hound | 614 |
male | 147 | |
female | 127 |
Some database servers, including MySQL, allow you to use numbers in place of field names in the GROUP BY or ORDER BY fields to reduce the overall length of the queries. I tend to avoid this abbreviated method of writing queries because I find it challenging to troubleshoot when you are writing complicated queries with many fields, but it does allow you to write queries faster. To use this method, assign each field in your SELECT statement a number acording to the order the field appears in the SELECT statement. In the following statement:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
test_name would be #1, Month would be #2, and Num_Completed_Tests would be #3. You could then rewrite the query above to read:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC;
Question 2: Revise the query your wrote in Question 1 so that it uses only numbers in the GROUP BY and ORDER BY fields.
%%sql
SELECT gender, breed_group, COUNT(dog_guid) AS dog_count
FROM dogs
GROUP BY 1, 2
ORDER BY 3 DESC
18 rows affected.
gender | breed_group | dog_count |
---|---|---|
male | None | 8466 |
female | None | 8367 |
male | Sporting | 2584 |
female | Sporting | 2262 |
male | Herding | 1736 |
female | Herding | 1704 |
male | Toy | 1473 |
female | Toy | 1145 |
male | Non-Sporting | 1098 |
male | Working | 1075 |
female | Non-Sporting | 919 |
male | Terrier | 919 |
female | Working | 895 |
female | Terrier | 794 |
male | Hound | 725 |
female | Hound | 614 |
male | 147 | |
female | 127 |
Just like you can query subsets of rows using the WHERE clause, you can query subsets of aggregated groups using the HAVING clause. However, wheras the expression that follows a WHERE clause has to be applicable to each row of data in a column, the expression that follows a HAVING clause has to be applicable or computable using a group of data.
If you wanted to examine the number of tests completed only during the winter holiday months of November and December, you would need to use a WHERE clause, because the month a test was completed in is recorded in each row. Your query might look like this:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
WHERE MONTH(created_at)=11 OR MONTH(created_at)=12
GROUP BY 1, 2
ORDER BY 3 DESC;
If you then wanted to output only the test-month pairs that had at least 20 records in them, you would add a HAVING clause, because the stipulation of at least 20 records only makes sense and is only computable at the aggregated group level:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
WHERE MONTH(created_at)=11 OR MONTH(created_at)=12
GROUP BY 1, 2
HAVING COUNT(created_at)>=20
ORDER BY 3 DESC;
Question 3: Revise the query your wrote in Question 2 so that it (1) excludes the NULL and empty string entries in the breed_group field, and (2) excludes any groups that don't have at least 1,000 distinct Dog_Guids in them. Your result should contain 8 rows. (HINT: sometimes empty strings are registered as non-NULL values. You might want to include the following line somewhere in your query to exclude these values as well):
breed_group!=""
%%sql
SELECT gender, breed_group, COUNT(dog_guid) AS dog_count
FROM dogs
WHERE breed_group != "" AND breed_group IS NOT NULL
GROUP BY gender,breed_group
HAVING dog_count >= 1000
ORDER BY dog_count DESC
8 rows affected.
gender | breed_group | dog_count |
---|---|---|
male | Sporting | 2584 |
female | Sporting | 2262 |
male | Herding | 1736 |
female | Herding | 1704 |
male | Toy | 1473 |
female | Toy | 1145 |
male | Non-Sporting | 1098 |
male | Working | 1075 |
We will review several issues that can be tricky about using GROUP BY in your queries in the next lesson, but those issues will make more sense once you are sure you are comfortable with the basic functionality of the GROUP BY and HAVING clauses.
Question 4: Write a query that outputs the average number of tests completed and average mean inter-test-interval for every breed type, sorted by the average number of completed tests in descending order (popular hybrid should be the first row in your output).
%%sql
SELECT breed_type, AVG(total_tests_completed) AS avg_test_completed, AVG(mean_iti_minutes) AS avg_mean_interval
FROM dogs
WHERE mean_iti_minutes IS NOT NULL
GROUP BY breed_type
ORDER BY avg_test_completed DESC
4 rows affected.
/opt/conda/lib/python3.4/site-packages/SQLAlchemy-1.0.11-py3.4-linux-x86_64.egg/sqlalchemy/engine/default.py:450: Warning: Truncated incorrect DOUBLE value: '#VALUE!' cursor.execute(statement, parameters)
breed_type | avg_test_completed | avg_mean_interval |
---|---|---|
Popular Hybrid | 10.257530120481928 | 2834.3205728931534 |
Cross Breed | 9.945900537634408 | 2872.351156110182 |
Pure Breed | 9.871602824737856 | 3193.350493795222 |
Mixed Breed/ Other/ I Don't Know | 9.54250850170034 | 3023.0711302156274 |
Question 5: Write a query that outputs the average amount of time it took customers to complete each type of test where any individual reaction times over 6000 minutes are excluded and only average reaction times that are greater than 0 are included (your output should end up with 58 rows).
%%sql
SELECT test_name, AVG( TIMESTAMPDIFF( HOUR, start_time, end_time ) ) AS avg_complete_time
FROM exam_answers
WHERE TIMESTAMPDIFF( MINUTE, start_time, end_time ) < 6000
GROUP BY test_name
HAVING avg_complete_time > 0
58 rows affected.
test_name | avg_complete_time |
---|---|
Activity | 0.5096 |
Arm Pointing | 0.0238 |
Attachment | 0.0381 |
Confinement | 0.1757 |
Cover Your Eyes | 0.0555 |
Delayed Cup Game | 0.0158 |
Diet | 0.6735 |
Different Perspective | 0.0493 |
Emotions | 0.1173 |
Environment | 0.0779 |
Excitability | 0.0263 |
Eye Contact Game | 0.0738 |
Eye Contact Warm-up | 0.0513 |
Foot Pointing | 0.0281 |
Gender | 0.0882 |
Impossible Task Game | 0.0238 |
Impossible Task Warm-up | 0.1040 |
Inferential Reasoning Game | 0.0157 |
Inferential Reasoning Warm-up | 0.0336 |
Memory versus Pointing | 0.0057 |
Memory versus Smell | 0.0235 |
Navigation Game | 0.1273 |
Navigation Learning | 0.0226 |
Navigation Warm-up | 0.1637 |
Numerosity Warm-Up | 0.0116 |
Obedience | 0.0169 |
One Cup Warm-up | 0.0472 |
Owner | 0.0567 |
Partnership | 0.0244 |
Perception | 0.0339 |
Physical | 0.1232 |
Physical Reasoning Game | 0.0065 |
Physical Reasoning Warm-up | 0.0351 |
Purina | 0.0327 |
Purina-Only | 0.0430 |
Puzzles | 0.0310 |
Recall | 0.0296 |
Set 1 | 0.0790 |
Set 2 | 0.1259 |
Set 3 | 0.2641 |
Shaker Warm-Up | 0.0644 |
Shy/Boldness | 0.0436 |
Sociability | 0.3241 |
Social | 0.7465 |
Social-Quiz | 2.3382 |
Stair Game | 0.0793 |
Surprise And Delight | 0.2592 |
Switch | 0.0531 |
Toys | 0.0521 |
Training | 0.0550 |
Treat Warm-up | 0.0601 |
Turn Your Back | 0.0759 |
Two Cup Warm-up | 0.0086 |
Warm-Up | 0.0486 |
Watching | 0.2822 |
Watching - Part 2 | 0.0834 |
Yawn Game | 0.1556 |
Yawn Warm-up | 0.5921 |
Question 6: Write a query that outputs the total number of unique User_Guids in each combination of State and ZIP code (postal code) in the United States, sorted first by state name in ascending alphabetical order, and second by total number of unique User_Guids in descending order (your first state should be AE and there should be 5043 rows in total in your output).
%%sql
SELECT state, zip, COUNT( DISTINCT( user_guid ) ) AS unique_users
FROM users
WHERE country = "US"
GROUP BY state, zip
ORDER BY state ASC, COUNT( DISTINCT( user_guid ) ) DESC
5043 rows affected.
state | zip | unique_users |
---|---|---|
AE | 9128 | 2 |
AE | 9053 | 1 |
AE | 9107 | 1 |
AE | 9469 | 1 |
AE | 9845 | 1 |
AK | 99507 | 3 |
AK | 99709 | 3 |
AK | 99577 | 2 |
AK | 99501 | 2 |
AK | 99752 | 1 |
AK | 99518 | 1 |
AK | 99775 | 1 |
AK | 99567 | 1 |
AK | 99824 | 1 |
AK | 99928 | 1 |
AK | 99587 | 1 |
AK | 99611 | 1 |
AK | 99645 | 1 |
AK | 99676 | 1 |
AK | 99502 | 1 |
AK | 99705 | 1 |
AK | 99509 | 1 |
AK | 99712 | 1 |
AK | 99516 | 1 |
AL | 35209 | 4 |
AL | 36532 | 2 |
AL | 36609 | 2 |
AL | 36116 | 2 |
AL | 35801 | 2 |
AL | 36527 | 1 |
AL | 35216 | 1 |
AL | 35803 | 1 |
AL | 35242 | 1 |
AL | 35906 | 1 |
AL | 36561 | 1 |
AL | 35405 | 1 |
AL | 35976 | 1 |
AL | 36602 | 1 |
AL | 35476 | 1 |
AL | 36037 | 1 |
AL | 36604 | 1 |
AL | 35004 | 1 |
AL | 35646 | 1 |
AL | 36104 | 1 |
AL | 35007 | 1 |
AL | 35662 | 1 |
AL | 36106 | 1 |
AL | 36695 | 1 |
AL | 35094 | 1 |
AL | 35674 | 1 |
AL | 36801 | 1 |
AL | 35121 | 1 |
AL | 35758 | 1 |
AL | 36203 | 1 |
AL | 36832 | 1 |
AL | 35179 | 1 |
AL | 35760 | 1 |
AL | 36303 | 1 |
AL | 35205 | 1 |
AL | 35763 | 1 |
AL | 36375 | 1 |
AP | 96367 | 1 |
AP | 96377 | 1 |
AP | 96205 | 1 |
AP | 96310 | 1 |
AR | 72207 | 3 |
AR | 72401 | 3 |
AR | 72712 | 2 |
AR | 72223 | 2 |
AR | 72632 | 2 |
AR | 72202 | 1 |
AR | 72703 | 1 |
AR | 72206 | 1 |
AR | 72704 | 1 |
AR | 71909 | 1 |
AR | 71913 | 1 |
AR | 72212 | 1 |
AR | 72714 | 1 |
AR | 72015 | 1 |
AR | 72756 | 1 |
AR | 72034 | 1 |
AR | 72762 | 1 |
AR | 72042 | 1 |
AR | 72554 | 1 |
AR | 72802 | 1 |
AR | 72058 | 1 |
AR | 72901 | 1 |
AR | 72631 | 1 |
AR | 72113 | 1 |
AR | 72908 | 1 |
AR | 72135 | 1 |
AR | 72956 | 1 |
AR | 72201 | 1 |
AR | 72701 | 1 |
AZ | 86303 | 14 |
AZ | 85718 | 6 |
AZ | 85253 | 5 |
AZ | 85254 | 5 |
AZ | 85260 | 5 |
AZ | 85711 | 5 |
AZ | 85749 | 5 |
AZ | 85750 | 4 |
AZ | 85716 | 4 |
AZ | 85719 | 4 |
AZ | N/A | 4 |
AZ | 85266 | 4 |
AZ | 85282 | 3 |
AZ | 85283 | 3 |
AZ | 85032 | 3 |
AZ | 85255 | 3 |
AZ | 85042 | 3 |
AZ | 85257 | 3 |
AZ | 85044 | 3 |
AZ | 85296 | 3 |
AZ | 85737 | 3 |
AZ | 86305 | 3 |
AZ | 85048 | 3 |
AZ | 85233 | 3 |
AZ | 85021 | 3 |
AZ | 85392 | 3 |
AZ | 85022 | 3 |
AZ | 85250 | 3 |
AZ | 85251 | 2 |
AZ | 85715 | 2 |
AZ | 85028 | 2 |
AZ | 85206 | 2 |
AZ | 86351 | 2 |
AZ | 85374 | 2 |
AZ | 85259 | 2 |
AZ | 85045 | 2 |
AZ | 85739 | 2 |
AZ | 85304 | 2 |
AZ | 85704 | 2 |
AZ | 85143 | 2 |
AZ | 85745 | 2 |
AZ | 85202 | 2 |
AZ | 85024 | 2 |
AZ | 85748 | 2 |
AZ | 85203 | 2 |
AZ | 85615 | 2 |
AZ | 85712 | 2 |
AZ | 85281 | 2 |
AZ | 85086 | 2 |
AZ | 85006 | 1 |
AZ | 85338 | 1 |
AZ | 86327 | 1 |
AZ | 85205 | 1 |
AZ | 85622 | 1 |
AZ | 85118 | 1 |
AZ | 85008 | 1 |
AZ | 85340 | 1 |
AZ | 86336 | 1 |
AZ | 85635 | 1 |
AZ | 85029 | 1 |
AZ | 85755 | 1 |
AZ | 85119 | 1 |
AZ | 85012 | 1 |
AZ | 85353 | 1 |
AZ | 85207 | 1 |
AZ | 85636 | 1 |
AZ | 85284 | 1 |
AZ | 86002 | 1 |
AZ | 85120 | 1 |
AZ | 85013 | 1 |
AZ | 85365 | 1 |
AZ | 86403 | 1 |
AZ | 85209 | 1 |
AZ | 85641 | 1 |
AZ | 85286 | 1 |
AZ | 86301 | 1 |
AZ | 85122 | 1 |
AZ | 85015 | 1 |
AZ | 86426 | 1 |
AZ | 85210 | 1 |
AZ | 85646 | 1 |
AZ | 85730 | 1 |
AZ | 85128 | 1 |
AZ | 85016 | 1 |
AZ | 85382 | 1 |
AZ | 86504 | 1 |
AZ | 85212 | 1 |
AZ | 85650 | 1 |
AZ | 85303 | 1 |
AZ | 85138 | 1 |
AZ | 85020 | 1 |
AZ | 85226 | 1 |
AZ | 85658 | 1 |
AZ | 86312 | 1 |
AZ | 85140 | 1 |
AZ | 85385 | 1 |
AZ | 85262 | 1 |
AZ | 85743 | 1 |
AZ | 85050 | 1 |
AZ | 85308 | 1 |
AZ | 86314 | 1 |
AZ | 85248 | 1 |
AZ | 85710 | 1 |
AZ | 85069 | 1 |
AZ | 85327 | 1 |
AZ | 86323 | 1 |
AZ | 85614 | 1 |
AZ | 85249 | 1 |
AZ | 85268 | 1 |
AZ | 85083 | 1 |
AZ | 85335 | 1 |
AZ | 86326 | 1 |
AZ | 85027 | 1 |
CA | 92107 | 16 |
CA | 90046 | 13 |
CA | 94107 | 12 |
CA | 92130 | 12 |
CA | 94110 | 10 |
CA | 92024 | 10 |
CA | 94941 | 9 |
CA | 94611 | 9 |
CA | 94114 | 9 |
CA | 94025 | 9 |
CA | 92064 | 8 |
CA | 90068 | 8 |
CA | 94131 | 8 |
CA | 90069 | 8 |
CA | 90049 | 8 |
CA | 90278 | 7 |
CA | 94121 | 7 |
CA | 90291 | 7 |
CA | 90292 | 7 |
CA | 90266 | 7 |
CA | 90803 | 7 |
CA | 93003 | 7 |
CA | 94602 | 7 |
CA | 94117 | 7 |
CA | 94549 | 6 |
CA | 90036 | 6 |
CA | 92672 | 6 |
CA | 94061 | 6 |
CA | 94123 | 6 |
CA | 90039 | 6 |
CA | 91301 | 6 |
CA | 95762 | 6 |
CA | 92103 | 6 |
CA | 92071 | 6 |
CA | 94901 | 6 |
CA | 95008 | 6 |
CA | 94903 | 6 |
CA | 94952 | 6 |
CA | 95014 | 6 |
CA | 90274 | 6 |
CA | 90277 | 6 |
CA | 93012 | 5 |
CA | 95476 | 5 |
CA | 94550 | 5 |
CA | 94706 | 5 |
CA | 92101 | 5 |
CA | 92009 | 5 |
CA | 94510 | 5 |
CA | 90232 | 5 |
CA | 93023 | 5 |
CA | 91406 | 5 |
CA | 94109 | 5 |
CA | 95003 | 5 |
CA | 90025 | 5 |
CA | 94597 | 5 |
CA | 93940 | 5 |
CA | 92106 | 5 |
CA | 95818 | 5 |
CA | 92122 | 5 |
CA | 94904 | 5 |
CA | 92211 | 5 |
CA | 94040 | 5 |
CA | 90210 | 5 |
CA | 90405 | 5 |
CA | 93105 | 5 |
CA | 94118 | 5 |
CA | 94506 | 4 |
CA | 90212 | 4 |
CA | 93561 | 4 |
CA | 92660 | 4 |
CA | 90034 | 4 |
CA | 95125 | 4 |
CA | 92026 | 4 |
CA | 92126 | 4 |
CA | 95608 | 4 |
CA | 94965 | 4 |
CA | 92116 | 4 |
CA | 92128 | 4 |
CA | 94306 | 4 |
CA | 94127 | 4 |
CA | 90720 | 4 |
CA | 95616 | 4 |
CA | 92117 | 4 |
CA | 92067 | 4 |
CA | 94062 | 4 |
CA | 94403 | 4 |
CA | 92677 | 4 |
CA | 91307 | 4 |
CA | 92612 | 4 |
CA | 95401 | 4 |
CA | 90265 | 4 |
CA | 92649 | 4 |
CA | 95403 | 4 |
CA | 94558 | 4 |
CA | 91941 | 4 |
CA | 94501 | 4 |
CA | 90004 | 4 |
CA | 90026 | 4 |
CA | 92886 | 4 |
CA | 95404 | 4 |
CA | 92078 | 4 |
CA | 91942 | 4 |
CA | 90027 | 4 |
CA | 90404 | 4 |
CA | 94523 | 4 |
CA | 91320 | 4 |
CA | 91367 | 4 |
CA | 92656 | 4 |
CA | 93103 | 4 |
CA | 92109 | 4 |
CA | 94583 | 4 |
CA | 95407 | 4 |
CA | 94703 | 4 |
CA | 95472 | 4 |
CA | 90211 | 4 |
CA | 91030 | 4 |
CA | 94010 | 3 |
CA | 90807 | 3 |
CA | 94928 | 3 |
CA | 92111 | 3 |
CA | 91208 | 3 |
CA | 94705 | 3 |
CA | 90064 | 3 |
CA | 95648 | 3 |
CA | 92629 | 3 |
CA | 91403 | 3 |
CA | 92008 | 3 |
CA | 92115 | 3 |
CA | 94607 | 3 |
CA | 90065 | 3 |
CA | 92867 | 3 |
CA | 95492 | 3 |
CA | 91101 | 3 |
CA | 93907 | 3 |
CA | 92603 | 3 |
CA | 94551 | 3 |
CA | 92646 | 3 |
CA | 95032 | 3 |
CA | 94568 | 3 |
CA | 92037 | 3 |
CA | 92129 | 3 |
CA | 94536 | 3 |
CA | 94609 | 3 |
CA | 93035 | 3 |
CA | 95926 | 3 |
CA | 90731 | 3 |
CA | 95136 | 3 |
CA | 95618 | 3 |
CA | 95682 | 3 |
CA | 95448 | 3 |
CA | 93230 | 3 |
CA | 94949 | 3 |
CA | 90024 | 3 |
CA | 93924 | 3 |
CA | 92131 | 3 |
CA | 91001 | 3 |
CA | 93041 | 3 |
CA | 92562 | 3 |
CA | 92019 | 3 |
CA | 91311 | 3 |
CA | 91786 | 3 |
CA | 95621 | 3 |
CA | 92618 | 3 |
CA | 94618 | 3 |
CA | 90403 | 3 |
CA | 94598 | 3 |
CA | 94115 | 3 |
CA | 94619 | 3 |
CA | 91364 | 3 |
CA | 94027 | 3 |
CA | 93546 | 3 |
CA | 93065 | 3 |
CA | 95051 | 3 |
CA | 95819 | 3 |
CA | 92691 | 3 |
CA | 93711 | 3 |
CA | 93955 | 3 |
CA | 92056 | 3 |
CA | 92081 | 3 |
CA | 92592 | 3 |
CA | 91321 | 3 |
CA | 94002 | 3 |
CA | 95630 | 3 |
CA | 92057 | 3 |
CA | 95062 | 3 |
CA | 92084 | 3 |
CA | 94563 | 3 |
CA | 94301 | 2 |
CA | 92835 | 2 |
CA | 93444 | 2 |
CA | 96161 | 2 |
CA | 92627 | 2 |
CA | 95409 | 2 |
CA | 95065 | 2 |
CA | 94564 | 2 |
CA | 91387 | 2 |
CA | 93309 | 2 |
CA | 94530 | 2 |
CA | 90808 | 2 |
CA | N/A | 2 |
CA | 93013 | 2 |
CA | 95070 | 2 |
CA | 95482 | 2 |
CA | 94930 | 2 |
CA | 94507 | 2 |
CA | 91604 | 2 |
CA | 93110 | 2 |
CA | 95835 | 2 |
CA | 90638 | 2 |
CA | 96022 | 2 |
CA | 95030 | 2 |
CA | 92506 | 2 |
CA | 94566 | 2 |
CA | 94939 | 2 |
CA | 91606 | 2 |
CA | 95843 | 2 |
CA | 94534 | 2 |
CA | 94608 | 2 |
CA | 94707 | 2 |
CA | 91335 | 2 |
CA | 94015 | 2 |
CA | 90066 | 2 |
CA | 90813 | 2 |
CA | 95437 | 2 |
CA | 92065 | 2 |
CA | 92673 | 2 |
CA | 95110 | 2 |
CA | 92010 | 2 |
CA | 94515 | 2 |
CA | 91104 | 2 |
CA | 91607 | 2 |
CA | 93908 | 2 |
CA | 93401 | 2 |
CA | 95134 | 2 |
CA | 92606 | 2 |
CA | 91302 | 2 |
CA | 94553 | 2 |
CA | 91354 | 2 |
CA | 95678 | 2 |
CA | 92647 | 2 |
CA | 95033 | 2 |
CA | 95112 | 2 |
CA | 91105 | 2 |
CA | 93923 | 2 |
CA | 94129 | 2 |
CA | 92780 | 2 |
CA | 93402 | 2 |
CA | 94610 | 2 |
CA | 91355 | 2 |
CA | 94022 | 2 |
CA | 90815 | 2 |
CA | 92880 | 2 |
CA | 93036 | 2 |
CA | 92104 | 2 |
CA | 91423 | 2 |
CA | 92014 | 2 |
CA | 94519 | 2 |
CA | 91106 | 2 |
CA | 92782 | 2 |
CA | 95620 | 2 |
CA | 95005 | 2 |
CA | 94539 | 2 |
CA | 92270 | 2 |
CA | 94024 | 2 |
CA | 94070 | 2 |
CA | 90402 | 2 |
CA | 95816 | 2 |
CA | 94132 | 2 |
CA | 90045 | 2 |
CA | 90755 | 2 |
CA | 92120 | 2 |
CA | 92881 | 2 |
CA | 92075 | 2 |
CA | 93536 | 2 |
CA | 95691 | 2 |
CA | 93063 | 2 |
CA | 95460 | 2 |
CA | 92020 | 2 |
CA | 94521 | 2 |
CA | 94133 | 2 |
CA | 95959 | 2 |
CA | 93422 | 2 |
CA | 95010 | 2 |
CA | 92054 | 2 |
CA | 94559 | 2 |
CA | 94502 | 2 |
CA | 90272 | 2 |
CA | 92653 | 2 |
CA | 93291 | 2 |
CA | 94582 | 2 |
CA | 94954 | 2 |
CA | 91502 | 2 |
CA | 91730 | 2 |
CA | 94116 | 2 |
CA | 92373 | 2 |
CA | 94920 | 2 |
CA | 91945 | 2 |
CA | 90012 | 2 |
CA | 90029 | 2 |
CA | 91505 | 2 |
CA | 92123 | 2 |
CA | 94526 | 2 |
CA | 94158 | 2 |
CA | 92626 | 2 |
CA | 93010 | 2 |
CA | 92003 | 2 |
CA | 94041 | 2 |
CA | 94505 | 2 |
CA | 94089 | 2 |
CA | 95695 | 2 |
CA | 92657 | 2 |
CA | 90503 | 2 |
CA | 92694 | 2 |
CA | 93308 | 2 |
CA | 95124 | 2 |
CA | 92110 | 2 |
CA | 94960 | 2 |
CA | 95354 | 2 |
CA | 92124 | 2 |
CA | 91207 | 2 |
CA | 94547 | 1 |
CA | 94704 | 1 |
CA | 91324 | 1 |
CA | 91901 | 1 |
CA | 96002 | 1 |
CA | 90051 | 1 |
CA | 95633 | 1 |
CA | 95020 | 1 |
CA | 92058 | 1 |
CA | 92232 | 1 |
CA | 92091 | 1 |
CA | 92503 | 1 |
CA | 92007 | 1 |
CA | 94043 | 1 |
CA | 91041 | 1 |
CA | 94102 | 1 |
CA | 95722 | 1 |
CA | 90015 | 1 |
CA | 93108 | 1 |
CA | 95834 | 1 |
CA | 90504 | 1 |
CA | 92701 | 1 |
CA | 95605 | 1 |
CA | 92595 | 1 |
CA | 94963 | 1 |
CA | 95355 | 1 |
CA | 94606 | 1 |
CA | 91750 | 1 |
CA | 93726 | 1 |
CA | 91325 | 1 |
CA | 91902 | 1 |
CA | 94011 | 1 |
CA | 94303 | 1 |
CA | 96003 | 1 |
CA | 92865 | 1 |
CA | 93446 | 1 |
CA | 95023 | 1 |
CA | 95425 | 1 |
CA | 92059 | 1 |
CA | 92247 | 1 |
CA | 92092 | 1 |
CA | 92504 | 1 |
CA | 94565 | 1 |
CA | 94044 | 1 |
CA | 94588 | 1 |
CA | 90230 | 1 |
CA | 91042 | 1 |
CA | 93562 | 1 |
CA | 94103 | 1 |
CA | 95746 | 1 |
CA | 90016 | 1 |
CA | 90290 | 1 |
CA | 92663 | 1 |
CA | 93730 | 1 |
CA | 94122 | 1 |
CA | 92705 | 1 |
CA | 93311 | 1 |
CA | 95128 | 1 |
CA | 92602 | 1 |
CA | 95356 | 1 |
CA | 92027 | 1 |
CA | 92127 | 1 |
CA | 94531 | 1 |
CA | 91214 | 1 |
CA | 91755 | 1 |
CA | 91326 | 1 |
CA | 91908 | 1 |
CA | 94014 | 1 |
CA | 94304 | 1 |
CA | 90810 | 1 |
CA | 93447 | 1 |
CA | 95650 | 1 |
CA | 92637 | 1 |
CA | 93021 | 1 |
CA | 95436 | 1 |
CA | 92253 | 1 |
CA | 93117 | 1 |
CA | 95076 | 1 |
CA | 91405 | 1 |
CA | 94590 | 1 |
CA | 93611 | 1 |
CA | 95747 | 1 |
CA | 90017 | 1 |
CA | 90704 | 1 |
CA | 92707 | 1 |
CA | 93312 | 1 |
CA | 95132 | 1 |
CA | 95610 | 1 |
CA | 94966 | 1 |
CA | 95361 | 1 |
CA | 92028 | 1 |
CA | 91764 | 1 |
CA | 92260 | 1 |
CA | 91910 | 1 |
CA | 96067 | 1 |
CA | 92869 | 1 |
CA | 93452 | 1 |
CA | 95677 | 1 |
CA | 93150 | 1 |
CA | 95501 | 1 |
CA | 92102 | 1 |
CA | 92516 | 1 |
CA | 94591 | 1 |
CA | 93619 | 1 |
CA | 90019 | 1 |
CA | 95864 | 1 |
CA | 90041 | 1 |
CA | 92708 | 1 |
CA | 94970 | 1 |
CA | 95366 | 1 |
CA | 91770 | 1 |
CA | 92262 | 1 |
CA | 94708 | 1 |
CA | 91913 | 1 |
CA | 94018 | 1 |
CA | 94402 | 1 |
CA | 96073 | 1 |
CA | 90067 | 1 |
CA | 90814 | 1 |
CA | 92870 | 1 |
CA | 93458 | 1 |
CA | 90242 | 1 |
CA | 95442 | 1 |
CA | 92675 | 1 |
CA | 93225 | 1 |
CA | 95503 | 1 |
CA | 92545 | 1 |
CA | 94571 | 1 |
CA | 94947 | 1 |
CA | 91411 | 1 |
CA | 92011 | 1 |
CA | 94518 | 1 |
CA | 94595 | 1 |
CA | 91701 | 1 |
CA | 93637 | 1 |
CA | 95765 | 1 |
CA | 90020 | 1 |
CA | 90293 | 1 |
CA | 91775 | 1 |
CA | 90042 | 1 |
CA | 92118 | 1 |
CA | 92610 | 1 |
CA | 95383 | 1 |
CA | 92038 | 1 |
CA | 94538 | 1 |
CA | 91303 | 1 |
CA | 92263 | 1 |
CA | 94555 | 1 |
CA | 94709 | 1 |
CA | 91921 | 1 |
CA | 96104 | 1 |
CA | 93463 | 1 |
CA | 94063 | 1 |
CA | 90247 | 1 |
CA | 92648 | 1 |
CA | 95037 | 1 |
CA | 95113 | 1 |
CA | 95519 | 1 |
CA | 92549 | 1 |
CA | 94574 | 1 |
CA | 94596 | 1 |
CA | 91702 | 1 |
CA | 93643 | 1 |
CA | 94112 | 1 |
CA | 95811 | 1 |
CA | 90401 | 1 |
CA | 91776 | 1 |
CA | 95945 | 1 |
CA | 90043 | 1 |
CA | 90740 | 1 |
CA | 93405 | 1 |
CA | 95138 | 1 |
CA | 92119 | 1 |
CA | 92040 | 1 |
CA | 92072 | 1 |
CA | 94556 | 1 |
CA | 91356 | 1 |
CA | 91931 | 1 |
CA | 94404 | 1 |
CA | 93534 | 1 |
CA | 95688 | 1 |
CA | 95046 | 1 |
CA | 95457 | 1 |
CA | 92683 | 1 |
CA | 93243 | 1 |
CA | 95116 | 1 |
CA | 95536 | 1 |
CA | 92105 | 1 |
CA | 94577 | 1 |
CA | 94951 | 1 |
CA | 91436 | 1 |
CA | 94520 | 1 |
CA | 91201 | 1 |
CA | 91711 | 1 |
CA | 93654 | 1 |
CA | 95949 | 1 |
CA | 92806 | 1 |
CA | 93420 | 1 |
CA | 95212 | 1 |
CA | 96107 | 1 |
CA | 92046 | 1 |
CA | 92143 | 1 |
CA | 94542 | 1 |
CA | 92307 | 1 |
CA | 91361 | 1 |
CA | 90071 | 1 |
CA | 91006 | 1 |
CA | 94080 | 1 |
CA | 92651 | 1 |
CA | 95050 | 1 |
CA | 92688 | 1 |
CA | 93257 | 1 |
CA | 95119 | 1 |
CA | 95540 | 1 |
CA | 92586 | 1 |
CA | 94580 | 1 |
CA | 91501 | 1 |
CA | 91203 | 1 |
CA | 91723 | 1 |
CA | 93710 | 1 |
CA | 91316 | 1 |
CA | 91789 | 1 |
CA | 93953 | 1 |
CA | 92807 | 1 |
CA | 95227 | 1 |
CA | 95624 | 1 |
CA | 96141 | 1 |
CA | 92620 | 1 |
CA | 92163 | 1 |
CA | 94544 | 1 |
CA | 92320 | 1 |
CA | 90077 | 1 |
CA | 91011 | 1 |
CA | 94086 | 1 |
CA | 95692 | 1 |
CA | 90005 | 1 |
CA | 95465 | 1 |
CA | 95120 | 1 |
CA | 95570 | 1 |
CA | 92590 | 1 |
CA | 92021 | 1 |
CA | 94599 | 1 |
CA | 91204 | 1 |
CA | 91791 | 1 |
CA | 94134 | 1 |
CA | 95965 | 1 |
CA | 90048 | 1 |
CA | 90804 | 1 |
CA | 92821 | 1 |
CA | 93428 | 1 |
CA | 95628 | 1 |
CA | 96145 | 1 |
CA | 92625 | 1 |
CA | 95405 | 1 |
CA | 94545 | 1 |
CA | 94702 | 1 |
CA | 95470 | 1 |
CA | 94560 | 1 |
CA | 94503 | 1 |
CA | 91016 | 1 |
CA | 93549 | 1 |
CA | 94087 | 1 |
CA | 95694 | 1 |
CA | 95060 | 1 |
CA | 95831 | 1 |
CA | 92692 | 1 |
CA | 93292 | 1 |
CA | 95123 | 1 |
CA | 95602 | 1 |
CA | 94955 | 1 |
CA | 95236 | 1 |
CA | 91205 | 1 |
CA | 91737 | 1 |
CA | 93720 | 1 |
CA | 91801 | 1 |
CA | 96001 | 1 |
CA | 90806 | 1 |
CA | 92831 | 1 |
CA | 93443 | 1 |
CA | 96151 | 1 |
CA | 95018 | 1 |
CA | 92223 | 1 |
CA | 94546 | 1 |
CA | 92374 | 1 |
CA | 94925 | 1 |
CA | 91377 | 1 |
CA | 93550 | 1 |
CA | 90014 | 1 |
CA | 95833 | 1 |
CA | 90031 | 1 |
CA | 95603 | 1 |
CA | 92593 | 1 |
CA | 94585 | 1 |
CA | 91602 | 1 |
CA | 92025 | 1 |
CA | 94528 | 1 |
CA | 94605 | 1 |
CA | 91748 | 1 |
CA | 93722 | 1 |
CO | 80304 | 10 |
CO | 80210 | 9 |
CO | 80303 | 7 |
CO | 80526 | 7 |
CO | 80220 | 6 |
CO | 80403 | 6 |
CO | 80205 | 5 |
CO | 80126 | 5 |
CO | 80206 | 5 |
CO | 80227 | 5 |
CO | 80401 | 5 |
CO | 80237 | 4 |
CO | 81601 | 4 |
CO | 80439 | 4 |
CO | 80020 | 4 |
CO | 80521 | 4 |
CO | 80021 | 4 |
CO | 81620 | 4 |
CO | 80127 | 4 |
CO | 80111 | 4 |
CO | 80128 | 4 |
CO | 80026 | 4 |
CO | 80132 | 4 |
CO | 80211 | 4 |
CO | 80134 | 4 |
CO | 80922 | 4 |
CO | 80015 | 3 |
CO | 80904 | 3 |
CO | 80906 | 3 |
CO | 80302 | 3 |
CO | 80525 | 3 |
CO | 80212 | 3 |
CO | 80503 | 3 |
CO | 80014 | 3 |
CO | 80234 | 2 |
CO | 80827 | 2 |
CO | 80923 | 2 |
CO | 80203 | 2 |
CO | 80218 | 2 |
CO | 80238 | 2 |
CO | 81611 | 2 |
CO | 80247 | 2 |
CO | 80222 | 2 |
CO | 80301 | 2 |
CO | 80004 | 2 |
CO | 80524 | 2 |
CO | 80908 | 2 |
CO | 80005 | 2 |
CO | 80112 | 2 |
CO | 81623 | 2 |
CO | 80129 | 2 |
CO | 80113 | 2 |
CO | 80917 | 2 |
CO | 80027 | 2 |
CO | 80918 | 2 |
CO | 81301 | 2 |
CO | 80120 | 2 |
CO | 80919 | 2 |
CO | 80138 | 2 |
CO | 80214 | 2 |
CO | 81501 | 2 |
CO | 80122 | 1 |
CO | 80422 | 1 |
CO | 80504 | 1 |
CO | 80106 | 1 |
CO | 81507 | 1 |
CO | 80216 | 1 |
CO | 80540 | 1 |
CO | 80829 | 1 |
CO | 80123 | 1 |
CO | 80430 | 1 |
CO | 80962 | 1 |
CO | 80204 | 1 |
CO | 80516 | 1 |
CO | 80016 | 1 |
CO | 80107 | 1 |
CO | 80542 | 1 |
CO | 80864 | 1 |
CO | 80124 | 1 |
CO | 80000 | 1 |
CO | 81007 | 1 |
CO | 80517 | 1 |
CO | 80108 | 1 |
CO | 80547 | 1 |
CO | 80002 | 1 |
CO | 81131 | 1 |
CO | 80109 | 1 |
CO | 80602 | 1 |
CO | 80465 | 1 |
CO | 81201 | 1 |
CO | 80207 | 1 |
CO | 80022 | 1 |
CO | 81621 | 1 |
CO | 80223 | 1 |
CO | 80631 | 1 |
CO | 80466 | 1 |
CO | 81235 | 1 |
CO | 80209 | 1 |
CO | 80023 | 1 |
CO | 80226 | 1 |
CO | 80634 | 1 |
CO | 80911 | 1 |
CO | 80487 | 1 |
CO | 80010 | 1 |
CO | 81236 | 1 |
CO | 80643 | 1 |
CO | 81625 | 1 |
CO | 80488 | 1 |
CO | 80011 | 1 |
CO | 81252 | 1 |
CO | 80527 | 1 |
CO | 80228 | 1 |
CO | 80644 | 1 |
CO | 80116 | 1 |
CO | 81639 | 1 |
CO | 80308 | 1 |
CO | 80498 | 1 |
CO | 80012 | 1 |
CO | 80031 | 1 |
CO | 80528 | 1 |
CO | 80230 | 1 |
CO | 80808 | 1 |
CO | 81652 | 1 |
CO | 80501 | 1 |
CO | 80013 | 1 |
CO | 80033 | 1 |
CO | 81403 | 1 |
CO | 80534 | 1 |
CO | 80233 | 1 |
CO | 80813 | 1 |
CO | 80121 | 1 |
CO | 80202 | 1 |
CO | 80104 | 1 |
CO | 80215 | 1 |
CO | 80537 | 1 |
CT | 6880 | 8 |
CT | 6824 | 6 |
CT | 6840 | 6 |
CT | 6614 | 5 |
CT | 6820 | 5 |
CT | 6107 | 4 |
CT | 6109 | 4 |
CT | 6902 | 4 |
CT | 6877 | 3 |
CT | 6410 | 3 |
CT | 6517 | 3 |
CT | 6851 | 3 |
CT | 6484 | 3 |
CT | 6903 | 3 |
CT | 6117 | 3 |
CT | 6371 | 3 |
CT | 6489 | 3 |
CT | 6905 | 3 |
CT | 6010 | 2 |
CT | 6073 | 2 |
CT | 6612 | 2 |
CT | 6511 | 2 |
CT | 6811 | 2 |
CT | 6512 | 2 |
CT | 6405 | 2 |
CT | 6033 | 2 |
CT | 6883 | 2 |
CT | 6415 | 2 |
CT | 6830 | 2 |
CT | 6035 | 2 |
CT | 6762 | 2 |
CT | 6416 | 2 |
CT | 6340 | 2 |
CT | 6776 | 2 |
CT | 6040 | 2 |
CT | 6478 | 2 |
CT | 6777 | 2 |
CT | 6422 | 2 |
CT | 6480 | 2 |
Question 7: Write a query that outputs the total number of unique User_Guids in each combination of State and ZIP code in the United States that have at least 5 users, sorted first by state name in ascending alphabetical order, and second by total number of unique User_Guids in descending order (your first state/ZIP code combination should be AZ/86303).
%%sql
SELECT state, zip, COUNT( DISTINCT(user_guid) ) AS total_users
FROM users
WHERE country = "US"
GROUP BY state, zip
HAVING total_users >= 5
ORDER BY state ASC, total_users DESC
285 rows affected.
state | zip | total_users |
---|---|---|
AZ | 86303 | 14 |
AZ | 85718 | 6 |
AZ | 85254 | 5 |
AZ | 85260 | 5 |
AZ | 85711 | 5 |
AZ | 85749 | 5 |
AZ | 85253 | 5 |
CA | 92107 | 16 |
CA | 90046 | 13 |
CA | 94107 | 12 |
CA | 92130 | 12 |
CA | 94110 | 10 |
CA | 92024 | 10 |
CA | 94114 | 9 |
CA | 94941 | 9 |
CA | 94025 | 9 |
CA | 94611 | 9 |
CA | 90068 | 8 |
CA | 90069 | 8 |
CA | 94131 | 8 |
CA | 92064 | 8 |
CA | 90049 | 8 |
CA | 90803 | 7 |
CA | 93003 | 7 |
CA | 94117 | 7 |
CA | 94121 | 7 |
CA | 90266 | 7 |
CA | 94602 | 7 |
CA | 90278 | 7 |
CA | 90291 | 7 |
CA | 90292 | 7 |
CA | 91301 | 6 |
CA | 92103 | 6 |
CA | 94901 | 6 |
CA | 94903 | 6 |
CA | 92672 | 6 |
CA | 94123 | 6 |
CA | 94952 | 6 |
CA | 90274 | 6 |
CA | 90277 | 6 |
CA | 90036 | 6 |
CA | 94549 | 6 |
CA | 90039 | 6 |
CA | 94061 | 6 |
CA | 95762 | 6 |
CA | 92071 | 6 |
CA | 95008 | 6 |
CA | 95014 | 6 |
CA | 92009 | 5 |
CA | 92101 | 5 |
CA | 94510 | 5 |
CA | 94706 | 5 |
CA | 94109 | 5 |
CA | 92106 | 5 |
CA | 93940 | 5 |
CA | 90210 | 5 |
CA | 94904 | 5 |
CA | 93012 | 5 |
CA | 94118 | 5 |
CA | 90232 | 5 |
CA | 93023 | 5 |
CA | 90025 | 5 |
CA | 94597 | 5 |
CA | 92122 | 5 |
CA | 94040 | 5 |
CA | 93105 | 5 |
CA | 95476 | 5 |
CA | 91406 | 5 |
CA | 94550 | 5 |
CA | 95003 | 5 |
CA | 92211 | 5 |
CA | 95818 | 5 |
CA | 90405 | 5 |
CO | 80304 | 10 |
CO | 80210 | 9 |
CO | 80526 | 7 |
CO | 80303 | 7 |
CO | 80403 | 6 |
CO | 80220 | 6 |
CO | 80205 | 5 |
CO | 80206 | 5 |
CO | 80227 | 5 |
CO | 80126 | 5 |
CO | 80401 | 5 |
CT | 6880 | 8 |
CT | 6824 | 6 |
CT | 6840 | 6 |
CT | 6614 | 5 |
CT | 6820 | 5 |
DC | 20009 | 14 |
DC | 20002 | 8 |
DC | 20007 | 8 |
DC | 20001 | 7 |
DC | 20016 | 7 |
DC | 20005 | 6 |
DC | 20011 | 6 |
DC | 20003 | 5 |
DE | 19958 | 7 |
FL | 33418 | 6 |
FL | 32312 | 6 |
FL | 33647 | 6 |
FL | 33156 | 5 |
FL | 32751 | 5 |
FL | 32765 | 5 |
GA | 30004 | 7 |
GA | 30305 | 7 |
GA | 30345 | 6 |
GA | 30308 | 5 |
GA | 30316 | 5 |
GA | 30328 | 5 |
GA | 30067 | 5 |
HI | 96734 | 8 |
ID | 83702 | 9 |
IL | 60614 | 18 |
IL | 60654 | 11 |
IL | 60640 | 9 |
IL | 60657 | 9 |
IL | 60613 | 8 |
IL | 60610 | 7 |
IL | 60660 | 7 |
IL | 60618 | 6 |
IL | 60647 | 6 |
IL | 60626 | 5 |
IL | 60642 | 5 |
IL | 60093 | 5 |
IN | 47401 | 6 |
KS | 66062 | 6 |
LA | 70115 | 8 |
LA | 70118 | 6 |
MA | 2127 | 8 |
MA | 2129 | 6 |
MA | 2135 | 5 |
MA | 2143 | 5 |
MA | 2472 | 5 |
MA | 2116 | 5 |
MA | 2130 | 5 |
MD | 20854 | 8 |
MD | 20910 | 7 |
MD | 20817 | 7 |
MD | 21286 | 6 |
MD | 20878 | 6 |
MD | 20895 | 5 |
MD | 20852 | 5 |
MD | 21228 | 5 |
MD | 21230 | 5 |
MI | 48103 | 8 |
MN | 55426 | 6 |
MO | 63139 | 6 |
MO | 63122 | 5 |
MT | 59715 | 5 |
N/A | N/A | 83 |
NC | 27701 | 32 |
NC | 27705 | 26 |
NC | 27707 | 17 |
NC | 27516 | 17 |
NC | 27615 | 11 |
NC | 27517 | 11 |
NC | 27613 | 11 |
NC | 27713 | 10 |
NC | 27617 | 9 |
NC | 27278 | 9 |
NC | 27539 | 9 |
NC | 27514 | 9 |
NC | 27519 | 7 |
NC | 27614 | 7 |
NC | 27608 | 7 |
NC | 27513 | 7 |
NC | 27609 | 7 |
NC | 28787 | 6 |
NC | 28270 | 6 |
NC | 27587 | 6 |
NC | 27410 | 6 |
NC | 27603 | 6 |
NC | 27607 | 6 |
NC | 28374 | 6 |
NC | 27104 | 6 |
NC | 27612 | 6 |
NC | 28205 | 5 |
NC | 28226 | 5 |
NC | 27502 | 5 |
NC | 27606 | 5 |
NJ | 7030 | 8 |
NJ | 7302 | 7 |
NJ | 8540 | 5 |
NM | 87111 | 6 |
NV | 89511 | 5 |
NY | 10003 | 14 |
NY | 10023 | 13 |
NY | 10024 | 13 |
NY | 10025 | 12 |
NY | 11201 | 12 |
NY | 10028 | 11 |
NY | 10016 | 10 |
NY | 10019 | 9 |
NY | 10011 | 9 |
NY | 10021 | 8 |
NY | 11238 | 8 |
NY | 14850 | 8 |
NY | 10014 | 8 |
NY | 11249 | 7 |
NY | 10128 | 6 |
NY | 10583 | 6 |
NY | 10012 | 6 |
NY | 10013 | 6 |
NY | 11217 | 6 |
NY | 10708 | 5 |
NY | 11222 | 5 |
NY | 10022 | 5 |
NY | 11105 | 5 |
NY | 10075 | 5 |
NY | 11209 | 5 |
NY | 11211 | 5 |
NY | 11215 | 5 |
OH | 43214 | 8 |
OH | 43068 | 5 |
OH | 45243 | 5 |
OH | 45040 | 5 |
OH | 44149 | 5 |
OH | 43235 | 5 |
OK | 73013 | 5 |
OR | 97229 | 8 |
OR | 97212 | 7 |
OR | 97702 | 5 |
OR | 97405 | 5 |
OR | 97206 | 5 |
OR | 97215 | 5 |
PA | 19460 | 6 |
PA | 19147 | 6 |
PA | 19087 | 6 |
PA | 15237 | 5 |
PA | 15101 | 5 |
SC | 29303 | 14 |
SC | 29464 | 13 |
SC | 29407 | 5 |
SC | 29707 | 5 |
SC | 29205 | 5 |
TN | 37205 | 7 |
TN | 37027 | 6 |
TN | 37067 | 6 |
TX | 78745 | 8 |
TX | 77008 | 8 |
TX | 78750 | 8 |
TX | 77007 | 7 |
TX | 78703 | 7 |
TX | 77584 | 6 |
TX | 78757 | 6 |
TX | 78704 | 6 |
TX | 77006 | 5 |
TX | 75248 | 5 |
TX | 78748 | 5 |
TX | 77381 | 5 |
TX | 77382 | 5 |
TX | 75023 | 5 |
TX | 75025 | 5 |
TX | 75219 | 5 |
TX | 76712 | 5 |
TX | 78613 | 5 |
UT | 84098 | 6 |
VA | 20191 | 9 |
VA | 22201 | 9 |
VA | 22314 | 8 |
VA | 22903 | 7 |
VA | 22043 | 7 |
VA | 20147 | 7 |
VA | 20120 | 6 |
VA | 22304 | 6 |
VA | 22101 | 6 |
VA | 22301 | 5 |
VA | 22902 | 5 |
VA | 23462 | 5 |
WA | 98103 | 9 |
WA | 98225 | 7 |
WA | 98102 | 7 |
WA | 98118 | 7 |
WA | 98122 | 7 |
WA | 98033 | 6 |
WA | 98115 | 6 |
WA | 98045 | 6 |
WA | 98052 | 6 |
WA | 98199 | 6 |
WA | 98074 | 6 |
WA | 98106 | 5 |
WA | 98110 | 5 |
WA | 98072 | 5 |
WI | 53213 | 5 |
Be sure to watch the next video before beginning Exercise 6, the next set of MySQL exercises, and feel free to practice any other queries you wish below!