WEBVTT 00:00.150 --> 00:00.740 All right. 00:00.750 --> 00:01.850 Next up here. 00:01.950 --> 00:03.510 This one will be nice and short. 00:03.510 --> 00:07.550 Always trying to figure out is how many times the average user post. 00:07.650 --> 00:13.320 So we have a board or our investors want to know basically what our engagement is like just in general 00:13.600 --> 00:18.110 for every user any user what how many times can we assume they've posted. 00:18.120 --> 00:24.540 So this is just across the board and really all we need to do is take the total number of posts photos 00:24.780 --> 00:26.790 and divide it by the total number of users. 00:27.180 --> 00:34.320 And you could do that separately different lines or you could you know do a big joint statement between 00:34.500 --> 00:40.740 users and photos and then use average and calculate average but you could just do it using subcommittee's 00:40.800 --> 00:42.490 which is what I'm going to do. 00:42.510 --> 00:43.950 So something a little different. 00:43.980 --> 00:50.870 So we'll just to calculate average number of photos per user. 00:51.540 --> 00:58.810 So what we're looking for is total number of photos divided by total number of users. 00:59.580 --> 01:04.890 And the first part will each of those parts individually if we know how to do we can just do a select 01:04.890 --> 01:12.350 count star from photos and that will give us the total number of photos. 01:12.420 --> 01:15.860 If I had a semi-colon 257. 01:16.140 --> 01:22.710 And then for users who just need to change that to be users like to count from users and we get 100 01:23.430 --> 01:24.670 now forget it mental math. 01:24.750 --> 01:27.990 Even if you're not it's easy to figure out are average in your head. 01:28.020 --> 01:34.080 But of course we're trying to do this dynamically so that if we have X you know ten thousand more users 01:34.080 --> 01:36.080 tomorrow this still applies. 01:36.480 --> 01:41.490 So using stub queries basically what I can do is divide these two. 01:41.940 --> 01:45.880 So I can't just do this I'll get a syntax error. 01:46.530 --> 01:47.370 What we need to do. 01:47.370 --> 01:52.780 Like we've seen before was said purpose is add parentheses around these two things. 01:54.410 --> 01:57.090 OK but even that won't be enough. 01:57.090 --> 01:58.090 What are we missing. 01:58.410 --> 02:01.480 Well it's just like we can't just say five plus five. 02:01.830 --> 02:04.060 We can't just do one divided by two. 02:04.500 --> 02:09.500 We have to say select one to about a right to in order to get anything. 02:09.510 --> 02:11.890 So we need an extra Select up here. 02:12.350 --> 02:13.740 Yeah it's kind of a mess. 02:14.020 --> 02:18.200 You know we could do this on separate lines maybe I'm not actually sure how to best format this because 02:18.200 --> 02:23.410 it's kind of a weird query but maybe something like this. 02:25.080 --> 02:26.920 Well we can leave it at that for now. 02:27.440 --> 02:29.880 Now get rid of all comment that out. 02:29.940 --> 02:36.310 Now if we run this will we get this massive line here. 02:36.540 --> 02:44.670 But 2.5 7 so every user has 2.5 on average every user has 2.5 7 posts that's thrown off significantly 02:44.910 --> 02:48.250 for a couple of reasons we'll see later. 02:48.300 --> 02:52.080 There are lots of users who just don't post anything. 02:52.200 --> 02:54.920 And if we tried to get rid of the spot our efforts would change. 02:54.960 --> 02:56.720 So we'll get there in a moment. 02:56.820 --> 03:02.250 What we could do is just add in a simple alias we'll just call it as average KBG. 03:02.890 --> 03:03.310 OK. 03:03.360 --> 03:04.050 And there we go. 03:04.200 --> 03:05.600 So that's the answer to that one. 03:05.640 --> 03:11.910 If you came up with another way feel free to share it and we'll talk about it in the comments. 03:11.910 --> 03:12.270 All right. 03:12.300 --> 03:12.660 Moving on.