[LeetCode]1661.Average Time of~, 1251.Average Selling Price , 1141. User Activity for the Past 30 Days I

Q. 1661 Average Time of Process per Machine

Average Time of Process per Machine - LeetCode

Table: Views

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| article_id    | int     |
| author_id     | int     |
| viewer_id     | int     |
| view_date     | date    |
+---------------+---------+
There is no primary key (column with unique values) for this table, the table may have duplicate rows.
Each row of this table indicates that some viewer viewed an article (written by some author) on some date. 
Note that equal author_id and viewer_id indicate the same person.
 

Write a solution to find all the authors that viewed at least one of their own articles.

Return the result table sorted by id in ascending order.

The result format is in the following example.

 

Example 1:

Input: 
Views table:
+------------+-----------+-----------+------------+
| article_id | author_id | viewer_id | view_date  |
+------------+-----------+-----------+------------+
| 1          | 3         | 5         | 2019-08-01 |
| 1          | 3         | 6         | 2019-08-02 |
| 2          | 7         | 7         | 2019-08-01 |
| 2          | 7         | 6         | 2019-08-02 |
| 4          | 7         | 1         | 2019-07-22 |
| 3          | 4         | 4         | 2019-07-21 |
| 3          | 4         | 4         | 2019-07-21 |
+------------+-----------+-----------+------------+
Output: 
+------+
| id   |
+------+
| 4    |
| 7    |
+------+

문제를 아예 코드로 들고왔더니 ^^... 저렇게 보인다 

여하간 중요한 건 그 .. 풀이가 되는 법 :) 근데 좀 많이 어려웠다 

A. 일단 Join을 써야 하는 건 알겠는데 그 안에 또 다시 select / where 절을 넣어야 한다... 까지 파악했음

그리고 같은 테이블의 activity_type여도 a.activity_type인지 그냥 activity_type인지 구분하는 게 까다로웠다 :0

# Write your MySQL query statement below
SELECT a.machine_id,  
	  ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time
FROM Activity as a
JOIN (
  SELECT machine_id, process_id, activity_type, timestamp
  FROM activity
  WHERE activity_type = "end"
  GROUP BY machine_id, process_id) AS b
  ON a.machine_id = b.machine_id
WHERE a.activity_type = "start"
GROUP BY a.machine_id;

<Run 결과 | Submit 결과>

 

1251. Average Selling Price

https://leetcode.com/problems/average-selling-price/

Average Selling Price - LeetCode

Q.

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 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     |
+---------------+---------+
There is no primary key for this table, it may contain duplicates.
Each row of this table indicates the date, units, and product_id of each product sold.

Write an SQL query 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. The query result format is in the following example.

Example 1:

Input: 
Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+
Output: 
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
Explanation: 
Average selling price = Total Price of Product / Number of products sold.
Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96
Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96

A. 가격의 평균을 구하는 것 자체는 Average 혹은 단순 수식 계산으로 진행하면 되는데 중요한 건 purchase_date다. start/end date 조건이 있으니까 Between으로 얘를 살려줬다

SELECT p.product_id, ROUND(SUM(u.units*p.price)/SUM(u.units),2) AS average_price
FROM prices AS p
JOIN unitssold AS un
  ON p.product_id = un.product_id AND un.purchase_date
  BETWEEN p.start_date AND p.end_date #Between으로 조건을 살림
GROUP BY p.product_id

Result ! 깔꼼하게 성공 :)

 

1141. User Activity for the Past 30 Days

User Activity for the Past 30 Days I - LeetCode

일단 datediff라는 새로운 함수가 낯설었다. 분명 접하긴 했는데? 쓰지는 못하는 ...

Datediff는 시작/종료 날짜를 비교하여 DATEPART 구분자에 따라서 차이를 계산하여 결과를 정수로 반환해주는 함수다. 그러면 Datepart 구분자는 또 뭐냐? Day, Month, Year처럼 그 단위를 선정해주는 구분자를 의미한다. Datediff함수는 (Datepart, 시작일, 종료일) 형태로 사용하면 됨!

select activity_date as day, count(distinct user_id) as active_users 
from activity 
where datediff('2019-07-27', activity_date) < 30 and datediff('2019-07-27', activity_date) >= 0
Group by activity_date;

이것만 쓸 줄 안다면 어렵지 않..았을 문제. 작성 후에는 근자감이 샘솟아서 그냥 run 없이 submit해버렸다

근거 있는 자신감이었던 걸로 :)