Modules

When you're analyzing data, especially for online shopping like on eBay, you often need to figure out things like who are the biggest spenders. This is where SQL's GROUP BY and ORDER BY come in super handy, especially when you want to rank your results.

Let's take an example. Imagine you have a table orders that tracks all the purchases on eBay. This table has columns like buyer_id, order_id, and order_amount. Here is a sample:

You're being asked to get the top 3 spenders on Ebay. Here are the steps you would need to take:

• Step 1: First, you group the data by buyer_id, to sum up how much each user spent. This gives you the total spend per user.

SELECT buyer_id,
       SUM(order_amount) AS total_spent
FROM orders
GROUP BY 1

• Step 2: Next, you want to find out who spent the most. You do this by ordering the results of your grouped data in descending order based on the total spend.

SELECT buyer_id,
       SUM(order_amount) AS total_spent
FROM orders
GROUP BY 1
ORDER BY 2 DESC

• Step 3: You use LIMIT to show only the top 3 results.

SELECT buyer_id,
       SUM(order_amount) AS total_spent
FROM orders
GROUP BY 1
ORDER BY 2 DESC
LIMIT 3

And that's it, your results will show you the top 3 spenders!

Let me share with you a quick trick, if you only want to show the buyer_id without their total_spent, you can do this by putting the aggregate sum instead on your ORDER BY clause. Just like this:

SELECT buyer_id
FROM orders
GROUP BY 1
ORDER BY SUM(order_amount) DESC
LIMIT 3

Tada! You are now able to use 3 things at the same time: GROUP BY, ORDER BY, and LIMIT. 🚀

🚨Always remember the rule: first GROUP BY, then ORDER BY, and finally LIMIT. This logical order makes perfect sense - you start by grouping your data, then you sort them, and if necessary, limit the results to a specific number.

Let's now practice