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 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.
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%.
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, I want to target them and say, Hey, by the way, I noticed 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 and 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 clone, our
fake Instagram, we're trying to identify users who have never posted a photo, so they've signed up,
they never posted anything.
And we want our users to be more engaged for a bunch of reasons.
We want higher engagement in our app, which means that we could 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 email.
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.
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 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 star 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 these.
These are all posted by the same user.
So three these are all from the same user.
These are all from 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 in or join photos and where do we want to do it?
I want to join on users 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 URL and we can just start with those two.
All right.
So we can see all these usernames 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 if we rerun this.
We'll have to scroll a bit.
There we go.
You can see we have certain users who have a null 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 we need to do is only select the users that have photos of NULL essentially.
And of course, if I go back to having star here, it's not just image URL that's null, it's everything
in photos.
It's hard to see.
Here's an example, but we've got a couple of nulls here.
We've got the image or the photo ID, we've got the image URL, we've got the user ID of the photo and
then we have the created additional.
So all four things are null.
We just need to focus on one of them.
It doesn't matter which one.
So I'll go back to just adding username here and we can do it.
Doesn't matter really.
Let's do image URL again and then all we want to do is add a wear down here.
But we can't just say where photos dot.
We can just do id equals null.
That doesn't work.
Remember null is weird so we need to do where photos id is null.
And this could be anything on the photo side.
Could be photos ID it could be photos that image URL, photos that user ID, any of them.
So I'll just do it as ID and if I do this now, you'll see.
And we actually don't even need to display the image URL.
But if I do that, this is our list of users who have never posted anything for whatever reasons.
Some of them programmatically.
I created them as bots, which all they do is go comment and like other people's photos, but 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 something, 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.
If we want to figure out how many, that's another thing.
We can just add account in, but we just want the username so that we can target them.
These are people who've never posted anything.
And just a note, you know, if you did this the other way around, it would have worked just fine as
well too.
Do photos users and then a right join.
Does the same thing.
So either way it works.
But the key thing is that we're working with NULL, 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 NULL,
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.