LC 175 & LC 197 & LC 607 & LC 1148 & LC 1581

The following intro-level SQL problems are about ‘Join (JOIN or WHERE)’. Recall DATEDIFF, WHERE IS.

Recall

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

LeetCode 175

Combine Two Tables (Easy) [link]

SELECT P.firstName, P.lastName, A.city, A.state
FROM Person P
LEFT JOIN Address A
ON P.personId = A.personId;

LeetCode 197

Rising Temperature (Easy) [link]

Using WHERE to link two tables

SELECT w1.id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1 
    AND w1.temperature > w2.temperature;

Using JOIN

SELECT w1.id
FROM Weather w1
JOIN Weather w2
ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

DATEDIFF(date1, date2) = date1 - date2.

LeetCode 607

Sales Person (Easy) [link]

Both LEFT JOIN and JOIN work.

SELECT name
FROM SalesPerson
WHERE sales_id NOT IN (
    SELECT O.sales_id
    FROM Orders O
    JOIN Company C
    ON C.com_id = O.com_id
    WHERE C.name = 'RED'
);

LeetCode 1148

Article Views I (Easy) [link]

SELECT DISTINCT author_id as id
FROM Views
WHERE author_id = viewer_id
ORDER BY ids;

LeetCode 1581

Customer Who Visited But Did Not Make Any Transaction (Easy) [link]

Using WHERE

SELECT customer_id, COUNT(customer_id) AS count_no_trans
FROM Visits V
WHERE V.visit_id NOT IN (
    SELECT visit_id
    FROM Transactions
)
GROUP BY customer_id;

Using LEFT JOIN

SELECT customer_id, COUNT(customer_id) AS count_no_trans
FROM Visits V
LEFT JOIN Transactions T
ON V.visit_id = T.visit_id
WHERE amount IS NULL
GROUP BY customer_id;