[SQL] Leetcode easy 문제 뿌시기

21/10/26 9:11PM - 11:11PM 2시간동안 최대한 풀기

 

1. #1084. Sales Analysis III

Product: product_id | product_name | unit_price

Sales: seller_id | product_id | buyer_id | sale_date | quantity | price

Write an SQL query that reports the products that were only sold in the spring of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive. Return the result table in any order.

<My answer> SELECT product_id,         product_name FROM Sales LEFT JOIN Product  USING(product_id) GROUP BY (product_id) HAVING SUM(IF(sale_date BETWEEN '2019-01-01' AND '2019-03-31',1,0))>0 AND             SUM(IF(sale_date <'2019-01-01' OR sale_date >'2019-03-31',1,0))=0

SELECT product_id, product_name FROM Sales  JOIN Product  Using(product_id) GROUP BY product_id HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'

*** The description of Sales table: This table has no primary key, it can have 'repeated rows'. Product_id is a foreign key to the Product table. Each row of this table contains some information about one sale. 

 

테이블에 저장된 각각의 데이터를 유일하게 구분하는 키를기본키(Primary Key)라 부르며. 각 테이블 간에 연결을 만들기 위해서 테이블에서 다른 테이블의 참조되는 기본키 칼럼을외래키(Foregin Key)라 한다.
출처: https://ingorae.tistory.com/446 [잉고래의 잇다이어리]

 

**We should be using INNER JOIN in this case as in the instructions it specifies that product_id in the Sales Table is the foreign key. This tells us that there is a foreign key constraint implemented which means the product_id column in Sales can have the values in the range from the product_id column in the Product table.

<Answer2> SELECT s.product_id, product_name FROM Sales s LEFT JOIN Product p ON s.product_id = p.product_id GROUP BY s.product_id HAVING MIN(sale_date) >= CAST('2019-01-01' AS DATE) AND        MAX(sale_date) <= CAST('2019-03-31' AS DATE)

*CAST was not necessary in this case as the table description mentioned 'sale_date' is a type of DATE. 

 

2. #1082. Sales Analysis I

Product: product_id | product_name | unit_price

Sales: seller_id | product_id | buyer_id | sale_date | quantity | price

Write an SQL query that reports the best seller by total sales price, If there is a tie, report them all.

Return the result table in any order.

<My answer> SELECT a.seller_id FROM (SELECT seller_id,  SUM(price) AS total_sales  FROM Sales  GROUP BY seller_id  ORDER BY total_sales DESC  LIMIT 1) a   <Answer> SELECT seller_id FROM Sales GROUP BY seller_id HAVING SUM(price) = (SELECT SUM(price)                               FROM Sales                               GROUP BY seller_id                               ORDER BY 1 DESC                                LIMIT 1 )

** SUM(price) 는 최고값이 하나여야 하지만, 그 값을 갖는 아이디는 여러개일수 있기에 LIMIT 1을 seller_id에 대해서 하는 것은 실수! 좋은 edge case문제이다. 

 

3. #512. Game Play Analysis II

Activity: player_id | devide_id | event_date | games_played

*(player_id, event_date) is the primary key of this table. 

Write an SQL query to report the device that is first logged in for each player.

<My answer> SELECT player_id, device_id  FROM Activity  GROUP BY player_id HAVING MIN(event_date) <Correct answer> SELECT player_id,        device_id         FROM Activity        WHERE (player_id, event_date) IN (SELECT player_id, MIN(event_date)                                                     FROM Activity                                                     GROUP BY player_id) <Correct answer2> SELECT DISTINCT player_id,                          FIRST_VALUE(device_id) OVER (PARTITION BY player_id ORDER BY event_date) device_id FROM activity

4. #1050. Actors and Directors Who Cooperated At Least Three Times

ActorDirector: actor_id | director_id | timestamp

Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor has cooperated with the director at least three times. Return the result table in any order.

SELECT actor_id, director_id FROM ActorDirector GROUP BY 1, 2 HAVING COUNT(*) >= 3

5. #1075. Project Employees I

Project: project_id | employee_id

(project_id, employee_id) is the primary key of this table

employee_id is a foreign key to Employee table. 

Employee: employee_id | name | experience_years

employee_id is the primary key of this table. 

 

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

SELECT project_id,         ROUND(AVG(experience_years),2) AS average_years FROM Project JOIN Employee USING(employee_id) GROUP BY (project_id)

6. #1076. Project Employees II

Project: project_id | employee_id

(project_id, employee_id) is the primary key of this table

employee_id is a foreign key to Employee table. 

Employee: employee_id | name | experience_years

employee_id is the primary key of this table. 

Write an SQL query that reports all the projects that have the most employees. Return the result table in any order.

SELECT project_id FROM Project GROUP BY project_id HAVING COUNT(employee_id) = (SELECT COUNT(employee_id) FROM Project                                             GROUP BY project_id                                             ORDER BY 1 DESC                                             LIMIT 1)

7. #1141. User Activity for the Past 30 Days I

Activity: user_id | session_id | activity_date | activity_type

There is no primary key for this table (it may duplicate rows). 

The activity_type column is an ENUM of type

 

Write an SQL query 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.

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_user FROM Activity WHERE activity_date BETWEEN SUBDATE('2019-07-27',29) AND '2019-07-27' GROUP BY activity_date HAVING COUNT(activity_type) >=1

8. #1148. Article Views I

Views: article_id | author_id | viewer_id | view_date

There is no primary key for this table.

Write an SQL query to find all the authors that viewed at least one of their own articles. Return the result table sorted by id in ascending order.

SELECT DISTINCT author_id AS id FROM Views WHERE author_id = viewer_id ORDER BY author_id ASC

9. #1173. Immediate Food Delivery I

Delivery: delivery_id | customer_id | order_date | customer_pref_delivery_date

delivery_id is the primary key of this table. 

 

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.

Write an SQL query to find the percentage of immediate orders in the table, rounded to 2 decimal places.

SELECT ROUND(AVG(order_date = customer_pref_delivery_date)*100,2) AS immediate_percentage FROM Delivery

10. #1280. Students and Examinations

Students: student_id | student_name

Subjects: subject_name (varchar)

Examinations: student_id | subject_name

 

Write an SQL query to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

SELECT student.student_id,           student.student_name,           subject.subject_name,           COUNT(exam.subject_name) AS attended_exams FROM Students student JOIN Subjects subject LEFT JOIN Examinations exam ON student.student_id = exam.student_id AND subject.subject_name = exam.subject_name GROUP BY student.student_id, subject.subject_name ORDER BY student_id, subject_name;

11. #1294. Weather Type in Each Country

Countries: country_id | country_name

Weather: country_id | weather_state | day

(country_id, day) is the primary key for this table. 

 

Write an SQL query to find the type of weather in each country for November 2019.

The type of weather is:

• Cold if the average weather_state is less than or equal 15,
• Hot if the average weather_state is greater than or equal to 25, and

Warm otherwise. Return result table in any order.

SELECT country_name,         CASE WHEN AVG(weather_state) <=15 THEN 'Cold'         ELSE (CASE WHEN AVG(weather_state) >=25 THEN 'Hot' ELSE 'Warm' END) END AS weather_type FROM Weather LEFT JOIN Countries USING(country_id) WHERE LEFT(day,7) = '2019-11' GROUP BY (country_name)

12. #1327. List the Products Ordered in a Period

Products: product_id | product_name | product_category

Orders: product_id | order_date | unit

There is no primary  key for Orders table. 

 

Write an SQL query to get the names of products that have at least 100 units ordered in February 2020 and their amount. Return result table in any order.

SELECT product_name,         SUM(unit) AS unit        FROM Orders        LEFT JOIN Products USING(product_id)        WHERE LEFT(order_date,7)='2020-02'        GROUP BY product_name        HAVING SUM(unit) >= 100

**LEFT JOIN is not a good option since we do not want any product_ids that are not sold (not in the orders table), so INNER JOIN/JOIN is much better. 하지만 추후에 HAVING을 넣긴 함. 무엇이 더 좋은지는 고민이 더 필요.

 

13. #1350. Students With Invalid Departments

Departments: id | name

Students: id | name | department_id

Write an SQL query to find the id and the name of all students who are enrolled in departments that no longer exist. Return the result table in any order.

SELECT student.id,        student.name        FROM Students student        LEFT JOIN Departments department        ON student.department_id = department.id        WHERE department.name IS NULL

SELECT id, name FROM Students WHERE department_id NOT IN (     SELECT id      FROM Departments)

2021/10/27

14. #1378. Replace Employee ID With The Unique Identifier

Employees: id | name(varchar)

EmployeeUNI: id | unique_id

(id, unique_id) is the primary key for this table. 

Write an SQL query to show the unique ID of each user, If a user does not have a unique ID replace just show null.

SELECT unique_id, name FROM Employees LEFT JOIN EmployeeUNI USING(id)

15. #1407. Top Travellers

Users: id | name(varchar)

Rides: id | user_id | distance

Write an SQL query to report the distance traveled by each user.

Return the result table ordered by travelled_distance in descending order, if two or more users traveled the same distance, order them by their name in ascending order.

SELECT users.name,         CASE WHEN ride.distance IS NULL THEN 0 ELSE SUM(ride.distance) END AS travelled_distance FROM Users users LEFT JOIN Rides ride ON users.id = ride.user_id GROUP BY ride.user_id ORDER BY travelled_distance DESC, name ASC

*Primary key와 Foreign key의 차이: Foreign key는 relation지을때 사용할 수 있지만 unique하지 않을 수 있다는 것을 염두해두자.

** group by에서 user_name으로 안하는 이유는 같은 이름인 사람이 두 명이상 있을 수 있기 때문

 

16. #1421. NPV Queries

NPV: id | year | npv => (id, year) is the primary => id can have multiple rows by years

Queries: id | year (id, year) is the primary key => id can have multiple rows by years

 

Write an SQL query to find the npv of each query of the Queries table. Return the result table in any order.

SELECT queries.id,         queries.year,        (CASE WHEN npv.npv IS NULL THEN 0 ELSE npv.npv END) AS npv FROM Queries queries LEFT JOIN NPV npv ON queries.id = npv.id AND queries.year = npv.year

select q.id, q.year, coalesce(n.npv, 0) as npv from npv n right join queries q on n.id = q.id and n.year = q.year

*COALESCE: Occasionally, you will end up with a dataset that has some nulls that you'd prefer to contain actual values. This happens frequently in numerical data (displaying nulls as 0 is often preferable), and when performing outer joins that result in some unmatched rows. In cases like this, you can use COALESCE to replace the null values:

SELECT incidnt_num, descript, COALESCE(descript, 'No Description')

FROM tutorial.sf_crime_incidents_cleandate ORDER BY descript DESC

 

17. #1484. Group Sold Products By The Date

Activities: sell_date | product(varchar)

There is no primary key for this table (maybe containing duplicates).

Write an SQL query to find for each date the number of different products sold and their names.

The sold products names for each date should be sorted lexicographically. Return the result table ordered by sell_date.

SELECT sell_date,         COUNT(DISTINCT product) AS num_sold,         GROUP_CONCAT(DISTINCT product ORDER BY product) AS products FROM Activities GROUP BY sell_date

*GROUP_CONCAT: group별로 row 값들을 하나로 축약 group_concat 기본 구분자는 콤마. 

컴마대신 |로 구분해서 나옴. 

MySQL에서 group by로 구분자를 이용하여 문자열을 합칠땐 GROUP_CONCAT을 이용한다.

1. 기본: GROUP_CONCAT을(필드명)
2. 임의의 구분자 지정: GROUP_CONCAT을(필드명 SEPARATOR '구분자')
3. 중복제거: GROUP_CONCAT을(DISTINCT 필드명)
4. 문자열기준으로 정렬 : GROUP_CONCAT을(필드명 ORDER BY 필드명)

https://bkjeon1614.tistory.com/88

[mysql] 서로 다른 결과를 한줄로 합쳐서 보여줘야 할 때(GROUP_CONCAT)

*CONCAT: You can combine strings from several columns together (and with hard-coded values) using CONCAT. Simply order the values you want to concatenate and separate them with commas. If you want to hard-code values, enclose them in single quotes. 

SELECT incidnt_num, day_of_week, LEFT(date, 10) AS cleaned_date,

          CONCAT(day_of_week, ', ', LEFT(date, 10)) AS day_and_date

FROM tutorial.sf_crime_incidents_2014_01

*DISTINCT를 하였을 뿐 아니라, ORDER BY까지 잊지 않고 챙겨야함! (output table에 알파벳순서로 나열되어있음)

https://mode.com/sql-tutorial/sql-string-functions-for-cleaning/#concat

Using SQL String Functions to Clean Data | Advanced SQL - Mode

18. #1495. Friendly Movies Streamed Last Month

TVProgram: program_date(date) | content_id(int) | channel(varchar)

Content: contend_id(varchar) | title(varchar) | kids_content(enum) | content_type(varchar)

Write an SQL query to report the distinct titles of the kid-friendly movies streamed in June 2020. Return the result table in any order.

 

SELECT DISTINCT content.title FROM TVProgram tv JOIN Content content USING(content_id) WHERE LEFT(program_date,7) = '2020-06' AND       Kids_content = 'Y'       AND content_type = 'Movies'

19. #1517. Find Users With Valid E-Mails

Users: user_id | name(varchar) | mail(varchar)

user_id is the primary key for this table. some e-mails are invalid.

Write an SQL query to find the users who have valid emails. A valid e-mail has a prefix name and a domain where:

• The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.', and/or dash '-'. The prefix name must start with a letter.

The domain is '@leetcode.com'. Return the result table in any order.

 

*LIKE: LIKE is a logical operator in SQL that allows you to match on similar values rather than exact ones.

In this example, the results from the Billboard Music Charts dataset will include rows for which "group" starts with "Snoop" and is followed by any number and selection of characters.

SELECT * FROM tutorial.billboard_top_100_year_end WHERE "group" LIKE 'Snoop%'

https://mode.com/sql-tutorial/sql-like/

SQL LIKE | Basic SQL - Mode

LIKE is case-sensitive, meaning that the above query will only capture matches that start with a capital "S" and lower-case "noop." To ignore case when you're matching values, you can use the ILIKE command. You can also use _ (a single underscore) to substitute for an individual character.

SELECT * FROM Users WHERE mail REGEXP '^[a-z][a-z0-9._-]*@leetcode.com'

letters (upper or lower case), digits, underscore '_', period '.', and/or dash '-'. starts with letter.