1 00:00:00,150 --> 00:00:00,690 All right. 2 00:00:00,690 --> 00:00:01,770 Next up here. 3 00:00:01,800 --> 00:00:03,390 This one will be nice and short. 4 00:00:03,420 --> 00:00:07,410 All we're trying to figure out is how many times does the average user post. 5 00:00:07,440 --> 00:00:13,530 So we have a board or our investors want to know basically what our engagement is like just in general. 6 00:00:13,560 --> 00:00:15,690 For every user, any user. 7 00:00:16,290 --> 00:00:17,990 How many times can we assume they've posted? 8 00:00:18,000 --> 00:00:19,920 So this is just across the board. 9 00:00:19,920 --> 00:00:26,040 And really all we need to do is take the total number of post's photos and divide it by the total number 10 00:00:26,040 --> 00:00:26,790 of users. 11 00:00:27,000 --> 00:00:34,470 And you could do that separately in different lines, or you could do a big joint statement between 12 00:00:34,470 --> 00:00:38,400 users and photos and then use average and calculate average. 13 00:00:38,400 --> 00:00:41,940 But you could just do it using sub queries, which is what I'm going to do. 14 00:00:42,420 --> 00:00:43,890 So something a little different. 15 00:00:43,890 --> 00:00:45,510 So we'll just do calculate. 16 00:00:46,760 --> 00:00:50,420 Average number of photos per user. 17 00:00:51,400 --> 00:00:58,450 So what we're looking for is total number of photos divided by total number of users. 18 00:00:59,470 --> 00:01:03,100 And the first part, will each of those parts individually we know how to do. 19 00:01:03,760 --> 00:01:11,890 We can just do a select count star from photos and that will give us the total number of photos. 20 00:01:12,370 --> 00:01:20,350 If I add a semicolon, 257 and then four users, I just need to change that to be users. 21 00:01:20,350 --> 00:01:22,930 Select count from users and we get 100. 22 00:01:23,290 --> 00:01:27,730 Now if you're good at mental math, even if you're not, it's easy to figure out our average in your 23 00:01:27,730 --> 00:01:28,000 head. 24 00:01:28,000 --> 00:01:34,720 But of course we're trying to do this dynamically so that if we have x 10,000 more users tomorrow, 25 00:01:34,750 --> 00:01:35,860 this still applies. 26 00:01:36,340 --> 00:01:40,990 So using sub queries, basically what I can do is divide these two. 27 00:01:41,830 --> 00:01:43,960 So I can't just do this. 28 00:01:43,990 --> 00:01:45,370 I'll get a syntax error. 29 00:01:46,450 --> 00:01:52,630 But we need to do like we've seen before with sub queries is add parentheses around these two things. 30 00:01:54,870 --> 00:01:56,520 But even that won't be enough. 31 00:01:56,970 --> 00:01:58,080 What are we missing? 32 00:01:58,290 --> 00:02:03,930 Well, it's just like we can't just say five plus five, or we can't just do one divided by two. 33 00:02:04,320 --> 00:02:09,389 We have to say select one divided by two in order to get anything. 34 00:02:09,389 --> 00:02:11,520 So we need an extra select up here. 35 00:02:12,210 --> 00:02:13,710 Yeah, it's kind of a mess. 36 00:02:14,050 --> 00:02:16,050 You know, we could do this on separate lines, maybe. 37 00:02:16,050 --> 00:02:21,450 I'm not actually sure how to best format this because it's kind of a weird query, but maybe something 38 00:02:21,450 --> 00:02:23,250 like this. 39 00:02:25,070 --> 00:02:29,510 Well, we can leave it at that for now and I'll get rid of or I'll comment that out. 40 00:02:29,810 --> 00:02:31,340 Now, if we run this. 41 00:02:33,600 --> 00:02:38,040 Well, we get this massive line here, but 2.57. 42 00:02:38,040 --> 00:02:40,110 So every user has 2.5. 43 00:02:40,320 --> 00:02:42,900 On average, every user has 2.57 posts. 44 00:02:42,900 --> 00:02:46,290 That's thrown off significantly for a couple of reasons. 45 00:02:46,290 --> 00:02:47,190 We'll see later. 46 00:02:48,210 --> 00:02:52,020 There are bots and users who just don't post anything. 47 00:02:52,020 --> 00:02:54,900 And if we tried to get rid of these bots, our average would change. 48 00:02:54,900 --> 00:02:56,130 So we'll get there in a moment. 49 00:02:56,820 --> 00:03:04,110 What we could do is just add in simple alias, we'll just call it as average avg and there we go. 50 00:03:04,110 --> 00:03:05,520 So that's the answer to that one. 51 00:03:05,520 --> 00:03:11,070 If you came up with another way, feel free to share it and we'll talk about it in the comments. 52 00:03:11,880 --> 00:03:12,300 All right. 53 00:03:12,300 --> 00:03:12,930 Moving on.