LeetCode Diary (daily update)

A LeetCode a day, keep unemployment away ;)

Starting today, I will be solving 5 LeetCode problems per day for at least one month. In this article, I will be documenting the problems that I solve to keep myself motivated. I’ll also be putting down the solutions as well as some notes as I code along. So, let’s do this together and get coding!

Day 1.

• Combine Two Tables

# Write your MySQL query statement below
SELECT FirstName, LastName, City, State
FROM Person
LEFT JOIN Address
ON Person.PersonId=Address.PersonId;

¶ In this case where each column has a unique name for all tables, we don’t need to select Person.FirstName, Person.LastName, Address.City, Address.State

¶ A LEFT JOIN performs a join starting with the first (left-most) table.
Then, any matched records from the second table (right-most) will be included. LEFT JOIN and LEFT OUTER JOIN are the same.

• Second Highest Salary

SELECT DISTINCT Salary
AS SecondHighestSalary
FROM Employee
ORDER BY Salary DESC 
LIMIT 1 OFFSET 1;

¶ The OFFSET clause specifies the number of rows to skip before starting to return rows from the query.

• Nth Highest Salary

First attempt:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
    SELECT DISTINCT Salary FROM Employee 
      ORDER BY Salary DESC 
      LIMIT 1 OFFSET N-1
  );
END

So it turned out that we’re not allowed to use calculations within the return function. So we have to move that out of the return block.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
    SELECT DISTINCT Salary FROM Employee 
      ORDER BY Salary DESC 
      LIMIT 1 OFFSET N
  );
END

Here, this will give us the desired output. Remember that if value N hasn’t been declared as INT, instead of setting N=N-1, we’d need to first DECLARE m INT , then m = N-1 (m can be anything here). And use “m” in the query below in the Return block.

• Rank Scores

SELECT Score, 
DENSE_RANK() OVER(ORDER BY Score DESC) AS "Rank"
FROM Scores;

For this question, I used a window function to solve. There are other ways to solve this without using a window function; but this way the code would be much shorter and cleaner.

¶ Formula: Function() OVER(PARTITION BY x ORDERED BY y ASC/DESC)

⇨ classified by x

⇨ ordered by y

Frequently used functions for example(in similar cases): RANK() , ROW_NUMBER() , DENSE_RANK() , etc.

Here, for “Rank” we add double quotation marks because Rank is a reversed word. So for MySQL solutions, to escape reserved words used as column names, you can use an apostrophe before and after the keyword. For example `Rank`.

¶ Reserved words are SQL keywords and other symbols that have special meanings when they are processed by the Relational Engine. Reserved words are not recommended for use as database, table, column, variable or other object names. If a reserved word is used as an object name, it must be enclosed in double-quotes to notify the Relational Engine that the word is not being used as a keyword in the given context.

• Consecutive Numbers

Solution #1 (Self Join):

# Write your MySQL query statement below
SELECT l1.Num AS ConsecutiveNums 
FROM Logs l1
JOIN Logs l2 ON l2.Id = l1.Id +1  AND l1.Num = l2.Num 
JOIN Logs l3 ON l3.Id = l2.Id +1 AND l2.Num = l3.Num;

Solution #2:

# Write your MySQL query statement below
SELECT DISTINCT
   l1.Num AS ConsecutiveNums
FROM
   Logs AS 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;

So through this question, I’ve also learned that: 1. The keywordAS here is optional. 2. A way to approach to solving these type of questions, we can first type out the structure (for example:SELECT , FROM , WHERE), and fill in the blanks as you work your way up/ down.

Day 2.

• Employees Earning More Than Their Managers

Thought process: Algorithm

As this table has the employee’s manager information, we probably need to select information from it twice. Like this (step 1):

SELECT *
FROM Employee AS e1, Employee AS e2
;

Approach I.

# Write your MySQL query statement below
SELECT e1.Name AS Employee
FROM Employee AS e1,
     Employee AS e2
WHERE e1.ManagerId = e2.Id AND
      e1.Salary > e2.Salary;

Approach II.

# Write your MySQL query statement below
SELECT e1.Name AS 'Employee'
FROM Employee e1
JOIN Employee e2
ON e2.Id= e1.ManagerId
WHERE e1.Salary > e2.Salary;

Here, it can be a SELF JOIN, LEFT JOIN, RIGHT JOIN. Doesn’t matter. AS is also optional.

• Duplicate Emails

Approach I.

# Write your MySQL query statement below
SELECT DISTINCT p1.Email AS Email
FROM Person p1,
     Person p2
WHERE p1.Id <> p2.Id AND
      p1.Email = p2.Email;

Approach II.

SELECT Email FROM
( SELECT Email, COUNT(Email) AS num
  FROM Person
  GROUP BY Email
) AS statistic
WHERE num > 1
;

Approach III. (a more efficient version of Approach II.)

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;

The difference between (II vs. III) the having and where clause in SQL is that HAVING allows you to filter on aggregate functions as oppose to WHERE. The WHERE clause cannot be used with aggregates, but on row’s data.

¶ An aggregate function performs a calculation one or more values and returns a single value. The aggregate function is often used with the GROUP BY clause and HAVING clause of the SELECT statement.

• Customers Who Never Order

Approach I. (NOT IN)

# Write your MySQL query statement below
SELECT Name AS Customers
FROM Customers
WHERE Customers.Id NOT IN (SELECT CustomerId FROM Orders);

Approach II. (LEFT JOIN)

# Write your MySQL query statement below
SELECT cus.Name AS Customers 
FROM Customers cus
LEFT JOIN Orders ord
ON cus.Id = ord.CustomerId 
WHERE ord.CustomerId IS NULL;

• Department Highest Salary

# Write your MySQL query statement below
SELECT Department.Name AS Department,
       Employee.Name AS Employee,
       Salary
FROM Employee
  LEFT JOIN Department 
  ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId, Salary) IN (SELECT DepartmentId, Max(Salary) 
                                 FROM Employee
                                 GROUP BY DepartmentId);

Okay… I should practice more problems with subquery.

¶ A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Simply put, inner query gets executed first, and generates the table that the outer query will be based upon.

• Department Top Three Salaries

Approach I.

# Write your MySQL query statement below
SELECT dp.Name AS "Department",
       ep.Name AS "Employee",
       ep.Salary AS "Salary"
FROM Employee AS ep
INNER JOIN Department AS dp 
ON ep.DepartmentId = dp.Id
WHERE (SELECT COUNT(DISTINCT Salary)
       FROM Employee AS ep1
       WHERE ep1.DepartmentId = ep.DepartmentId AND ep1.Salary > ep.Salary) < 3;

A top 3 salary in this company means there is no more than 3 salary bigger than itself in the company.

Or: (from LeetCode)

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

Approach II. (DENSE_RANK)

# Write your MySQL query statement below
SELECT Department.Name AS "Department",
       e.Name AS "Employee",
       e.Salary 
FROM (SELECT DepartmentId, Name, Salary,
      DENSE_RANK() OVER(PARTITION BY DepartmentId 
                        ORDER BY Salary DESC) 
      AS r FROM Employee) As e
JOIN Department 
     ON e.DepartmentId = Department.Id
WHERE r <= 3;

Day 3.

• Delete Duplicate Emails

# Write your MySQL query statement below
DELETE p1
FROM Person p1,
     Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id;

DELETE statement (here, we can just replace the select statement with delete)

First, join the table with itself on the Email column. Then, we need to find the bigger id having same email address with other records. So we can add a new condition to the WHERE clause.

• Rising Temperature

# Write your MySQL query statement below
SELECT w2.id 
FROM Weather w1
JOIN Weather w2
ON w2.Temperature > w1.Temperature AND DATEDIFF(w2.recordDate, w1.recordDate) = 1
WHERE w2.recordDate > w1.recordDate;

• Trips and Users