Back to questions
Page With No Likes [Facebook SQL Interview Question]
Assume you're given the tables below about Facebook Page and Page likes (as in "Like a Facebook Page").
Write a query to return the IDs of the Facebook pages which do not possess any likes. The output should be sorted in ascending order.
Table:
| Column Name | Type |
|---|---|
| page_id | integer |
| page_name | varchar |
Example Input:
| page_id | page_name |
|---|---|
| 20001 | SQL Solutions |
| 20045 | Brain Exercises |
| 20701 | Tips for Data Analysts |
Table:
| Column Name | Type |
|---|---|
| user_id | integer |
| page_id | integer |
| liked_date | datetime |
Example Input:
| user_id | page_id | liked_date |
|---|---|---|
| 111 | 20001 | 04/08/2022 00:00:00 |
| 121 | 20045 | 03/12/2022 00:00:00 |
| 156 | 20001 | 07/25/2022 00:00:00 |
Example Output
| page_id |
|---|
| 20701 |
The dataset you are querying against may have different input & output - this is just an example!
Solution
There are two ways to go about it. Either or can be established between tables and or a subquery can be used to identify which pages have not been liked by any user.
The clause starts selecting data from the left table. For each row in the left table (), it compares the value in the column with the value of each row in the column in the right table ().
When page_id are found on both sides, the clause creates a new row that contains columns that appear in the clause and adds this row to the result set.
In case from table is not available in table, the clause also creates a new row that contains columns that appear in the clause. In addition, it fills the columns that come from the (right table) with NULL. Rows having NULL values in the result is the set of the solution.
Read about [1] and [2] to get the better understanding.
Solution #1: Using LEFT OUTER JOIN
Another solution to this problem, since pages with NO LIKES are needed, would be the clause (refer to Solution #2). It's an appropriate and efficient operator to get this information. Check out here.
Both methods give the same output.
Solution #2: Using
Solution #3: Using
Solution #4: Using
PostgreSQL 14