[MySQL] LeetCode 문제 연습 8

1907. Count Salary Categories (Medium)

• Table: Accounts
  +-------------+------+
  | Column Name | Type |
  +-------------+------+
  | account_id | int |
  | income | int |
  +-------------+------+
  account_id is the primary key (column with unique values) for this table.
  Each row contains information about the monthly income for one bank account.
  
  Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:
  
  - "Low Salary": All the salaries strictly less than $20000.
  - "Average Salary": All the salaries in the inclusive range [$20000, $50000].
  - "High Salary": All the salaries strictly greater than $50000.
  
  The result table must contain all three categories. If there are no accounts in a category, return 0.
  
  Return the result table in any order.

제출한 쿼리 :

SELECT 'Low Salary' AS category
       , COUNT(*) AS accounts_count
FROM Accounts
WHERE income < 20000

UNION ALL

SELECT 'Average Salary' AS category
       , COUNT(*) AS accounts_count
FROM Accounts
WHERE income BETWEEN 20000 AND 50000

UNION ALL

SELECT 'High Salary' AS category
       , COUNT(*) AS accounts_count
FROM Accounts
WHERE income > 50000;

---

1978. Employees Whose Manager Left the Company (Easy)

• Table: Employees
  +-------------+----------+
  | Column Name | Type |
  +-------------+----------+
  | employee_id | int |
  | name | varchar |
  | manager_id | int |
  | salary | int |
  +-------------+----------+
  In SQL, employee_id is the primary key for this table.
  This table contains information about the employees, their salary, and the ID of their manager. Some employees do not have a manager (manager_id is null).
  
  Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_id set to the manager that left.
  
  Return the result table ordered by employee_id.

제출한 쿼리 :

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

---

626. Exchange Seats (Medium)

• Table: Seat

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| student | varchar |
+-------------+---------+
id is the primary key (unique value) column for this table.
Each row of this table indicates the name and the ID of a student.
id is a continuous increment.

Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Return the result table ordered by id in ascending order.

제출한 쿼리 :

SELECT CASE WHEN (s.id % 2 <> 0) AND s.id = (SELECT COUNT(*) FROM Seat) THEN s.id
            WHEN s.id % 2 = 0 THEN s.id - 1
            ELSE s.id + 1
       END AS id, student
FROM Seat AS s
ORDER BY id

---

1341. Movie Rating (Medium)

• Table: Movies
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | movie_id | int |
  | title | varchar |
  +---------------+---------+
  movie_id is the primary key (column with unique values) for this table.
  title is the name of the movie.
  
  Table: Users
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | user_id | int |
  | name | varchar |
  +---------------+---------+
  user_id is the primary key (column with unique values) for this table.
  
  Table: MovieRating
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | movie_id | int |
  | user_id | int |
  | rating | int |
  | created_at | date |
  +---------------+---------+
  (movie_id, user_id) is the primary key (column with unique values) for this table.
  This table contains the rating of a movie by a user in their review.
  created_at is the user's review date.
  
  Write a solution to:
  - Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.
  - Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.

제출한 쿼리 :

SELECT name AS results
FROM MovieRating 
JOIN Users USING(user_id)
GROUP BY name
ORDER BY COUNT(*) DESC, name
LIMIT 1

UNION ALL

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

---

1321. Restaurant Growth (Medium)

• Table: Customer
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | customer_id | int |
  | name | varchar |
  | visited_on | date |
  | amount | int |
  +---------------+---------+
  In SQL,(customer_id, visited_on) is the primary key for this table.
  This table contains data about customer transactions in a restaurant.
  visited_on is the date on which the customer with ID (customer_id) has visited the restaurant.
  amount is the total paid by a customer.
  
  You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).
  
  Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.
  
  Return the result table ordered by visited_on in ascending order.

제출한 쿼리 :

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;

---

오늘의 TMI

새로운 물고기 데려왔다...!