Questions

1. JOINs JOINs JOINs

Write a query for student names whose friend got offered a higher salary than them.

Names must be ordered by salary amount offered to the friend.

It is guaranteed that no two students have the same salary.

Table: Students

Column Name	Type
id (PK)	int
name	string

Table: Friends

Column Name	Type
id (FK)	int
friend_id (FK)	int

Table: Packages

Column Name	Type
id (FK)	int
salary	int

SELECT
	s.name
FROM
	Students s
JOIN
	Packages p USING (id)
JOIN
	Friends f USING (id)
JOIN
	Packages p2 ON f.friend_id = p2.id
WHERE
	p.salary < p2.salary
ORDER BY
	p2.salary

"""
Visualize your JOINs
s.id | s.name | p.salary | f.friend_id | p2.salary
"""

2. GROUP BYs

Source: Leetcode | Sales Analysis III

Write an SQL query that reports the product names that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.

Return the result table in any order.

Table: Sales

Column Name	Type
seller_id	int
product_id (FK)	int
buyer_id	int
sale_date	date
quantity	int
price	int

Table: Product

Column Name	Type
product_id (PK)	int
product_name	varchar
unit_price	int

SELECT
	product_id,
	product_name
FROM
	Product
JOIN
	Sales USING (product_id)
GROUP BY
	product_id, product_name
HAVING
	MIN(sale_date) >= DATE('2019-01-01')
	AND MAX(sale_date) <= DATE('2019-03-31')
;

"""
Isolate a single key?
1. What happens if there's only one value?
2. product_ids {1, 2, 3}, pick 2 only
	
	2 has sale_dates {2019-02-17, 2019-06-02, 2019-03-01}

3. Deduce that MIN = 2019-02-17 and MAX = 2019-06-02
"""