[MySQL] LeetCode 문제 연습 5

1174. Immediate Food Delivery II (Medium)

• Table: Delivery
  +-----------------------------+---------+
  | Column Name | Type |
  +-----------------------------+---------+
  | delivery_id | int |
  | customer_id | int |
  | order_date | date |
  | customer_pref_delivery_date | date |
  +-----------------------------+---------+
  delivery_id is the column of unique values of this table.
  The table holds information about food delivery to customers that make orders at some date and specify a preferred delivery date (on the same order date or after it).
  
  If the customer's preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled.
  
  The first order of a customer is the order with the earliest order date that the customer made. It is guaranteed that a customer has precisely one first order.
  
  Write a solution to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

제출한 쿼리 :

SELECT ROUND(AVG(order_date = customer_pref_delivery_date) * 100, 2) AS "immediate_percentage"
FROM Delivery
WHERE (customer_id, order_date) IN (
  SELECT customer_id
  		 , MIN(order_date) 
  FROM Delivery
  GROUP BY customer_id
  );

---

550. Game Play Analysis IV (Medium)

• Table: 'Activity'
  +--------------+---------+
  | Column Name | Type |
  +--------------+---------+
  | player_id | int |
  | device_id | int |
  | event_date | date |
  | games_played | int |
  +--------------+---------+
  (player_id, event_date) is the primary key (combination of columns with unique values) of this table.
  This table shows the activity of players of some games.
  Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.
  
  Write a solution to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.

제출한 쿼리 :

SELECT ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) 
										  FROM Activity), 2) AS "fraction"
FROM Activity
WHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))
  IN (SELECT player_id
  	  	  	 , MIN(event_date) AS "first_login"
  	  FROM Activity
      GROUP BY player_id
     )

---

2356. Number of Unique Subjects Taught by Each Teacher (Easy)

• Table: Teacher
  +-------------+------+
  | Column Name | Type |
  +-------------+------+
  | teacher_id | int |
  | subject_id | int |
  | dept_id | int |
  +-------------+------+
  (subject_id, dept_id) is the primary key (combinations of columns with unique values) of this table.
  Each row in this table indicates that the teacher with teacher_id teaches the subject subject_id in the department dept_id.
  
  Write a solution to calculate the number of unique subjects each teacher teaches in the university.
  
  Return the result table in any order.

제출한 쿼리 :

SELECT teacher_id,COUNT(DISTINCT subject_id) AS "cnt"
FROM teacher
GROUP BY teacher_id;

---

1141. User Activity for the Past 30 Days I (Easy)

• Table: Activity
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | user_id | int |
  | session_id | int |
  | activity_date | date |
  | activity_type | enum |
  +---------------+---------+
  This table may have duplicate rows.
  The activity_type column is an ENUM (category) of type ('open_session', 'end_session', 'scroll_down', 'send_message').
  The table shows the user activities for a social media website.
  Note that each session belongs to exactly one user.
  
  Write a solution to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on someday if they made at least one activity on that day.
  
  Return the result table in any order.

제출한 쿼리 :

SELECT activity_date AS "day"
	   , COUNT(DISTINCT user_id) AS "active_users"
FROM Activity
WHERE (activity_date > "2019-06-27" AND activity_date <= "2019-07-27")
GROUP BY activity_date;

---

1070. Product Sales Analysis III (Medium)

• 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 select the product id, year, quantity, and price for the first year of every product sold.
  
  Return the resulting table in any order.

제출한 쿼리 :

SELECT product_id
	   , `year` AS first_year
	   , quantity
	   , price
FROM sales s
WHERE (s.product_id, s.year) IN (
        SELECT product_id, MIN(`year`)
        FROM sales
        GROUP BY product_id
    );

---