SQL 50

My solutions to the Top 50 SQL questions from LeetCode

Crack SQL Interview in 50 Qs

Select

1757. Recyclable and Low Fat Products

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

584. Find Customer Referee

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

In SQL, NULL represents an unknown or missing value. When you compare any value with NULL, the result of the comparison is also NULL, which is interpreted as FALSE in the context of WHERE clauses.

595. Big Countries

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

1148. Article Views I

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

1683. Invalid Tweets

SELECT tweet_id
FROM Tweets
WHERE length(content) > 15

---

Basic Joins

1378. Replace Employee ID With The Unique Identifier

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

1068. Product Sales Analysis I

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

1581. Customer Who Visited but Did Not Make Any Transactions

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

197. Rising Temperature

SELECT id
FROM Weather T1
WHERE temperature >
    (
        SELECT temperature
        FROM Weather T2
        WHERE T2.recordDate = DATE_SUB(T1.recordDate, INTERVAL 1 DAY)
        --WHERE T2.recordDate = DATE_ADD(T1.recordDate, INTERVAL -1 DAY)
        --WHERE T2.recordDate = SUBDATE(T1.recordDate, 1)
        --WHERE T2.recordDate = ADDDATED(T1.recordDate, -1)
    )

1661. Average Time of Process per Machine

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

577. Employee Bonus

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

1280. Students and Examinations

SELECT s.student_id, student_name, sub.subject_name,
       COUNT(e.student_id) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e
ON s.student_id = e.student_id AND sub.subject_name = e.subject_name
GROUP BY s.student_id, sub.subject_name
ORDER BY s.student_id, sub.subject_name;

570. Managers with at Least 5 Direct Reports

SELECT name
FROM Employee
WHERE id IN
    (
        SELECT managerId
        FROM Employee
        GROUP BY managerId
        HAVING COUNT(managerId) >= 5
    )

1934. Confirmation Rate

SELECT s.user_id,
       ROUND(AVG(IF(action='confirmed', 1, 0)), 2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c
ON s.user_id = c.user_id
GROUP BY s.user_id

---

Basic Aggregate Functions

620. Not Boring Movies

SELECT *
FROM Cinema
WHERE (id % 2) = 1 AND description <> "boring"
ORDER BY rating DESC;

1251. Average Selling Price

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

1075. Project Employees I

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

--OR
-- SELECT project_id, ROUND(SUM(experience_years)/ COUNT(p.employee_id), 2) AS average_years
-- FROM Project p
-- LEFT JOIN Employee e
-- ON p.employee_id = e.employee_id
-- GROUP BY project_id

1633. Percentage of Users Attended a Contest

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

1211. Queries Quality and Percentage

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

1193. Monthly Transactions I

SELECT LEFT(trans_date, 7) AS month, country,
    COUNT(id) AS trans_count,
    SUM(state = 'approved') AS approved_count,
    SUM(amount) AS trans_total_amount ,
    SUM(amount * (state = 'approved')) AS approved_total_amount
FROM Transactions
GROUP BY month, country

1174. Immediate Food Delivery II

SELECT ROUND(AVG(order_date = customer_pref_delivery_date) * 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

SELECT ROUND( COUNT(player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM Activity
WHERE
    (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))
    IN(
            SELECT player_id, MIN(event_date) AS first_login
            FROM Activity
            GROUP BY player_id
    )

---

Sorting and Grouping

2356. Number of Unique Subjects Taught by Each Teacher

SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt
FROM Teacher
GROUP BY teacher_id

1141. User Activity for the Past 30 Days I

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY activity_date

1070. Product Sales Analysis III

SELECT product_id, year AS first_year, quantity, price
FROM Sales
WHERE (product_id, year) IN (
    SELECT product_id, MIN(year)
    FROM Sales
    GROUP BY product_id
)

596. Classes More Than 5 Students

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

1729. Find Followers Count

SELECT user_id, COUNT(follower_id) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id

619. Biggest Single Number

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

--OR
-- SELECT MAX(num) AS num
-- FROM (
--     SELECT num
--     FROM MyNumbers
--     GROUP BY num
--     HAVING COUNT(num) = 1
--     ) myTable;

1045. Customers Who Bought All Products

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product)

---

Advanced Select and Joins

1731. The Number of Employees Which Report to Each Employee

SELECT mgr.employee_id, mgr.name,
    COUNT(e1.employee_id) AS reports_count,
    ROUND(AVG(e1.age), 0) AS average_age
FROM Employees mgr
JOIN Employees e1
ON mgr.employee_id = e1.reports_to
GROUP BY mgr.employee_id
ORDER BY mgr.employee_id

1789. Primary Department for Each Employee

SELECT employee_id, department_id
FROM Employee
WHERE primary_flag = "Y" OR
    employee_id in (
        SELECT employee_id
        FROM Employee
        GROUP BY employee_id
        HAVING COUNT(employee_id) = 1
        )

610. Triangle Judgement

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

180. Consecutive Numbers

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 a Given Date

SELECT distinct product_id, 10 as price
FROM Products
GROUP BY product_id
HAVING MIN(change_date) > "2019-08-16"

UNION

SELECT product_id, new_price as price
FROM Products
WHERE (product_id, change_date) IN (
    SELECT product_id, max(change_date) as recent_date
    FROM Products
    WHERE change_date <= "2019-08-16"
    GROUP BY product_id
)

GROUP BY product_id
HAVING MAX(change_date) <= "2019-08-16"

1204. Last Person to Fit in the Bus

SELECT person_name
FROM Queue q1
WHERE 1000 >= (
    SELECT SUM(weight)
    FROM Queue q2
    WHERE q2.turn <= q1.turn
)
ORDER BY turn DESC
LIMIT 1;

1907. Count Salary Categories

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

---

Subqueries

1978. Employees Whose Manager Left the Company

SELECT employee_id
FROM Employees
WHERE salary < 30000 AND manager_id NOT IN(
    SELECT employee_id
    FROM Employees
)
ORDER BY employee_id

626. Exchange Seats

SELECT IF (id < (SELECT MAX(id) FROM Seat),
            IF(id % 2 = 0, id - 1, id + 1),
            IF(id % 2 = 0, id - 1, id)
        ) AS id, student
FROM Seat
ORDER BY id;

1341. Movie Rating

(SELECT name AS results
FROM Users u
JOIN MovieRating mr
ON u.user_id = mr.user_id
GROUP BY name
ORDER BY COUNT(rating) DESC, name
LIMIT 1)

UNION ALL

(SELECT title AS results
FROM Movies m
JOIN MovieRating mr
ON m.movie_id = mr.movie_id
WHERE EXTRACT(YEAR_MONTH FROM created_at) = 202002
GROUP BY title
ORDER BY AVG(rating) DESC, title
LIMIT 1)

1321. Restaurant Growth

SELECT visited_on,
    (
        SELECT SUM(amount)
        FROM Customer
        WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
    ) AS amount,
    ROUND(
        (
        SELECT SUM(amount) / 7
        FROM Customer
        WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
        )
    , 2) AS average_amount
FROM Customer c
WHERE visited_on >= (
    SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
    FROM Customer
)
GROUP BY visited_on;

602. Friend Requests II: Who Has the Most Friends

WITH base AS(
    SELECT requester_id id FROM RequestAccepted
    UNION ALL
    SELECT accepter_id id FROM RequestAccepted
)

SELECT id, COUNT(id) num FROM base
GROUP BY id
ORDER BY num DESC
LIMIT 1;

585. Investments in 2016

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE tiv_2015 IN (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(tiv_2015) > 1
) AND (lat, lon) IN (
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(lat) = 1
)

185. Department Top Three Salaries

SELECT d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
WHERE 3 > (
    SELECT COUNT(DISTINCT e2.salary)
    FROM Employee e2
    WHERE e.departmentId = e2.departmentId AND
        e2.salary > e.salary
)
ORDER BY Department, Salary DESC;

---

Advanced String Functions / Regex / Clause

1667. Fix Names in a Table

SELECT user_id,
    CONCAT(UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name, 2))) AS name
FROM Users
ORDER BY user_id

1527. Patients With a Condition

SELECT *
FROM Patients
WHERE conditions LIKE "% DIAB1%" OR conditions LIKE "DIAB1%"

196. Delete Duplicate Emails

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

176. Second Highest Salary

SELECT
(SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1
OFFSET 1) AS SecondHighestSalary

1484. Group Sold Products By The Date

SELECT sell_date,
    COUNT(DISTINCT product) AS num_sold,
    GROUP_CONCAT(DISTINCT product ORDER BY product ASC separator ',')AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date

1327. List the Products Ordered in a Period

SELECT product_name, SUM(unit) AS unit
FROM Orders o
LEFT JOIN Products p
ON o.product_id = p.product_id
WHERE order_date BETWEEN '2020-02-01' AND '2020-02-029'
GROUP BY p.product_id
HAVING SUM(unit) >= 100

1517. Find Users With Valid E-Mails

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

---