[MySQL] LeetCode 문제 연습 9

602. Friend Requests II: Who Has the Most Friends (Medium)

• Table: RequestAccepted
  +----------------+---------+
  | Column Name | Type |
  +----------------+---------+
  | requester_id | int |
  | accepter_id | int |
  | accept_date | date |
  +----------------+---------+
  (requester_id, accepter_id) is the primary key (combination of columns with unique values) for this table.
  This table contains the ID of the user who sent the request, the ID of the user who received the request, and the date when the request was accepted.
  
  Write a solution to find the people who have the most friends and the most friends number.
  
  The test cases are generated so that only one person has the most friends.

제출한 쿼리 :

WITH AllFriends AS (
    SELECT id
           , COUNT(*) AS num
    FROM (
          SELECT requester_id AS id
          FROM RequestAccepted
          
          UNION ALL
          
          SELECT accepter_id AS id
          FROM RequestAccepted
         ) AS combined
    GROUP BY id
                   )

, MaxFriends AS (
    SELECT id, num
    FROM AllFriends
    WHERE num = (SELECT MAX(num) FROM AllFriends)
)
SELECT id, num
FROM MaxFriends
ORDER BY id;

---

585. Investments in 2016 (Medium)

• Table: Insurance
  +-------------+-------+
  | Column Name | Type |
  +-------------+-------+
  | pid | int |
  | tiv_2015 | float |
  | tiv_2016 | float |
  | lat | float |
  | lon | float |
  +-------------+-------+
  pid is the primary key (column with unique values) for this table.
  Each row of this table contains information about one policy where:
  pid is the policyholder's policy ID.
  tiv_2015 is the total investment value in 2015 and tiv_2016 is the total investment value in 2016.
  lat is the latitude of the policy holder's city. It's guaranteed that lat is not NULL.
  lon is the longitude of the policy holder's city. It's guaranteed that lon is not NULL.
  
  Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who:
  - have the same tiv_2015 value as one or more other policyholders, and
  - are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute pairs must be unique).
  
  Round tiv_2016 to two decimal places.

제출한 쿼리 :

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(*) > 1
       )
    AND (lat, lon) 
    NOT IN (
            SELECT lat, lon
            FROM (
                  SELECT lat
                         , lon
                         , COUNT(*) AS cnt
                  FROM Insurance
                  GROUP BY lat, lon
                 ) AS temp
            WHERE cnt > 1
           );

---

185. Department Top Three Salaries (Hard)

• Table: Employee
  +--------------+---------+
  | Column Name | Type |
  +--------------+---------+
  | id | int |
  | name | varchar |
  | salary | int |
  | departmentId | int |
  +--------------+---------+
  id is the primary key (column with unique values) for this table.
  departmentId is a foreign key (reference column) of the ID from the Department table.
  Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.
  
  Table: Department
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | id | int |
  | name | varchar |
  +-------------+---------+
  id is the primary key (column with unique values) for this table.
  Each row of this table indicates the ID of a department and its name.
  
  A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.
  
  Write a solution to find the employees who are high earners in each of the departments.
  
  Return the result table in any order.

제출한 쿼리 :

WITH table1 AS (
    SELECT employee.*
           , department.name AS Department 
    FROM employee 
    INNER JOIN department 
    ON employee.departmentId = department.id)

SELECT Department
       , name AS Employee
       , salary AS Salary 
FROM (
      SELECT *
             , DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS rank1 
      FROM table1
     ) t
WHERE rank1 <= 3

---

1667. Fix Names in a Table (Easy)

• Table: Users
  +----------------+---------+
  | Column Name | Type |
  +----------------+---------+
  | user_id | int |
  | name | varchar |
  +----------------+---------+
  user_id is the primary key (column with unique values) for this table.
  This table contains the ID and the name of the user. The name consists of only lowercase and uppercase characters.
  
  Write a solution to fix the names so that only the first character is uppercase and the rest are lowercase.
  
  Return the result table ordered by user_id.

제출한 쿼리 :

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 (Easy)

• Table: Patients
  +--------------+---------+
  | Column Name | Type |
  +--------------+---------+
  | patient_id | int |
  | patient_name | varchar |
  | conditions | varchar |
  +--------------+---------+
  patient_id is the primary key (column with unique values) for this table.
  'conditions' contains 0 or more code separated by spaces.
  This table contains information of the patients in the hospital.
  
  Write a solution to find the patient_id, patient_name, and conditions of the patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.
  
  Return the result table in any order.

제출한 쿼리 :

SELECT patient_id
       , patient_name
       , conditions
FROM Patients
WHERE conditions LIKE 'DIAB1%' OR
      conditions LIKE '% DIAB1%' OR
      conditions LIKE '% DIAB1' OR
      conditions LIKE '% DIAB1 %';

---