584. Find Customer Referee

Problem Statement

Table: Customers

Column Name	Type
id	int
name	varchar
referee_id	int

In SQL, id is the primary key column for this table.

Each row of this table indicates the id of a customer, their name, and the id (referee_id) of the customer who referred them.

Instructions

• Find the names of customer that are not referred by any customer with id = 2.
• Return the result table in any order.
• The result format is in the following example.

Example

Input: Customers table

id	name	referee_id
1	Will	null
2	Jane	null
3	Alex	2
4	Bill	null
5	Zack	1
6	Mark	2

Output:

name
Will
Jane
Bill
Zack

Submissions

PostgreSQL

SELECT name
FROM Customer
WHERE referee_id != 2 OR referee_id IS NULL;

Pandas

import pandas as pd

def find_customer_referee(customer: pd.DataFrame) -pd.DataFrame:
    return customer[(customer['referee_id'] != 2) | (customer['referee_id'].isnull())][['name']]

Explanations

PostgreSQL

Submitted by @noeyislearning

• SELECT name: This is the SELECT statement, which specifies the data you want to retrieve from the database. In this case, you're selecting the name column, so the query will return the names of customers.
• FROM Customer: This is the FROM clause, which specifies the table you want to retrieve data from. In this case, you're retrieving data from the Customer table.
• WHERE referee_id != 2 OR referee_id IS NULL: This is the WHERE clause, which specifies the conditions that the rows must meet to be included in the result set. In this case, the conditions are that referee_id must not be equal to 2 (referee_id != 2) or referee_id must be null (referee_id IS NULL).
  • referee_id != 2: This condition is true for rows where referee_id is not equal to 2.
  • referee_id IS NULL: This condition is true for rows where referee_id is null. In SQL, null is a special marker used to indicate that a data value does not exist in the database.
  • OR: This is a logical operator that combines the two conditions. A row will be included in the result set if either condition is true.

Pandas

Submitted by @noeyislearning