1098. 小众书籍
书籍表 Books
:
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| book_id | int |
| name | varchar |
| available_from | date |
+----------------+---------+
book_id 是这个表的主键。
订单表 Orders
:
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| order_id | int |
| book_id | int |
| quantity | int |
| dispatch_date | date |
+----------------+---------+
order_id 是这个表的主键。
book_id 是 Books 表的外键。
你需要写一段 SQL 命令,筛选出过去一年中订单总量 少于10本 的 书籍 。
注意:不考虑 上架(available from)距今 不满一个月 的书籍。并且 假设今天是 2019-06-23 。
下面是样例输出结果:
Books 表:
+---------+--------------------+----------------+
| book_id | name | available_from |
+---------+--------------------+----------------+
| 1 | "Kalila And Demna" | 2010-01-01 |
| 2 | "28 Letters" | 2012-05-12 |
| 3 | "The Hobbit" | 2019-06-10 |
| 4 | "13 Reasons Why" | 2019-06-01 |
| 5 | "The Hunger Games" | 2008-09-21 |
+---------+--------------------+----------------+
Orders 表:
+----------+---------+----------+---------------+
| order_id | book_id | quantity | dispatch_date |
+----------+---------+----------+---------------+
| 1 | 1 | 2 | 2018-07-26 |
| 2 | 1 | 1 | 2018-11-05 |
| 3 | 3 | 8 | 2019-06-11 |
| 4 | 4 | 6 | 2019-06-05 |
| 5 | 4 | 5 | 2019-06-20 |
| 6 | 5 | 9 | 2009-02-02 |
| 7 | 5 | 8 | 2010-04-13 |
+----------+---------+----------+---------------+
Result 表:
+-----------+--------------------+
| book_id | name |
+-----------+--------------------+
| 1 | "Kalila And Demna" |
| 2 | "28 Letters" |
| 5 | "The Hunger Games" |
+-----------+--------------------+
提交答案:
# Write your MySQL query statement below
select b.book_id, b.name
from
(select book_id, quantity
from Orders
where dispatch_date between DATE_SUB('2019-06-23', INTERVAL 1 YEAR) and '2019-06-23'
) o
RIGHT JOIN
(select *
from Books
where DATE_ADD(available_from, INTERVAL 1 MONTH) <= '2019-06-23') b
ON o.book_id=b.book_id
group by b.book_id
having sum(IF(o.quantity, o.quantity, 0)) < 10
解析:
- 不考虑 上架(available from)距今 不满一个月 的书籍。
select *
from Books
where DATE_ADD(available_from, INTERVAL 1 MONTH) <= '2019-06-23'
- 筛选出过去一年中的订单。
select book_id, quantity
from Orders
where dispatch_date between DATE_SUB('2019-06-23', INTERVAL 1 YEAR) and '2019-06-23'
Orders
右连接Books
,因为没有订单的也需要列出来;- IF函数判断,再使用sum函数即可。
having sum(IF(o.quantity, o.quantity, 0)) < 10
1107. 每日新用户统计
Traffic
表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| activity | enum |
| activity_date | date |
+---------------+---------+
该表没有主键,它可能有重复的行。
activity 列是 ENUM 类型,可能取 ('login', 'logout', 'jobs', 'groups', 'homepage') 几个值之一。
编写一个 SQL 查询,以查询从今天起最多 90 天内,每个日期该日期首次登录的用户数。假设今天是 2019-06-30.
查询结果格式如下例所示:
Traffic 表:
+---------+----------+---------------+
| user_id | activity | activity_date |
+---------+----------+---------------+
| 1 | login | 2019-05-01 |
| 1 | homepage | 2019-05-01 |
| 1 | logout | 2019-05-01 |
| 2 | login | 2019-06-21 |
| 2 | logout | 2019-06-21 |
| 3 | login | 2019-01-01 |
| 3 | jobs | 2019-01-01 |
| 3 | logout | 2019-01-01 |
| 4 | login | 2019-06-21 |
| 4 | groups | 2019-06-21 |
| 4 | logout | 2019-06-21 |
| 5 | login | 2019-03-01 |
| 5 | logout | 2019-03-01 |
| 5 | login | 2019-06-21 |
| 5 | logout | 2019-06-21 |
+---------+----------+---------------+
Result 表:
+------------+-------------+
| login_date | user_count |
+------------+-------------+
| 2019-05-01 | 1 |
| 2019-06-21 | 2 |
+------------+-------------+
请注意,我们只关心用户数非零的日期.
ID 为 5 的用户第一次登陆于 2019-03-01,因此他不算在 2019-06-21 的的统计内。
提交答案:
# Write your MySQL query statement below
select login_date, count(*) as user_count
from (
select user_id, min(activity_date) as login_date
from Traffic
where activity='login'
group by user_id
having min(activity_date) >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)
) t
group by login_date
解析:
- 先找到每个用户(近90天登录的)的首次登录时间,然后根据
login_date
分组统计数量即可。
1112. 每位学生的最高成绩
表:Enrollments
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| course_id | int |
| grade | int |
+---------------+---------+
(student_id, course_id) 是该表的主键。
编写一个 SQL 查询,查询每位学生获得的最高成绩和它所对应的科目,若科目成绩并列,取 course_id
最小的一门。查询结果需按student_id
增序进行排序。
查询结果格式如下所示:
Enrollments 表:
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 2 | 2 | 95 |
| 2 | 3 | 95 |
| 1 | 1 | 90 |
| 1 | 2 | 99 |
| 3 | 1 | 80 |
| 3 | 2 | 75 |
| 3 | 3 | 82 |
+------------+-----------+-------+
Result 表:
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 1 | 2 | 99 |
| 2 | 2 | 95 |
| 3 | 3 | 82 |
+------------+-----------+-------+
提交答案:
# Write your MySQL query statement below
select student_id, course_id, grade
from (
select student_id, course_id, grade
, RANK() over(partition by student_id order by grade DESC, course_id) as rk
from Enrollments
group by student_id, course_id
order by student_id ASC
) t
where rk=1
解析:
- 根据
student_id
分区,order by grade DESC, course_id
按成绩和课程id排序; group by student_id, course_id
,结果表中需要(student_id, course_id, grade)
,所以我们筛选的时候要根据学生id和课程id分组。
1113. 报告的记录
动作表:Actions
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| post_id | int |
| action_date | date |
| action | enum |
| extra | varchar |
+---------------+---------+
此表没有主键,所以可能会有重复的行。
action 字段是 ENUM 类型的,包含:('view', 'like', 'reaction', 'comment', 'report', 'share')
extra 字段是可选的信息(可能为 null),其中的信息例如有:1.报告理由(a reason for report) 2.反应类型(a type of reaction)
编写一条SQL,查询每种 报告理由(report reason)在昨天的不同报告数量(post_id)。假设今天是 2019-07-05。
查询及结果的格式示例:
Actions table:
+---------+---------+-------------+--------+--------+
| user_id | post_id | action_date | action | extra |
+---------+---------+-------------+--------+--------+
| 1 | 1 | 2019-07-01 | view | null |
| 1 | 1 | 2019-07-01 | like | null |
| 1 | 1 | 2019-07-01 | share | null |
| 2 | 4 | 2019-07-04 | view | null |
| 2 | 4 | 2019-07-04 | report | spam |
| 3 | 4 | 2019-07-04 | view | null |
| 3 | 4 | 2019-07-04 | report | spam |
| 4 | 3 | 2019-07-02 | view | null |
| 4 | 3 | 2019-07-02 | report | spam |
| 5 | 2 | 2019-07-04 | view | null |
| 5 | 2 | 2019-07-04 | report | racism |
| 5 | 5 | 2019-07-04 | view | null |
| 5 | 5 | 2019-07-04 | report | racism |
+---------+---------+-------------+--------+--------+
Result table:
+---------------+--------------+
| report_reason | report_count |
+---------------+--------------+
| spam | 1 |
| racism | 2 |
+---------------+--------------+
注意,我们只关心报告数量非零的结果。
提交答案:
# Write your MySQL query statement below
select extra as report_reason, count(distinct(post_id)) as report_count
from (
select post_id, extra
from Actions
where action_date=DATE_SUB('2019-07-05', INTERVAL 1 DAY)
and action='report'
) t
group by extra
解析:
count(distinct(post_id))
注意统计不同的post_id
即可。
以上是leetcode4道sql题的解答,如有考虑错误或者解法问题,请指正。🙂