Practice SQL Interview Question #2

 

Given the 2 tables below, User and UserHistory:

User
user_id
name
phone_num
UserHistory
user_id
date
action

Let’s continue with the 2nd question, presented again below…

2. Given the tables above, write a SQL query to determine which user_ids in the User table are not contained in the UserHistory table (assume the UserHistory table has a subset of the user_ids in User table). Do not use the SQL MINUS statement. Note: the UserHistory table can have multiple entries for each user_id.

Note that your SQL should be compatible with MySQL 5.0, and avoid using subqueries.

Basically we want the user_ids that exist in the User table but not in the UserHistory table. If we do a regular inner join on the user_id column, then that would just do a join on all the rows in which the User and UserHistory table share the same user_id values . But the question specifically asks for just the user_ids that are in the User table, but are not in the UserHistory table. So, using an inner join will not work.

What if, instead of an inner join, we use a left outer join on the user_id column? This will allow us to retain all the user_id values from the User table (which will be our "left" table) even when there is no matching user_id entry in the "right" table (in this case, the UserHistory table). When there is no matching record in the "right" table the entry will just show up as NULL. This means that any NULL entries are user_id values that exist in the User table but not in the UserHistory table. This is exactly what we need to answer the question. So, here’s what the SQL will look like: 

select distinct u.user_id
from User as u
left join UserHistory as uh on u.user_id=uh.user_id
where uh.user_id is null

You may be confused by the "User as u" and the "UserHistory as uh" syntax. Those are what’s called aliases. Aliases allow us to assign a shorter name to a table, and it makes for cleaner and more compact SQL. In the example above, "u" will actually be another name for the "User" table and "uh" will be another name for the "UserHistory" table.

We also use the distinct keyword. This will ensure that each user_id is returned only once.

That concludes our series of practice sql interview questions. If you are looking for some more advanced and challenging SQL interview questions the check out our other articles: Advanced SQL practice questions.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.