[MySQL] LeetCode 문제 연습 2

1378. Replace Employee ID With The Unique Identifier (Easy)

• Table: Employees
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | id | int |
  | name | varchar |
  +---------------+---------+
  id is the primary key (column with unique values) for this table.
  Each row of this table contains the id and the name of an employee in a company
  
  Table: Employees
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | id | int |
  | unique_id | int |
  +---------------+---------+
  (id, unique_id) is the primary key (combination of columns with unique values) for this table.
  Each row of this table contains the id and the corresponding unique id of an employee in the company.
  
  Write a solution to show the unique ID of each user, If a user does not have a unique ID replace just show null.
  
  Return the result table in any order.

제출한 쿼리 :

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

---

1068. Product Sales Analysis I (Easy)

• Table: Sales
  +-------------+-------+
  | Column Name | Type |
  +-------------+-------+
  | sale_id | int |
  | product_id | int |
  | year | int |
  | quantity | int |
  | price | int |
  +-------------+-------+
  (sale_id, year) is the primary key (combination of columns with unique values) of this table.
  product_id is a foreign key (reference column) to Product table.
  Each row of this table shows a sale on the product product_id in a certain year.
  Note that the price is per unit.
  
  Table: Product
  +--------------+---------+
  | Column Name | Type |
  +--------------+---------+
  | product_id | int |
  | product_name | varchar |
  +--------------+---------+
  product_id is the primary key (column with unique values) of this table.
  Each row of this table indicates the product name of each product.
  
  Write a solution to report the product_name, year, and price for each sale_id in the Sales table.
  
  Return the resulting table in any order.

제출한 쿼리 :

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

---

1581. Customer Who Visited but Did Not Make Any Transactions (Easy)

• Table: Visits
  
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | visit_id | int |
  | customer_id | int |
  +-------------+---------+
  visit_id is the column with unique values for this table.
  This table contains information about the customers who visited the mall.
  
  Table: Transactions
  
  +----------------+---------+
  | Column Name | Type |
  +----------------+---------+
  | transaction_id | int |
  | visit_id | int |
  | amount | int |
  +----------------+---------+
  transaction_id is column with unique values for this table.
  This table contains information about the transactions made during the visit_id.
  
  Write a solution to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.
  
  Return the result table sorted in any order.

제출한 쿼리 :

SELECT v.customer_id,
       COUNT(v.visit_id) AS "count_no_trans"
FROM Visits AS v
LEFT JOIN Transactions AS t
ON v.visit_id = t.visit_id
WHERE t.amount IS NULL
GROUP BY v.customer_id;

---

197. Rising Temperature (Easy)

• Table: Weather
  
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | id | int |
  | recordDate | date |
  | temperature | int |
  +---------------+---------+
  id is the column with unique values for this table.
  There are no different rows with the same recordDate.
  This table contains information about the temperature on a certain day.
  
  Write a solution to find all dates' Id with higher temperatures compared to its previous dates (yesterday).
  
  Return the result table in any order.

제출한 쿼리 :

SELECT w1.id
FROM Weather AS w1, Weather AS w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1
	AND w1.temperature > w2.temperature;

---

1661. Average Time of Process per Machine (Easy)

• Table: Activity
  
  +----------------+---------+
  | Column Name | Type |
  +----------------+---------+
  | machine_id | int |
  | process_id | int |
  | activity_type | enum |
  | timestamp | float |
  +----------------+---------+
  The table shows the user activities for a factory website.
  (machine_id, process_id, activity_type) is the primary key (combination of columns with unique values) of this table.
  machine_id is the ID of a machine.
  process_id is the ID of a process running on the machine with ID machine_id.
  activity_type is an ENUM (category) of type ('start', 'end').
  timestamp is a float representing the current time in seconds.
  'start' means the machine starts the process at the given timestamp and 'end' means the machine ends the process at the given timestamp.
  The 'start' timestamp will always be before the 'end' timestamp for every (machine_id, process_id) pair.
  
  There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.
  
  The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run.
  
  The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places.

Return the result table in any order.

제출한 쿼리 :

SELECT machine_id
	   , ROUND(AVG(end_time - start_time), 3) AS "processing_time"
FROM (
	SELECT machine_id
		   , process_id
		   , MAX(CASE WHEN activity_type = 'start' THEN timestamp 
		     	 END) AS "start_time"
		   , MAX(CASE WHEN activity_type = 'end' THEN timestamp 
		   		 END) AS "end_time"
	FROM Activity
	GROUP BY machine_id, process_id
	) AS process_time
GROUP BY machine_id;

---