All right.
Welcome back.
So we're moving on to our next question here.
Even though this one actually isn't framed as a question there's no question mark.
But that doesn't make it any less relevant or less real world.
In fact this is one that I think is a really common operation a common question to ask and it has to
do with inactive users.
So across any sort of startup or online app company there's often you know users who sign up create
an account and then do nothing whether it means never posting or never buying something never converting
from a free account to a paid account like in the case of Spotify or never in my case.
You know this is a real example for my courses.
Students who sign up and then never start it never make it past the first video.
They're at 0 percent and I'm actually able to send targeted.
I think once a month twice a month I can send an email update and I can target that to specific segments
so if I updated an introduction section to a course and I only wanted to let students know who haven't
started yet and want to target them and say hey by the way I notice you haven't started yet.
Well this is a good time to start.
I just revamped the whole intro.
I could do that based off of what we're going to be doing here.
Of course our data in our database is set up differently but it's the same idea.
So this is a really common thing and all we're going to try and do is for our Instagram cloner fake
Instagram we're trying to identify users who have never posted a photo.
So they've signed up and never posted anything and we want our users to be more engaged.
For a bunch of reasons you know we want higher engagement on our app which means that we could you know
have a higher valuation potentially or sell ads for more maybe get higher investments.
All that kind of stuff.
We want people posting if they're signed up it looks bad to have dead accounts people who sign up and
then give up.
So we're going to try and send an e-mail.
We're not worrying about doing that but how are we going to target these people who have never posted
a photo.
First step let's identify them.
And essentially we're going to need to work with a users table and the photos table.
So I'll add my three here which is identify inactive users users with no photos.
OK.
So you've probably gathered we need to do a join.
We're going to be working with the users table so we can start there.
Select start from users and you know we have 100 something users but we can't see who's done what because
we have the photos table.
And if we do our select start from photos just to jog your memory.
Well I guess we haven't actually seen all of our data here yet.
We've got about exactly 257 photos but a lot of them are done by the same user.
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
by the same user so three these are all from the same user.
These are often the same user but then we have some users who haven't posted anything.
But there's no way of knowing that just by looking at the photos because these are the posted photos.
So what we need to do is join these two together and hopefully you know what we need to do.
If I just do a regular inner join.
Inner join Fotos.
And where do we want to do it.
Want to join on users thought Id equals photos dot user ID and if we run this now
it's a mess.
So let's focus on just the things we need.
So rather than star can do username maybe the image you Aro and we can just start with those two.
All right so we can see you know all these user names and what they've submitted.
But the problem here is that this doesn't account for the people who haven't submitted anything.
So if there was a user who didn't submit something they're not showing up here because we're doing an
inner join which remember is the intersection of both circles the left and the right where they overlap.
But what we want is to identify where users on the left have no overlap on the right for photos.
So we can change this to a left join and that one change for a rerun this will have to scroll a bit.
There we go.
You can see we have certain users who have a knoll over here and that's referring to the fact that they
have no photos corresponding to them.
So there's a couple of them as I go here and that's what we want to be able to identify.
So we're almost there actually all that we need to do is only select the users that have photos of no
essentially And of course if I go back to having star here it's not just image you are all that.
No it's everything in photos it's hard to see.
Here's an example.
But we've got a couple of nos here.
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
and then we have it created at it's not.
So all four things are no.
We just need to focus on one of them.
It doesn't matter which one.
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
do immature again and then we want to do is Ad-Aware down here but we can't just say where photos dot.
You know we can just do ID equals all that doesn't work remember and all is weird.
So we need to do.
Where photo start ID is.
And this could be anything on the photo side.
It could be photos that I.D. could be photos that image you or photos that user id any of them.
So I'll just do it as I.D. and if I do this now you'll see.
And we actually don't even need to display that image you know.
But if I do that this is our list of users who have never posted anything for whatever reasons some
of them programmatically created them as bots which all they do is go comment and like other people's
photos they don't post anything themselves.
And then another persona of a user is a lurker which is somebody who has an account and they don't use
it all that much but they just kind of look at people's photos maybe like some thing but they don't
really.
They definitely don't post and they don't comment.
They're kind of a silent member of the community.
So we get a decent number of them.
Sorry for that fire truck and that's all we need to do.
You know if we wanted to figure out how many.
That's another thing we can just add account in.
But we just want that username so that we can target them.
These are people who have never posted anything.
And just to note you know if you did this the other way around it would have worked just fine as well.
Do Fotos users and then right join
does the same thing.
So either way works.
But the key thing is that we're working with know where we have an intersection where some users and
some photos overlap.
Most of them do.
But then we also have that subset of users who have no photos.
And we need to also join those with no.
Which is how we were able to tell if they have no photos.
So here's my solution.
If you came up with something different post it and we can discuss it.
Moving on.