SQL - Leetcode-01

SQL Schema

For Reference: Create Products Table

Create table If Not Exists Products (product_id int, low_fats ENUM('Y', 'N'), recyclable ENUM('Y','N'))
Truncate table Products
insert into Products (product_id, low_fats, recyclable) values ('0', 'Y', 'N')
insert into Products (product_id, low_fats, recyclable) values ('1', 'Y', 'Y')
insert into Products (product_id, low_fats, recyclable) values ('2', 'N', 'Y')
insert into Products (product_id, low_fats, recyclable) values ('3', 'Y', 'Y')
insert into Products (product_id, low_fats, recyclable) values ('4', 'N', 'N')

Question 1

Write a query to find the ids of products that are both low fat and recyclable.

SELECT product_id FROM Products WHERE low_fats = 'Y' AND recyclable = 'Y';

Question 2

Write a query to get the percentage of certain products:

SELECT ROUND(AVG(CASE WHEN low_fats = 'Y' OR recyclable = 'Y' THEN 1 ELSE 0 END),2) AS PERCENTAGE

SQL Schema

For Reference: Create Customer Table

Create table If Not Exists Customer (id int, name varchar(25), referee_id int)
Truncate table Customer
insert into Customer (id, name, referee_id) values ('1', 'Will', 'None')
insert into Customer (id, name, referee_id) values ('2', 'Jane', 'None')
insert into Customer (id, name, referee_id) values ('3', 'Alex', '2')
insert into Customer (id, name, referee_id) values ('4', 'Bill', 'None')
insert into Customer (id, name, referee_id) values ('5', 'Zack', '1')
insert into Customer (id, name, referee_id) values ('6', 'Mark', '2')

Question 3

Write a query to report the names of the customer that are not referred by the customer with id = 2. The trick here is to handle the NULL cases.

Simple Method:

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

Using COALESCE

COALESCE takes in multiple arguments and returns the first non-null value in the list then check if it is equal to 2

Faster method:

SELECT name FROM customer WHERE COALESCE(referee_id,0) <> 2;

Using ISNULL

Faster method:

SELECT name FROM customer WHERE IFNULL(referee_id,0) <> 2

SQL Schema

For Reference: Create Customer & Orders Tables

Create table If Not Exists Customers (id int, name varchar(255))
Create table If Not Exists Orders (id int, customerId int)
Truncate table Customers
insert into Customers (id, name) values ('1', 'Joe')
insert into Customers (id, name) values ('2', 'Henry')
insert into Customers (id, name) values ('3', 'Sam')
insert into Customers (id, name) values ('4', 'Max')
Truncate table Orders
insert into Orders (id, customerId) values ('1', '3')
insert into Orders (id, customerId) values ('2', '1')

Question 4

Write a query to report all customers who never order anything. Return result in any order.

Basically we need to perform a LEFT JOIN and then find out those with Orders id that IS NULL.

For Illustration: Showing

SELECT c.name as Customers, o.id as OrdersId FROM
Customers c LEFT JOIN Orders o ON c.id = o.customerId;

Output showing all customer with their orderId:

Output:

| Customers | OrdersId |
| --------- | -------- |
| Joe       | 2        |
| Henry     | null     |
| Sam       | 1        |
| Max       | null     |

So we basically need to adjust the query to select only those with null.

Final Solution:

SELECT c.name as Customers FROM
Customers c LEFT JOIN Orders o ON c.id = o.customerId
WHERE o.id IS NULL;

Output showing all customer with NULL orderId:

Output:

| Customers |
| --------- |
| Henry     |
| Max       |