sql
SQL Study Guide
1. Select
:::spoiler Solution
SELECT
name
FROM
Customer
WHERE
referee_id != 2
OR referee_id IS NULL;
:::
1757. Recyclable and Low Fat Products
:::spoiler Solution
SELECT
product_id
FROM
products
WHERE
low_fats = 'Y'
AND recyclable = 'Y';
:::
:::spoiler Solution
SELECT
name,
population,
area
FROM
World
WHERE
area >= 3000000
OR population >= 25000000;
:::
:::spoiler Solution
SELECT
DISTINCT author_id AS id
FROM
Views
WHERE
author_id = viewer_id
ORDER BY
id;
:::
:::spoiler Solution
SELECT
tweet_id
FROM
Tweets
WHERE
CHAR_LENGTH(content) > 15;
:::
2. Basic Joins
:::spoiler Solution
SELECT w1.id AS id
FROM Weather AS w1
JOIN Weather AS w2
ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;
:::
:::spoiler Solution
SELECT e.name, b.bonus
FROM Employee AS e
LEFT JOIN Bonus AS b ON e.empid = b.empid
WHERE bonus < 1000 OR bonus IS NULL;
:::
1068. Product Sales Analysis I
:::spoiler Solution
SELECT
p.product_name,
s.year,
s.price
FROM
Sales AS s
LEFT JOIN Product AS p ON s.product_id = p.product_id;
:::
1280. Students and Examinations
:::spoiler Solution
SELECT
s.student_id, s.student_name, sub.subject_name, IFNULL(grouped.attended_exams, 0) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN (
SELECT student_id, subject_name, COUNT(*) AS attended_exams
FROM Examinations
GROUP BY student_id, subject_name
) grouped
ON s.student_id = grouped.student_id AND sub.subject_name = grouped.subject_name
ORDER BY s.student_id, sub.subject_name;
:::
1378. Replace Employee ID With The Unique Identifier
:::spoiler Solution
SELECT b.unique_id, a.name FROM Employees AS a
LEFT JOIN EmployeeUNI AS b
ON a.id = b.id;
:::
1581. Customer Who Visited but Did Not Make Any Transactions
:::spoiler Solution
SELECT customer_id, COUNT(visit_id) AS count_no_trans
FROM Visits
WHERE visit_id NOT IN (
SELECT visit_id
FROM Transactions
)
GROUP BY customer_id;
:::
1661. Average Time of Process per Machine
:::spoiler Solution
SELECT machine_id,
ROUND(SUM(CASE WHEN activity_type = 'start' THEN -timestamp ELSE timestamp END) / (COUNT(DISTINCT process_id)), 3) AS processing_time
FROM Activity
GROUP BY machine_id;
:::
3. Basic Aggregate Functions
4. Sorting and Grouping
2356. Number of Unique Subjects Taught by Each Teacher
:::spoiler Solution
SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt
FROM Teacher
GROUP BY teacher_id;
:::
1141. User Activity for the Past 30 Days I
:::spoiler Solution
SELECT
activity_date AS day,
COUNT(DISTINCT user_id) AS active_users
FROM
Activity
WHERE
DATEDIFF('2019-07-27', activity_date) < 30 AND DATEDIFF('2019-07-27', activity_date)>=0
GROUP BY day;
:::
5. Advanced Select and Joins
6. Subqueries
7. Advanced String Functions / Regex / Clause
Problem Difficulty Legend
- 🟩 Easy
- 🟨 Medium
- 🟧 Medium-Hard
- 🟥 Hard
- ⬛ Very Hard