[MySQL] LeetCode 문제 연습 4

1251. Average Selling Price (Easy)

• Table: Prices
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | product_id | int |
  | start_date | date |
  | end_date | date |
  | price | int |
  +---------------+---------+
  (product_id, start_date, end_date) is the primary key (combination of columns with unique values) for this table.
  Each row of this table indicates the price of the product_id in the period from start_date to end_date.
  For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.
  
  Table: UnitsSold
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | product_id | int |
  | purchase_date | date |
  | units | int |
  +---------------+---------+
  This table may contain duplicate rows.
  Each row of this table indicates the date, units, and product_id of each product sold.
  
  Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places.
  
  Return the result table in any order.

제출한 쿼리 :

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

---

1075. Project Employees I (Easy)

• Table: Project
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | project_id | int |
  | employee_id | int |
  +-------------+---------+
  (project_id, employee_id) is the primary key of this table.
  employee_id is a foreign key to Employee table.
  Each row of this table indicates that the employee with employee_id is working on the project with project_id.
  
  Table: Employee
  +------------------+---------+
  | Column Name | Type |
  +------------------+---------+
  | employee_id | int |
  | name | varchar |
  | experience_years | int |
  +------------------+---------+
  employee_id is the primary key of this table. It's guaranteed that experience_years is not NULL.
  Each row of this table contains information about one employee.
  
  Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.
  
  Return the result table in any order.

제출한 쿼리 :

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

---

1633. Percentage of Users Attended a Contest (Easy)

• Table: Users
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | user_id | int |
  | user_name | varchar |
  +-------------+---------+
  user_id is the primary key (column with unique values) for this table.
  Each row of this table contains the name and the id of a user.
  
  Table: Register
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | contest_id | int |
  | user_id | int |
  +-------------+---------+
  (contest_id, user_id) is the primary key (combination of columns with unique values) for this table.
  Each row of this table contains the id of a user and the contest they registered into.
  
  Write a solution to find the percentage of the users registered in each contest rounded to two decimals.

Return the result table ordered by percentage in descending order. In case of a tie, order it by contest_id in ascending order.

제출한 쿼리 :

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

---

1211. Queries Quality and Percentage (Easy)

• Table: Queries
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | query_name | varchar |
  | result | varchar |
  | position | int |
  | rating | int |
  +-------------+---------+
  This table may have duplicate rows.
  This table contains information collected from some queries on a database.
  The position column has a value from 1 to 500.
  The rating column has a value from 1 to 5. Query with rating less than 3 is a poor query.
  

We define query quality as:

The average of the ratio between query rating and its position.

We also define poor query percentage as:

The percentage of all queries with rating less than 3.

Write a solution to find each query_name, the quality and poor_query_percentage.

Both quality and poor_query_percentage should be rounded to 2 decimal places.

Return the result table in any order.

제출한 쿼리 :

 SELECT query_name
        , ROUND(AVG(CAST(rating AS DECIMAL) / position), 2) AS "quality"
        , ROUND(SUM(CASE WHEN rating < 3 THEN 1 
                         ELSE 0
                    END) * 100 / COUNT(*), 2) AS "poor_query_percentage"
FROM Queries
WEHRE query_name IS NOT NULL 
GROUP BY query_name;

---

1193. Monthly Transactions I (Medium)

• Table: Transactions
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | id | int |
  | country | varchar |
  | state | enum |
  | amount | int |
  | trans_date | date |
  +---------------+---------+
  id is the primary key of this table.
  The table has information about incoming transactions.
  The state column is an enum of type ["approved", "declined"].
  
  Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.
  
  Return the result table in any order.

제출한 쿼리 :

SELECT SUBSTR(trans_date, 1, 7) AS "month"
	   , country, COUNT(id) AS trans_count
	   , SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS "approved_count"
	   , SUM(amount) AS trans_total_amount
	   , SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS "approved_total_amount"
FROM Transactions
GROUP BY `month`, country

---

오늘의 TMI