Hey there SQL-ites! Wow, it’s been awhile since I’ve last posted. Work has been crazy busy again, and just life in general. Sure feels good to be back, learning again! I’m daringly dropping straight back into my learnings from where we left off…in April!? Crazy.
Anyway, let’s get back to brass tacks. Before life and work got really crazy for me, we covered:
- The GROUP BY statement
- A beginner SQL skills challenge
- Using the LIKE statement for pattern matching
- Using the IN SQL statement and BETWEEN statement for better data manipulation
Another quick recap note, we’ve been using the famous DVD rental training database for our work. On to the good stuff.
GROUP BY SQL Skills Challenge #1
Let’s say it’s time for quarterly reviews, who doesn’t love evaluations? ¯_(ツ)_/¯ Implement your knowledge of GROUP BY against the following problem:
We need to figure out how much revenue our employees have realized from DVD rentals, and how many transactions each handled.
GROUP BY SQL Skills Answer #1
Let’s talk through the problem and dissect it before presenting code. “A problem well-stated is a problem-half solved” – paraphrase of some smart person.
- We’re talking about revenue, so we’ll need to be dealing with the payment table.
- We’re evaluating employees (staff), SUM of revenue, and COUNT of transactions.
- If we’re aggregating this data, we’re GROUPing BY employee.
- We’re also ORDERing the employees BY who handled the most transactions.
That said, here’s the code:
SELECT staff_id, SUM(amount), COUNT(amount)
GROUP BY staff_id
ORDER BY COUNT(amount) DESC;
…with our results!
GROUP BY SQL Skills Challenge #2
Well done on your challenge! Here’s the second:
It’s time to do inventory, flashbacks of retail and restaurant wonder for all of us. 🙂
In the name of forecasting and planning, we need to figure out the average replacement cost of our movies, by rating.
GROUP BY SQL Skills Answer #2
Ok, let’s walk through our problem.
- We need to use the film database here, since we’re gathering information on the movies.
- We’re GROUPing our films BY rating
- We’re using an aggregate function to determine the average replacement cost of each rating.
Drum roll, here’s a winning code snippet:
SELECT rating, AVG(replacement_cost)
GROUP BY rating
ORDER BY AVG(replacement_cost) DESC;
With the output:
Are there more challenges we should be covering? Yes. However, I’m trying to do better about getting more sleep these days. Unlike past SQL articles, it’s still (barely) before midnight. So we’ll take a quick breather, possibly update this article, but definitely keep moving forward. Cheers!