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