Level: Beginner
An aggregation query is often more than a count or summation. In this mini course, you'll tackle a series of exercises that cover built-in aggregation functions, including grouping data and ranking dating with window functions.
The course format is a series of exercises. Each exercise is about 5 minutes. To help you along, each exercise includes a hint and a detailed solution.
Wanted: Architects, Developers, Administrators, and Business Leaders
What you'll need
Operating System:
macOS 10+, Ubuntu 16+, or Windows 10+
Chromium web browser:
Chrome, Opera, or Microsoft Edge
YugabyteDB Managed free cluster
YugabyteDB Community Slack account
Duration 120 minutes
๐ Welcome!
๐ช Get Started with YugabyteDB Managed
๐งช [LAB]: Create a YugabyteDB Managed free cluster
๐ญ [LAB]: Create the ysql_exercises database
โน๐ปโโ๏ธ. Count the number of facilities
โน๏ธโโ๏ธ. Count the number of expensive facilities
๐๏ธ Count the number of recommendations each member makes
โน๐ปโโ๏ธ. List the total slots booked per facility
โน๐ฝ. List the total slots booked per facility in a given month
โน๐ฝโโ๏ธ. List the total slots booked per facility per month
๐๐ฟโโ๏ธ. Find the count of members who have made at least one booking
โน๐ฝโโ๏ธ. List facilities with more than 1000 slots booked
โน๐ฟโโ๏ธ. Find the total revenue of each facility
โน๐ฝ. Find facilities with a total revenue less than 1000
โน๐พโโ๏ธ. Output the facility id that has the highest number of slots booked
โน๐ฟโโ๏ธ. List the total slots booked per facility per month, part 2
โน๏ธ. List the total hours booked per named facility
โน๐ฟ. List each member's first booking after September 1st 2012
๐๐ผโโ๏ธ. Produce a list of member names, with each row containing the total member count
๐๐พโโ๏ธ. Produce a numbered list of members
โน๐ผโโ๏ธ. Output the facility id that has the highest number of slots booked, again
๐๏ธ. Rank members by (rounded) hours used
โน๐พโโ๏ธ. Find the top three revenue generating facilities
โน๐ป. Classify facilities by value
โน๐ฝโโ๏ธ. Calculate the payback time for each facility
๐ช. Calculate a rolling average of total revenue
๐ Additional reading
๐งฐ Your YugabyteDB skill on LinkedIn
๐ Learning Path Tracker
Practitioner:
YSQL Exercises - Aggregation queries
Yugabyte University courses are FREE, so enroll today!
Yes, you do!
This is a self-paced course! You can start, stop, pause, resume, and replay whenever you feel like it.
Using the Course Player, for example, you can watch learning videos whenever you want.
This course doesn't have any on-demand labs for hands-on exercises, but for our courses that do have labs, they are available when you want as well!
For this course, do I need to purchases a YugabyteDB software license?
You do not need to purchase a YugabyteDB software license for this course. YugabyteDB is open source software.
You can sign up for YugabyteDB Managed free cluster without a credit card or payment method.
Do I need to sign up for a YugabyteDB Managed free cluster?
What an interesting idea!
Most of our courses require a YugabyteDB cluster, so an on-demand YugabyteDB Managed free cluster is a great option.
If a course requires a YugabyteDB cluster, the course will provide two options:
(1) a lab provides instructions for how to sign up for a YugabyteDB Managed free cluster, or...
(2) an on-demand, lab environment that includes a YugabyteDB cluster.
A big THANK YOU from the team at Yugabyte University!
If you complete all of the courses in the learning path, you will automatically receive proof of completion and join a special group of students.
If you get stuck, help is a literally a click away. In the Course Player header bar, select Discussions and post a question, concern, or even suggestion.
We also have a #training channel on YugabyteDB Community Slack.
In the Course Player, you always find instructions on how to get help in the Help lesson.
After completing this course, you will have the opportunity to review it!
We highly value your feedback and suggestions. Your review and rating helps pay it forward for our future students.
Yes, you can. Simply enroll and start right away. It's free and fun! For this learning path, there are seven courses with a suggested order. You can enroll in any course you want.
YSQL Exercises: Learn by doing!
