2020-08-26 easy 19-43

19. Students With Invalid Departments

Table: Departments

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| id | int |

| name | varchar |

+---------------+---------+

id is the primary key of this table.

The table has information about the id of each department of a university.

Table: Students

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| id | int |

| name | varchar |

| department_id | int |

+---------------+---------+

id is the primary key of this table.

The table has information about the id of each student at a university and the id of the department he/she studies at.

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

SELECT a.id, a.name

FROM students AS a

LEFT JOIN departments AS b

ON a.department_id = b.id

WHERE b.id IS NULL

;

20. Average Selling Price

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.

SELECT a.product_id,

ROUND(IFNULL(SUM(a.units*b.price)/SUM(a.units), 0), 2) AS average_price

FROM unitssold AS a

LEFT JOIN prices AS b

ON a.product_id = b.product_id AND a.purchase_date BETWEEN b.start_date AND end_date

GROUP BY a.product_id

;

21. Actors and Directors Who Cooperated At Least Three Times

Table: ActorDirector

+-------------+---------+

| Column Name | Type |

+-------------+---------+

| actor_id | int |

| director_id | int |

| timestamp | int |

+-------------+---------+

timestamp is the primary key column for this table.

Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor have cooperated with the director at least 3 times.

SELECT actor_id, director_id

FROM

(SELECT actor_id, director_id, COUNT(timestamp) AS num

FROM actordirector

GROUP BY actor_id, director_id) AS a

WHERE num >= 3

;

22. Customer Placing the Largest Number of Orders

Query the customer_number from the orders table for the customer who has placed the largest number of orders.

It is guaranteed that exactly one customer will have placed more orders than any other customer.

The orders table is defined as follows:

| Column | Type |

|-------------------|-----------|

| order_number (PK) | int |

| customer_number | int |

| order_date | date |

| required_date | date |

| shipped_date | date |

| status | char(15) |

| comment | char(200) |

SELECT customer_number

FROM

(SELECT customer_number, COUNT(order_number) AS num

FROM orders

GROUP BY customer_number

ORDER BY num desc

LIMIT 1) AS a

;

如果这道题不能保证最后的结果只有一个customer呢？

solution1:

SELECT customer_number

FROM

(SELECT customer_number, DENSE_RANK() OVER(ORDER BY num DESC) AS rk

FROM

(SELECT customer_number, COUNT(order_number) AS num

FROM orders

GROUP BY customer_number) AS a ) AS a

WHERE rk = 1

;

solution 2:

SELECT customer_number

FROM

(SELECT customer_number, COUNT(order_number) AS num

FROM orders

GROUP BY customer_number) AS a

WHERE num = (SELECT MAX(num) FROM (SELECT customer_number, COUNT(order_number) AS num

FROM orders

GROUP BY customer_number

) AS a)

;

一般这种情况适合用dense_rank，不适合用having去进行把聚合函数放入子查询。

23. Product Sales Analysis I

Table:Sales

+-------------+-------+

| Column Name | Type |

+-------------+-------+

| sale_id | int |

| product_id | int |

| year | int |

| quantity | int |

| price | int |

+-------------+-------+

(sale_id, year) is the primary key of this table.product_id is a foreign key toProducttable.Note that the price is per unit.

Table:Product

+--------------+---------+

| Column Name | Type |

+--------------+---------+

| product_id | int |

| product_name | varchar |

+--------------+---------+

product_id is the primary key of this table。

Write an SQL query that reports all product names of the products in the Sales table along with their selling year and price.

SELECT product_name, year, price

FROM sales AS a

LEFT JOIN product AS b

ON a.product_id = b.product_id

;

此题的result当中的price不是total price，直接选就可以。

24. Shortest Distance in a Line

Table point holds the x coordinate of some points on x-axis in a plane, which are all integers.

Write a query to find the shortest distance between two points in these points.

| x |

|-----|

| -1 |

| 0 |

| 2 |

Note: Every point is unique, which means there is no duplicates in table point.

SELECT MIN(distance) AS shortest

FROM

(SELECT ABS(a.x-b.x) AS distance

FROM point AS a, point AS b

HAVING distance <> 0) AS a

;

SELECT MIN(ABS(a.x-b.x)) AS shortest

FROM point AS a, point AS b

WHERE ABS(a.x-b.x) <> 0

;

这个方案中，不能用having shortest <> 0，因为shortest已经选出了最小值，再having最小值不等于0，是无效的。

SELECT MIN(ABS(a.x-b.x)) AS shortest

FROM point AS a

INNER JOIN point AS b

ON a.x <> b.x

;

25. Sales Analysis III

Table:Product

+--------------+---------+

| Column Name | Type |

+--------------+---------+

| product_id | int |

| product_name | varchar |

| unit_price | int |

+--------------+---------+

product_id is the primary key of this table.

Table:Sales

+-------------+---------+

| Column Name | Type |

+-------------+---------+

| seller_id | int |

| product_id | int |

| buyer_id | int |

| sale_date | date |

| quantity | int |

| price | int |

+------ ------+---------+

This table has no primary key, it can have repeated rows.

product_id is a foreign key to Product table.

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

SELECT a.product_id, b.product_name

FROM

(SELECT DISTINCT product_id

FROM sales

WHERE product_id NOT IN (SELECT product_id FROM sales WHERE sale_date NOT BETWEEN '2019-01-01' AND '2019-04-01')) AS a

LEFT JOIN product AS b

ON a.product_id = b.product_id

;

这个题是一个很大的陷阱，如果题中出现了让你选择某一个column，这个column只能出现在一个类别里，不能出现在另一个类别里，那么这个时候就要小心，不能直接选它在一个类别里，而是要让它不在另一个类别里，剩下的自然就是在你想选的那个类别里了。还有就是，distinct最好放在join之前。

此题还可以跟sales analysis II做类比去做。

26. Biggest Single Number

Table my_numbers contains many numbers in column num including duplicated ones.

Can you write a SQL query to find the biggest number, which only appears once.

+---+

|num|

+---+

| 8 |

| 3 |

| 1 |

| 4 |

| 5 |

| 6 |

If there is no such number, just output null.

SELECT

IFNULL((SELECT num

FROM

(SELECT num, COUNT(num) AS n

FROM my_numbers

GROUP BY num

HAVING n = 1) AS a

ORDER BY num DESC

LIMIT 1), NULL) AS num

;

SELECT

(SELECT num

FROM

(SELECT num, COUNT(num) AS n

FROM my_numbers

GROUP BY num

HAVING n = 1) AS a

ORDER BY num DESC

LIMIT 1) AS num

;

如果没有符合条件的话，正常返回是什么也没有。但是如果想表达“如果什么都没有则返回NULL”，那么有两种方式：一种是加IF NULL然后SELECT，一种是只加SELECT，这两种都可以。

27. Big Countries

There is a table World

+-----------------+------------+------------+--------------+---------------+

+-----------------+------------+------------+--------------+---------------+

| Afghanistan | Asia | 652230 | 25500100 | 20343000 |

| Albania | Europe | 28748 | 2831741 | 12960000 |

| Algeria | Africa | 2381741 | 37100000 | 188681000 |

| Andorra | Europe | 468 | 78115 | 3712000 |

| Angola | Africa | 1246700 | 20609294 | 100990000 |

+-----------------+------------+------------+--------------+---------------+

A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.

Write a SQL solution to output big countries' name, population and area.

SELECT name, area, population

FROM world

WHERE area >= 3000000 OR population >= 25000000

;

28. Group Sold Products By The Date

Table Activities:

+-------------+---------+

| Column Name | Type |

+-------------+---------+

| sell_date | date |

| product | varchar |

+-------------+---------+

There is no primary key for this table, it may contains duplicates.

Each row of this table contains the product name and the date it was sold in a market.

Write an SQL query to find for each date, the number of distinct 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) AS products

FROM

(SELECT *

FROM activities

ORDER BY product) AS a

GROUP BY sell_date

;

当要把group_concat里面的内容排序时，我们应该先把raw data进行order，然后再group_concat，它自然就排好了。另外，group_concat不支持window function，至少在mysql是这样。

29. Find Customer Referee

Given a table customer holding customers information and the referee.

+------+------+-----------+

| id | name | referee_id|

+------+------+-----------+

| 1 | Will | NULL |

| 2 | Jane | NULL |

| 3 | Alex | 2 |

| 4 | Bill | NULL |

| 5 | Zack | 1 |

| 6 | Mark | 2 |

+------+------+-----------+

Write a query to return the list of customers NOT referred by the person with id '2'.

SELECT DISTINCT name

FROM customer

WHERE referee_id <> 2 OR referee_id IS NULL

;

30. Game Play Analysis II

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 of this table.

This table shows the activity of players of some game.

Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.

Write a SQL query that reports the device that is first logged in for each player.

SELECT a.player_id, b.device_id

FROM

(SELECT player_id, MIN(event_date) AS first_login_date

FROM activity

GROUP BY player_id) AS a

INNER JOIN activity AS b

ON a.player_id = b.player_id AND a.first_login_date = b.event_date

;

31. Replace Employee ID With The Unique Identifier

Table: Employees

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| id | int |

| name | varchar |

+---------------+---------+

id is the primary key for this table.

Each row of this table contains the id and the name of an employee in a company.

Table: EmployeeUNI

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| id | int |

| unique_id | int |

+---------------+---------+

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

Each row of this table contains the id and the corresponding unique id of an employee in the company.

Write an SQL query to show the unique ID of each user, If a user doesn't have a unique ID replace just show null. Return the result table in any order.

SELECT IFNULL(DISTINCT b.unique_id, NULL) AS unique_id, name

FROM employees AS a

LEFT JOIN employeeuni AS b

ON a.id = b.id

;

32. Not Boring Movies

X city opened a new cinema, many people would like to go to this cinema. The cinema also gives out a poster indicating the movies’ ratings and descriptions.

Please write a SQL query to output movies with an odd numbered ID and a description that is not 'boring'. Order the result by rating.

For example, table cinema:

+---------+-----------+--------------+-----------+

+---------+-----------+--------------+-----------+

| 1 | War | great 3D | 8.9 |

| 2 | Science | fiction | 8.5 |

| 3 | irish | boring | 6.2 |

| 4 | Ice song | Fantacy | 8.6 |

| 5 | House card| Interesting| 9.1 |

+---------+-----------+--------------+-----------+

SELECT id, movie, description, rating

FROM cinema

WHERE MOD(id, 2) = 1 AND description NOT LIKE 'boring'

ORDER BY rating DESC

;

33. Classes More Than 5 Students

There is a table courses with columns: student and class

Please list out all classes which have more than or equal to 5 students.

Note: The students should not be counted duplicate in each course.

SELECT class

FROM

(SELECT class, COUNT(DISTINCT student) AS num

FROM courses

GROUP BY class

HAVING num >= 5) AS a

;

34. Fix Product Name Format (跳过，不考该题型)

Table:Sales

+--------------+---------+

| Column Name | Type |

+--------------+---------+

| sale_id | int |

| product_name | varchar |

| sale_date | date |

+--------------+---------+

sale_id is the primary key for this table.

Since table Sales was filled manually in the year 2000, product_name may contain leading and/or trailing white spaces, also they are case-insensitive.

Write an SQL query to report:

product_name in lowercase without leading or trailing white spaces,

sale_date in the format('YYYY-MM'),

total the number of times the product was sold in this month.

Return the result table ordered by product_name in ascending order, in case of a tie order it by sale_date in ascending order.

35. Patients With a Condition

Table:Patients

+--------------+---------+

| Column Name | Type |

+--------------+---------+

| patient_id | int |

| patient_name | varchar |

| conditions | varchar |

+--------------+---------+

patient_id is the primary key for this table.

'conditions' contains 0 or more code separated by spaces.

This table contains information of the patients in the hospital.

Write an SQL query to report the patient_id, patient_name all conditions of patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.

SELECT patient_id, patient_name, conditions

FROM patients

WHERE conditions LIKE '%DIAB1%'

;

36. Find Users With Valid E-Mails (不考这个题型，跳过)

Table: Users

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| user_id | int |

| name | varchar |

| mail | varchar |

+---------------+---------+

user_id is the primary key for this table.

This table contains information of the users signed up in a website. 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'.

37. Customer Order Frequency

Table: Customers

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| customer_id | int |

| name | varchar |

| country | varchar |

+---------------+---------+

customer_id is the primary key for this table.

This table contains information of the customers in the company.

Table: Product

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| product_id | int |

| description | varchar |

| price | int |

+---------------+---------+

product_id is the primary key for this table.

This table contains information of the products in the company.

price is the product cost.

Table: Orders

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| order_id | int |

| customer_id | int |

| product_id | int |

| order_date | date |

| quantity | int |

+---------------+---------+

order_id is the primary key for this table.

This table contains information on customer orders.

customer_id is the id of the customer who bought "quantity" products with id "product_id".

Order_date is the date in format ('YYYY-MM-DD') when the order was shipped.

Write an SQL query to report the customer_id and customer_name of customers who have spent at least $100 in each month of June and July 2020.

WITH temp_1 AS (SELECT customer_id, MONTH(order_date) AS mon, SUM(o.quantity*p.price) AS price

FROM orders AS o

LEFT JOIN product AS p

ON o.product_id = p.product_id

WHERE YEAR(o.order_date) = 2020 and MONTH(order_date) IN (6,7)

GROUP BY customer_id, MONTH(order_date)

HAVING price >= 100),

temp_2 AS (SELECT DISTINCT customer_id FROM temp_1 WHERE customer_id IN (SELECT customer_id FROM (SELECT customer_id, COUNT(mon) FROM temp_1 GROUP BY customer_id HAVING COUNT(mon) = 2) AS a))

SELECT c.customer_id, c.name FROM temp_2 LEFT JOIN customers AS c ON temp_2.customer_id = c.customer_id

;

这道题的逻辑其实并不复杂，但是涉及到的子查询会比较多，容易搞错，要很细心。耗时也是蛮长的。

38. Friendly Movies Streamed Last Month

Table: TVProgram

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| program_date | date |

| content_id | int |

| channel | varchar |

+---------------+---------+

(program_date, content_id) is the primary key for this table.

This table contains information of the programs on the TV.

content_id is the id of the program in some channel on the TV.

Table: Content

+------------------+---------+

| Column Name | Type |

+------------------+---------+

| content_id | varchar |

| title | varchar |

| Kids_content | enum |

| content_type | varchar |

+------------------+---------+

content_id is the primary key for this table.

Kids_content is an enum that takes one of the values ('Y', 'N') where:

'Y' means is content for kids otherwise 'N' is not content for kids.

content_type is the category of the content as movies, series, etc.

Write an SQL query to report the distinct titles of the kid-friendly movies streamed in June 2020.

SELECT DISTINCT title AS title

FROM content AS c

LEFT JOIN tvprogram AS t

ON c.content_id = t.content_id

WHERE MONTH(program_date) = 6 AND YEAR(program_date) = 2020 AND content_type = 'movies' AND kids_content = 'Y'

;

39. Create a Session Bar Chart

Table: Sessions

+---------------------+---------+

| Column Name | Type |

+---------------------+---------+

| session_id | int |

| duration | int |

+---------------------+---------+

session_id is the primary key for this table.

duration is the time in seconds that a user has visited the application.

You want to know how long a user visits your application. You decided to create bins of "[0-5>", "[5-10>", "[10-15>" and "15 minutes or more" and count the number of sessions on it.

Write an SQL query to report the (bin, total) in any order.

这道题需要注意不能直接用case when，因为case when如果“when”没有合适的条件的话，不会显示这一条件的。如果想把分组条件全部显示出来，则必须要另想办法。

SELECT "[0-5>" AS bin, SUM(CASE WHEN duration/60 BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS total FROM sessions

UNION

SELECT "[5-10>" AS bin, SUM(CASE WHEN duration/60 BETWEEN 5 AND 10 THEN 1 ELSE 0 END) AS total FROM sessions

UNION

SELECT "[10-15>" AS bin, SUM(CASE WHEN duration/60 BETWEEN 10 AND 15 THEN 1 ELSE 0 END) AS total FROM sessions

UNION

SELECT "15 or more" AS bin, SUM(CASE WHEN duration/60 >= 15 THEN 1 ELSE 0 END) AS total FROM sessions

;

40. Top Travellers

Table: Users

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| id | int |

| name | varchar |

+---------------+---------+

id is the primary key for this table.

name is the name of the user.

Table: Rides

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| id | int |

| user_id | int |

| distance | int |

+---------------+---------+

id is the primary key for this table.

user_id is the id of the user who travelled the distance "distance".

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

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

SELECT u.name, IFNULL(travelled_distance, 0) AS travelled_distance

FROM

(SELECT user_id, SUM(distance) AS travelled_distance

FROM rides

GROUP BY user_id) AS a

RIGHT JOIN users AS u

ON a.user_id = u.id

ORDER BY travelled_distance DESC, name

;

这个题要注意的是，join的时候一定要确认好哪个表是base表；还有就是join以后，如果要求显示每个user的结果，说明即便结果是null，也要显示出来0（或者说符合实际意义），注意ifnull的使用。

41. Ads Performance

Table: Ads

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| ad_id | int |

| user_id | int |

| action | enum |

+---------------+---------+

(ad_id, user_id) is the primary key for this table.

Each row of this table contains the ID of an Ad, the ID of a user and the action taken by this user regarding this Ad.

The action column is an ENUM type of ('Clicked', 'Viewed', 'Ignored').

A company is running Ads and wants to calculate the performance of each Ad.

Performance of the Ad is measured using Click-Through Rate (CTR) where:

Write an SQL query to find the ctr of each Ad.

Round ctrto 2 decimal points. Order the result table by ctr in descending order and by ad_id in ascending order in case of a tie.

SELECT ad_id, ROUND(IFNULL(SUM(clicked)*100/(SUM(clicked)+SUM(viewed)), 0), 2) AS ctr

FROM

(SELECT ad_id,

CASE WHEN action = 'clicked' THEN 1 ELSE 0 END AS clicked,

CASE WHEN action = 'viewed' THEN 1 ELSE 0 END AS viewed

FROM ads) AS a

GROUP BY ad_id

ORDER BY ctr DESC, ad_id

;

42. Weather Type in Each Country

Table: Countries

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| country_id | int |

| country_name | varchar |

+---------------+---------+

country_id is the primary key for this table.

Each row of this table contains the ID and the name of one country.

Table: Weather

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| country_id | int |

| weather_state | varchar |

| day | date |

+---------------+---------+

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

Each row of this table indicates the weather state in a country for one day.

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 25 and Warm otherwise.

SELECT b.country_name, a.weather_type

FROM

(SELECT country_id,

CASE WHEN AVG(weather_state) <= 15 THEN 'Cold'

WHEN AVG(weather_state) >= 25 THEN 'Hot'

ELSE 'Warm'

END AS weather_type

FROM weather

WHERE MONTH(day) = 11 AND YEAR(day) = 2019

GROUP BY country_id) AS a

LEFT JOIN countries AS b

ON a.country_id = b.country_id

;

注意，在应试的情况下，每个column里面输入的内容最好还是注意大小写，不然怕机器过不了。

43. Students and Examinations

Table: Students

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| student_id | int |

| student_name | varchar |

+---------------+---------+

student_id is the primary key for this table.

Each row of this table contains the ID and the name of one student in the school.

Table: Subjects

+--------------+---------+

| Column Name | Type |

+--------------+---------+

| subject_name | varchar |

+--------------+---------+

subject_name is the primary key for this table.

Each row of this table contains the name of one subject in the school.

Table: Examinations

+--------------+---------+

| Column Name | Type |

+--------------+---------+

| student_id | int |

| subject_name | varchar |

+--------------+---------+

There is no primary key for this table. It may contain duplicates.

Each student from the Students table takes every course from Subjects table.

Each row of this table indicates that a student with ID student_id attended the exam of subject_name.

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

Order the result table by student_id and subject_name.

WITH temp AS (SELECT student_id, student_name, subject_name FROM students, subjects)

SELECT temp.student_id, temp.student_name, temp.subject_name, IFNULL(a.attended_exams, 0) AS attended_exams

FROM

(SELECT student_id, subject_name, COUNT(subject_name) AS attended_exams

FROM examinations AS e

GROUP BY student_id, subject_name) AS a

RIGHT JOIN temp

ON a.student_id = temp.student_id AND a.subject_name = temp.subject_name

ORDER BY student_id, subject_name

;

这道题其实有点tricky的地方在于三个表之中，最后一个表是主表，但是存在缺失信息，如果是跟另两张表挨个join的话，其实中间很有可能会出错，所以最好是先把两张信息无缺失的表cross join成一张标准表，然后将最后一张表的信息算出来之后再和标准表join，最后join完select的时候要注意ifnull，最后的order by也不能忘记写。