WEBVTT 00:00.090 --> 00:00.690 All right. 00:00.690 --> 00:01.490 Welcome back. 00:01.650 --> 00:03.650 So we're moving on to our next question here. 00:03.840 --> 00:07.810 Even though this one actually isn't framed as a question there's no question mark. 00:07.950 --> 00:11.450 But that doesn't make it any less relevant or less real world. 00:11.460 --> 00:17.010 In fact this is one that I think is a really common operation a common question to ask and it has to 00:17.010 --> 00:19.320 do with inactive users. 00:19.320 --> 00:26.880 So across any sort of startup or online app company there's often you know users who sign up create 00:26.880 --> 00:32.490 an account and then do nothing whether it means never posting or never buying something never converting 00:32.490 --> 00:38.850 from a free account to a paid account like in the case of Spotify or never in my case. 00:38.890 --> 00:41.530 You know this is a real example for my courses. 00:41.610 --> 00:45.810 Students who sign up and then never start it never make it past the first video. 00:45.930 --> 00:50.910 They're at 0 percent and I'm actually able to send targeted. 00:50.910 --> 00:56.580 I think once a month twice a month I can send an email update and I can target that to specific segments 00:56.580 --> 01:04.380 so if I updated an introduction section to a course and I only wanted to let students know who haven't 01:04.380 --> 01:08.940 started yet and want to target them and say hey by the way I notice you haven't started yet. 01:09.060 --> 01:10.200 Well this is a good time to start. 01:10.200 --> 01:12.280 I just revamped the whole intro. 01:12.360 --> 01:15.700 I could do that based off of what we're going to be doing here. 01:15.780 --> 01:19.780 Of course our data in our database is set up differently but it's the same idea. 01:19.950 --> 01:25.770 So this is a really common thing and all we're going to try and do is for our Instagram cloner fake 01:25.770 --> 01:29.770 Instagram we're trying to identify users who have never posted a photo. 01:29.910 --> 01:35.280 So they've signed up and never posted anything and we want our users to be more engaged. 01:35.280 --> 01:39.600 For a bunch of reasons you know we want higher engagement on our app which means that we could you know 01:39.600 --> 01:44.130 have a higher valuation potentially or sell ads for more maybe get higher investments. 01:44.220 --> 01:45.200 All that kind of stuff. 01:45.420 --> 01:49.770 We want people posting if they're signed up it looks bad to have dead accounts people who sign up and 01:49.770 --> 01:50.880 then give up. 01:50.970 --> 01:52.640 So we're going to try and send an e-mail. 01:52.930 --> 01:57.300 We're not worrying about doing that but how are we going to target these people who have never posted 01:57.300 --> 01:58.010 a photo. 01:58.050 --> 01:59.890 First step let's identify them. 02:00.390 --> 02:05.580 And essentially we're going to need to work with a users table and the photos table. 02:06.090 --> 02:16.810 So I'll add my three here which is identify inactive users users with no photos. 02:17.670 --> 02:18.340 OK. 02:18.750 --> 02:21.070 So you've probably gathered we need to do a join. 02:21.210 --> 02:25.050 We're going to be working with the users table so we can start there. 02:25.050 --> 02:33.150 Select start from users and you know we have 100 something users but we can't see who's done what because 02:33.150 --> 02:34.480 we have the photos table. 02:34.770 --> 02:38.070 And if we do our select start from photos just to jog your memory. 02:38.310 --> 02:40.760 Well I guess we haven't actually seen all of our data here yet. 02:40.770 --> 02:46.710 We've got about exactly 257 photos but a lot of them are done by the same user. 02:46.710 --> 02:54.960 So this here is user ID if I scroll way up here we go we can see you know these the these are all posted 02:54.960 --> 02:58.470 by the same user so three these are all from the same user. 02:58.500 --> 03:03.170 These are often the same user but then we have some users who haven't posted anything. 03:03.660 --> 03:08.880 But there's no way of knowing that just by looking at the photos because these are the posted photos. 03:09.030 --> 03:14.520 So what we need to do is join these two together and hopefully you know what we need to do. 03:14.640 --> 03:18.060 If I just do a regular inner join. 03:19.530 --> 03:21.660 Inner join Fotos. 03:21.780 --> 03:22.960 And where do we want to do it. 03:22.970 --> 03:31.950 Want to join on users thought Id equals photos dot user ID and if we run this now 03:35.790 --> 03:36.660 it's a mess. 03:36.660 --> 03:39.010 So let's focus on just the things we need. 03:39.030 --> 03:46.280 So rather than star can do username maybe the image you Aro and we can just start with those two. 03:47.160 --> 03:51.000 All right so we can see you know all these user names and what they've submitted. 03:51.180 --> 03:55.480 But the problem here is that this doesn't account for the people who haven't submitted anything. 03:55.500 --> 04:00.420 So if there was a user who didn't submit something they're not showing up here because we're doing an 04:00.420 --> 04:06.860 inner join which remember is the intersection of both circles the left and the right where they overlap. 04:06.900 --> 04:14.150 But what we want is to identify where users on the left have no overlap on the right for photos. 04:14.250 --> 04:22.790 So we can change this to a left join and that one change for a rerun this will have to scroll a bit. 04:22.790 --> 04:23.740 There we go. 04:23.800 --> 04:29.920 You can see we have certain users who have a knoll over here and that's referring to the fact that they 04:29.920 --> 04:32.230 have no photos corresponding to them. 04:32.380 --> 04:37.460 So there's a couple of them as I go here and that's what we want to be able to identify. 04:37.690 --> 04:45.390 So we're almost there actually all that we need to do is only select the users that have photos of no 04:45.490 --> 04:50.310 essentially And of course if I go back to having star here it's not just image you are all that. 04:50.320 --> 04:53.770 No it's everything in photos it's hard to see. 04:53.860 --> 04:54.920 Here's an example. 04:55.280 --> 04:57.310 But we've got a couple of nos here. 04:57.310 --> 05:04.130 We've got the image or the photo I.D. we've got the image you know you've got the user idea for a photo 05:04.420 --> 05:06.630 and then we have it created at it's not. 05:06.910 --> 05:08.490 So all four things are no. 05:08.740 --> 05:10.230 We just need to focus on one of them. 05:10.260 --> 05:11.410 It doesn't matter which one. 05:11.440 --> 05:18.970 So I'll go back to that just adding a user name here and we can do that it doesn't matter really let's 05:18.970 --> 05:27.620 do immature again and then we want to do is Ad-Aware down here but we can't just say where photos dot. 05:27.790 --> 05:32.190 You know we can just do ID equals all that doesn't work remember and all is weird. 05:32.230 --> 05:33.120 So we need to do. 05:33.130 --> 05:35.930 Where photo start ID is. 05:36.760 --> 05:38.850 And this could be anything on the photo side. 05:38.850 --> 05:44.670 It could be photos that I.D. could be photos that image you or photos that user id any of them. 05:44.890 --> 05:51.430 So I'll just do it as I.D. and if I do this now you'll see. 05:51.430 --> 05:54.230 And we actually don't even need to display that image you know. 05:54.790 --> 06:01.810 But if I do that this is our list of users who have never posted anything for whatever reasons some 06:01.810 --> 06:09.130 of them programmatically created them as bots which all they do is go comment and like other people's 06:09.130 --> 06:11.280 photos they don't post anything themselves. 06:11.380 --> 06:18.010 And then another persona of a user is a lurker which is somebody who has an account and they don't use 06:18.010 --> 06:23.170 it all that much but they just kind of look at people's photos maybe like some thing but they don't 06:23.170 --> 06:23.610 really. 06:23.650 --> 06:25.640 They definitely don't post and they don't comment. 06:25.660 --> 06:28.870 They're kind of a silent member of the community. 06:29.110 --> 06:30.910 So we get a decent number of them. 06:31.270 --> 06:34.080 Sorry for that fire truck and that's all we need to do. 06:34.120 --> 06:35.440 You know if we wanted to figure out how many. 06:35.440 --> 06:37.750 That's another thing we can just add account in. 06:37.750 --> 06:40.870 But we just want that username so that we can target them. 06:40.870 --> 06:42.650 These are people who have never posted anything. 06:42.940 --> 06:48.420 And just to note you know if you did this the other way around it would have worked just fine as well. 06:48.440 --> 06:52.420 Do Fotos users and then right join 06:55.230 --> 06:56.270 does the same thing. 06:56.610 --> 06:58.670 So either way works. 06:58.740 --> 07:06.030 But the key thing is that we're working with know where we have an intersection where some users and 07:06.030 --> 07:07.020 some photos overlap. 07:07.020 --> 07:08.100 Most of them do. 07:08.430 --> 07:11.230 But then we also have that subset of users who have no photos. 07:11.430 --> 07:14.210 And we need to also join those with no. 07:14.430 --> 07:16.940 Which is how we were able to tell if they have no photos. 07:17.100 --> 07:18.200 So here's my solution. 07:18.240 --> 07:21.340 If you came up with something different post it and we can discuss it. 07:21.360 --> 07:21.810 Moving on.