1757. Products With High Fat and Recylcable Flag

Initial attempt:

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

Corrected version:

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

584. Find Customer Referee

Initial attempt:

SELECT DISTINCT name FROM Customer WHERE referee_id != 2

Revised approach:

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

Alternative solution using IFNULL:

SELECT name FROM customer WHERE IFNULL(referee_id, 0) != 2

595. Big Countries

Direct solution:

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

1148. Article Views I

Original attempt:

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

Corrected version:

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

1683. Invalid Tweets

Error in initial attempt:

SELECT tweet_id FROM Tweets WHERE len(content) > 15

Corrected solution:

SELECT tweet_id FROM Tweets WHERE CHAR_LENGTH(content) > 15

1378. Replace Employee ID With The Unique Identifier

First approach:

SELECT name, unique_id FROM Employees INNER JOIN EmployeeUNI ON Employees.id = EmployeeUNI.id

Final solution:

SELECT name, unique_id FROM Employees LEFT JOIN EmployeeUNI ON Employees.id = EmployeeUNI.id

1068. Product Sales Analysis I

Working solution:

SELECT product_name, year, price FROM Sales LEFT JOIN Product ON Sales.product_id = Product.product_id

1581. Customer Who Visited but Did Not Make Any Transactions

Issue with initial attempt:

SELECT customer_id, transaction_id FROM Visits LEFT JOIN Transactions ON Visits.visit_id = Transactions.visit_id WHERE transaction_id = null

Corrected version:

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

197. Rising Temperature

Problematic solution:

SELECT * FROM Weather WHERE temperature > (SELECT Temperature FROM Weather WHERE recordDate = recordDate - 1)

Corrected solution:

SELECT b.id FROM weather a JOIN weather b WHERE DATEDIFF(b.recordDate, a.recordDate) = 1 AND b.temperature > a.temperature

1661. Average Time of Process per Machine

Improved version:

SELECT a.machine_id, ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time FROM Activity AS a JOIN Activity AS b ON a.machine_id = b.machine_id AND a.process_id = b.process_id AND a.activity_type = 'start' AND b.activity_type = 'end' GROUP BY machine_id

577. Employee Bonus

Optimized approach:

SELECT name, bonus FROM Employee LEFT JOIN Bonus ON Employee.empId = Bonus.empId WHERE bonus < 1000 OR bonus IS NULL

1280. Students and Examinations

Comprehensive solution:

WITH grouped AS (SELECT student_id, subject_name, COUNT(*) AS attended_exams FROM Examinations GROUP BY student_id, subject_name) SELECT Students.student_id, Students.student_name, Subjects.subject_name, IFNULL(grouped.attended_exams, 0) AS attended_exams FROM Students CROSS JOIN Subjects LEFT JOIN grouped ON Students.student_id = grouped.student_id AND Subjects.subject_name = grouped.subject_name ORDER BY Students.student_id, Subjects.subject_name

570. Managers with at Least Five Direct Reports

Corrected version:

SELECT name FROM Employee JOIN (SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(*) >= 5) Manager ON Employee.id = Manager.managerId

1934. Confirmation Rate

Complete solution:

WITH confirmed AS (SELECT user_id, COUNT(*) AS confirmed FROM Confirmations WHERE action='confirmed' GROUP BY user_id), requests AS (SELECT user_id, COUNT(*) AS requests FROM Confirmations GROUP BY user_id) SELECT a.user_id, IFNULL(ROUND(confirmed/requests, 2), 0) AS confirmation_rate FROM (SELECT s.user_id, IFNULL(requests, 0) AS requests FROM Signups s LEFT JOIN requests r ON s.user_id = r.user_id) a LEFT JOIN confirmed c ON a.user_id = c.user_id

620. Not Boring Movies

Simple working solution:

SELECT * FROM cinema WHERE id % 2 = 1 AND description != 'boring' ORDER BY rating DESC

1251. Average Selling Price

Fixed version:

SELECT Prices.product_id, IFNULL(ROUND(SUM(price * units)/SUM(units), 2), 0) AS average_price FROM Prices LEFT JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id WHERE purchase_date BETWEEN start_date AND end_date GROUP BY Prices.product_id

1075. Project Employees I

Working solution:

SELECT project_id, ROUND(AVG(experience_years), 2) AS average_years FROM Project LEFT JOIN Employee ON Project.employee_id = Employee.employee_id GROUP BY project_id

1633. Percentage of Users Registered in Contest

Corrected approach:

SELECT contest_id, ROUND(COUNT(DISTINCT user_id) / (SELECT COUNT(user_id) FROM Users) * 100, 2) AS percentage FROM Register GROUP BY contest_id ORDER BY percentage DESC, contest_id

1211. Queries Quality and Percentage

Clean solution:

SELECT query_name, ROUND(AVG(rating/position), 2) AS quality, ROUND(SUM(IF(rating < 3, 1, 0)) * 100 / COUNT(*), 2) AS poor_query_percentage FROM Queries GROUP BY query_name

1193. Monthly Transactions I

Solution:

SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, country, COUNT(*) AS trans_count, COUNT(IF(state='approved', 1, NULL)) AS approved_count, SUM(amount) AS trans_total_amount, SUM(IF(state='approved', amount, 0)) AS approved_total_amount FROM Transactions GROUP BY month, country

1174. Immediate Food Delivery II

Corrected solution:

SELECT ROUND(SUM(customer_pref_delivery_date = order_date)/COUNT(*)*100, 2) AS immediate_percentage FROM Delivery WHERE (customer_id, order_date) IN (SELECT customer_id, MIN(order_date) FROM Delivery GROUP BY customer_id)

550. Game Play Analysis IV

Fixed version:

SELECT ROUND(SUM(IF(event_date=DATE_ADD(start_date, INTERVAL 1 DAY), 1, 0))/COUNT(DISTINCT Activity.player_id), 2) AS fraction FROM Activity LEFT JOIN (SELECT player_id, MIN(event_date) AS start_date FROM Activity GROUP BY player_id) A ON Activity.player_id = A.player_id

2356. Number of Unique Subjects Taught by Each Teacher

Simple 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

Working solution:

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users FROM activity WHERE DATEDIFF('2019-07-27', activity_date) >= 0 AND DATEDIFF('2019-07-27', activity_date) < 30 GROUP BY activity_date

1084. Sales Analysis III

Corrected solution:

SELECT product_id, product_name FROM product WHERE product_id IN (SELECT DISTINCT product_id FROM sales WHERE DATEDIFF(sale_date, '2019-01-01') >= 0 AND DATEDIFF(sale_date, '2019-03-31') <= 0) AND product_id NOT IN (SELECT DISTINCT product_id FROM sales WHERE DATEDIFF(sale_date, '2019-01-01') < 0 OR DATEDIFF(sale_date, '2019-03-31') > 0)

596. Classes More Than Five Students

Direct solution:

SELECT class FROM courses GROUP BY class HAVING COUNT(student) >= 5

1729. Find Followers Count

Simple approach:

SELECT user_id, COUNT(*) AS followers_count FROM followers GROUP BY user_id ORDER BY user_id

619. Biggest Single Number

Solution:

SELECT IFNULL((SELECT num FROM MyNumbers GROUP BY num HAVING COUNT(num) = 1 ORDER BY num DESC LIMIT 1), NULL) AS num

1045. Customers Who Bought All Products

Correct approach:

SELECT customer_id FROM customer GROUP BY customer_id HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(DISTINCT product_key) FROM product)

1731. The Number of Employees Which Report to Each Employee

Working solution:

SELECT b.employee_id, b.name, COUNT(a.name) AS reports_count, ROUND(AVG(a.age), 0) AS average_age FROM employees a LEFT JOIN employees b ON a.reports_to = b.employee_id WHERE b.employee_id IS NOT NULL GROUP BY b.employee_id ORDER BY b.employee_id

1789. Primary Department for Each Employee

Window function solution:

WITH q AS (SELECT employee_id, department_id, primary_flag, COUNT(*) OVER(PARTITION BY employee_id) AS count_over FROM employee) SELECT employee_id, department_id FROM q WHERE primary_flag = 'Y' OR count_over = 1

610. Triangle Judgement

Solution:

SELECT x, y, z, CASE WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes' ELSE 'No' END AS triangle FROM triangle

180. Consecutive Numbers

Corrected approach:

SELECT DISTINCT l1.Num AS ConsecutiveNums FROM Logs l1, Logs l2, Logs l3 WHERE l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1 AND l1.Num = l2.Num AND l2.Num = l3.Num

1164. Product Price at Given Date

Window function approach:

SELECT DISTINCT p1.product_id, IFNULL(p2.new_price, 10) AS price FROM products p1 LEFT JOIN (SELECT product_id, new_price FROM (SELECT product_id, new_price, change_date, DENSE_RANK() OVER(PARTITION BY product_id ORDER BY change_date DESC) AS rnk FROM products WHERE change_date <= '2019-08-16') t WHERE rnk = 1) p2 ON p1.product_id = p2.product_id

1204. Last Person to Fit in the Bus

Window function solution:

SELECT person_name FROM (SELECT *, SUM(weight) OVER(ORDER BY turn) AS weight_sum FROM queue) t WHERE weight_sum <= 1000 ORDER BY weight_sum DESC LIMIT 1

1907. Count Salary Categories

Solution:

SELECT 'High Salary' AS category, COUNT(*) AS accounts_count FROM accounts WHERE income > 50000 UNION SELECT 'Average Salary' AS category, COUNT(*) AS accounts_count FROM accounts WHERE income BETWEEN 20000 AND 50000 UNION SELECT 'Low Salary' AS category, COUNT(*) AS accounts_count FROM accounts WHERE income < 20000

1978. Employees Whose Manager Left the Company

Direct solution:

SELECT employee_id FROM employee WHERE manager_id NOT IN (SELECT employee_id FROM employee)

626. Exchange Seats

Simple solution:

SELECT IF(id % 2 = 0, id - 1, IF(id = (SELECT COUNT(DISTINCT id) FROM seat), id, id + 1)) AS id, student FROM seat ORDER BY id

1341. Movie Rating

Union approach:

(SELECT name AS results FROM MovieRating LEFT JOIN users ON MovieRating.user_id = users.user_id GROUP BY MovieRating.user_id ORDER BY COUNT(rating) DESC, name LIMIT 1) UNION ALL (SELECT title AS results FROM MovieRating LEFT JOIN movies ON MovieRating.movie_id = movies.movie_id WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28' GROUP BY MovieRating.movie_id ORDER BY AVG(rating) DESC, title LIMIT 1)

1321. Restaurant Growth

Window functon solution:

WITH daily_total AS (SELECT visited_on, SUM(amount) AS daily_amount FROM customer GROUP BY visited_on) SELECT visited_on, SUM(daily_amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount, ROUND(AVG(daily_amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS average_amount FROM daily_total WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM customer)) >= 6

602. Friend Requests II

Correct solution:

WITH t1 AS (SELECT requester_id AS id FROM RequestAccepted UNION ALL SELECT accepter_id AS id FROM RequestAccepted) SELECT id, COUNT(*) AS num FROM t1 GROUP BY id ORDER BY num DESC LIMIT 1

585. Investment in 2016

Window function approach:

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016 FROM (SELECT tiv_2016, COUNT(*) OVER(PARTITION BY tiv_2015) AS count_tiv_2015, COUNT(*) OVER(PARTITION BY lat, lon) AS count_lat_lon FROM insurance) AS temp WHERE count_lat_lon = 1 AND count_tiv_2015 > 1

185. Department Top Three Salaries

Ranking solution:

WITH a AS (SELECT *, DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS r FROM employee) SELECT b.name AS Department, a.name AS employee, salary FROM a JOIN Department b ON a.departmentId = b.id WHERE r <= 3

1667. Fix Names in a Table

String manipulation solution:

SELECT user_id, CONCAT(UPPER(LEFT(name, 1)), LOWER(RIGHT(name, LENGTH(name)-1))) AS name FROM users ORDER BY user_id

1527. Patients with a Condition

Regular expression solution:

SELECT * FROM patients WHERE conditions REGEXP '^DIAB1|\\sDIAB1'

196. Delete Duplicate Emails

Self join solution:

DELETE p1 FROM Person p1, Person p2 WHERE p1.email = p2.email AND p1.id > p2.id

176. Second Highest Salary

Subquery approach:

SELECT IFNULL((SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1, 1), NULL) AS SecondHighestSalary

1484. Group Sold Products By The Date

Group concatenation solution:

SELECT sell_date, COUNT(DISTINCT product) AS num_sold, GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products FROM activities GROUP BY sell_date ORDER BY sell_date

1327. List the Products Ordered in a Period

Date filtering solution:

SELECT product_name, SUM(unit) AS unit FROM orders LEFT JOIN products ON orders.product_id = products.product_id WHERE DATE_FORMAT(order_date, '%Y-%m') = '2020-02' GROUP BY product_name HAVING unit >= 100

1517. Find Users With Valid E-Mails

Email validation solution:

SELECT * FROM users WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode[.]com$'