SQL Study Guide

1. Select

584. Find Customer Referee

Solution

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

1757. Recyclable and Low Fat Products

Solution

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

595. Big Countries

Solution

SELECT 
  name, 
  population, 
  area 
FROM 
  World 
WHERE 
  area >= 3000000 
  OR population >= 25000000;

1148. Article Views I

Solution

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

1683. Invalid Tweets

Solution

SELECT 
  tweet_id 
FROM 
  Tweets 
WHERE 
  CHAR_LENGTH(content) > 15;

2. Basic Joins

197. Rising Temperature

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;

577. Employee Bonus

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

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

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

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

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

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

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

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

Additional Resources

• SQL Tutorial (W3Schools)
• SQL Basics (Khan Academy)
• SQL Zoo
• Mode SQL Tutorial