Let's begin working through some examples. Now we have the beginnings of a blog post system, something really easy to understand. Now we have Users, and Users can write blog posts, and other Users can read those blog posts. So now imagine your boss says, I'd like to give a bonus to the 10 most popular writers on the website. Can you tell me who those people are? And you think about it for a minute, and you say, sure, boss, I'll take care of that. So we'll switch to the Query tab, and we know we want to fetch all of the Users. But for each of those Users, we also want to, presumably, pull in the PostReads table.

So we'll switch to the Query tab, and we know we want to fetch all of the users. But for each of those users, we also want to, presumably, pull in the PostReads table. All right, so you think, well, I want all users no matter what. So we'll do a left join here, and we'll pull in the PostReads on the condition that the, let's take a look, the connecting tissue is the user_id on this table matches up with the id here. At least that's what you think. So you say user_id equals the id on the Users table, and you give that a run. However, if I scroll down here, you're going to notice many records are blank. So what does that mean?

However, if I scroll down here, you're going to notice many records are blank. So what does that mean? Presumably, it means that in this case, Sven has not read a single post. And we can verify that. If we go to PostReads, let's look for one where the userId is 1, and we don't get anything. So that means Sven has not read a post. But that's not quite right, is it? Your boss didn't say, give me the users who have read the most posts. Here she said, give me the users who have written posts that have been read the most. It's a different thing there.

Here she said, give me the users who have written posts that have been read the most. It's a different thing there. So you always want to be careful when you write your query. Just because you get information in response to that query doesn't mean it's correct. So just be a little careful with that. In our case, I think we need to rewrite this. I want to get all users, and then I want to join the PostReads table on the condition that, what? Well, on the condition that the post ID that was read happens to be a post written by the current user.

Well, on the condition that the post_id that was read happens to be a Post written by the current User. So in these cases, we can use a subquery on the condition that the post_id is in a list of posts that were written by the current User. So we get id from the posts table where the writer_id matches up with the User in the current iteration, basically, like that. So now, if we give it a run, our information has changed, and this is more what we want. But we're still not quite there yet. So notice we all of a sudden have a bunch of duplication, because what I can see here is that Sven has written posts that were read, what is that, nine times by nine different

So notice we all of a sudden have a bunch of duplication, because what I can see here is that Sven has written posts that were read, what is that, nine times by nine different users. So I don't want this duplication, right? So we need to group them. And what are we going to group by? Well, we're going to group by the users.id. So now, if I give it another run, we're doing a select * , and then we're grouping, but MySQL doesn't know how to deal with these non-aggregated columns. So I'm going to be a little more specific here.

MySQL doesn't know how to deal with these non-aggregated columns. So I'm going to be a little more specific here. Yes, I want the userId. So if I give that a run, there's no more error. But next, I want the user's name. All right. Next, I want the total number of records for that user, and in this case, that will be the total number of post reads. So if we give that a run, now I can see Sven has written posts that were read nine times. But I don't like that function signature, so we'll call it postRead, or timesPost,

So if we give that a run, now I can see Sven has written posts that were read nine times. But I don't like that function signature, so we'll call it postRead, or timesPost, or whatever. And we get something like this. But now if I scroll down, you'll see these are not in any order. So let's order according to this column here. There we go. So just so it turns out that Sven, our example in this video, also happens to be the person who has written the most popular posts. So what we might say is limit 10, and now we have our list of people who should receive

who has written the most popular Posts. So what we might say is limit 10, and now we have our list of people who should receive a bonus.