力扣SQL仅数据库（1098~1132）

1098 小众书籍

需求

编写解决方案，筛选出过去一年中订单总量 少于 10 本 的 书籍，并且 不考虑 上架距今销售 不满一个月 的书籍 。假设今天是 2019-06-23 。
返回结果表 无顺序要求 。

数据准备

Create table If Not Exists Books (book_id int, name varchar(50), available_from date)
Create table If Not Exists Orders (order_id int, book_id int, quantity int, dispatch_date date)
Truncate table Books
insert into Books (book_id, name, available_from) values ('1', 'Kalila And Demna', '2010-01-01')
insert into Books (book_id, name, available_from) values ('2', '28 Letters', '2012-05-12')
insert into Books (book_id, name, available_from) values ('3', 'The Hobbit', '2019-06-10')
insert into Books (book_id, name, available_from) values ('4', '13 Reasons Why', '2019-06-01')
insert into Books (book_id, name, available_from) values ('5', 'The Hunger Games', '2008-09-21')
Truncate table Orders
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('1', '1', '2', '2018-07-26')
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('2', '1', '1', '2018-11-05')
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('3', '3', '8', '2019-06-11')
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('4', '4', '6', '2019-06-05')
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('5', '4', '5', '2019-06-20')
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('6', '5', '9', '2009-02-02')
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('7', '5', '8', '2010-04-13')

代码实现

with b as (select * from books where available_from <'2019-05-23')
, o as (select * from orders where dispatch_date > '2018-06-23')
,su as (select b.book_id,name,sum(ifnull(quantity,0))suu from  b left join o on o.book_id=b.book_id
group by  b.book_id,name)
select  book_id,name from su where suu<10;

1107. 每日新用户统计

需求：

编写解决方案，找出从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30 。

以 任意顺序 返回结果表

数据准备：

Create table If Not Exists Traffic (user_id int, activity ENUM('login', 'logout', 'jobs', 'groups', 'homepage'), activity_date date)
Truncate table Traffic
insert into Traffic (user_id, activity, activity_date) values ('1', 'login', '2019-05-01')
insert into Traffic (user_id, activity, activity_date) values ('1', 'homepage', '2019-05-01')
insert into Traffic (user_id, activity, activity_date) values ('1', 'logout', '2019-05-01')
insert into Traffic (user_id, activity, activity_date) values ('2', 'login', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('2', 'logout', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('3', 'login', '2019-01-01')
insert into Traffic (user_id, activity, activity_date) values ('3', 'jobs', '2019-01-01')
insert into Traffic (user_id, activity, activity_date) values ('3', 'logout', '2019-01-01')
insert into Traffic (user_id, activity, activity_date) values ('4', 'login', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('4', 'groups', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('4', 'logout', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('5', 'login', '2019-03-01')
insert into Traffic (user_id, activity, activity_date) values ('5', 'logout', '2019-03-01')
insert into Traffic (user_id, activity, activity_date) values ('5', 'login', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('5', 'logout', '2019-06-21')

代码实现：

#select date_sub('2019-06-30',interval 90 day);
with t1 as (select distinct user_id,min(activity_date) mindate from traffic where activity='login'
    group by user_id having mindate>=(date_sub('2019-06-30',interval 90 day)))
select mindate login_date,count(user_id) user_count from t1 group by login_date;

1112. 每位学生的最高成绩

需求：

编写解决方案，找出每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。

数据准备：

Create table If Not Exists Enrollments (student_id int, course_id int, grade int)
Truncate table Enrollments
insert into Enrollments (student_id, course_id, grade) values ('2', '2', '95')
insert into Enrollments (student_id, course_id, grade) values ('2', '3', '95')
insert into Enrollments (student_id, course_id, grade) values ('1', '1', '90')
insert into Enrollments (student_id, course_id, grade) values ('1', '2', '99')
insert into Enrollments (student_id, course_id, grade) values ('3', '1', '80')
insert into Enrollments (student_id, course_id, grade) values ('3', '2', '75')
insert into Enrollments (student_id, course_id, grade) values ('3', '3', '82')

代码实现：

select  * from enrollments;
with t1 as (select *,row_number() over(partition by student_id order by grade desc ,course_id) ran from enrollments)
select student_id,course_id,grade from t1 where ran=1;

1113. 报告的记录

需求：

编写解决方案，针对每个举报原因统计昨天的举报帖子数量。假设今天是 2019-07-05 。

返回结果表 无顺序要求 

数据准备：

Create table If Not Exists Actions (user_id int, post_id int, action_date date, action ENUM('view', 'like', 'reaction', 'comment', 'report', 'share'), extra varchar(10))
Truncate table Actions
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'like', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'share', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '4', '2019-07-04', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '4', '2019-07-04', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('4', '3', '2019-07-02', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('4', '3', '2019-07-02', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-04', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-04', 'report', 'racism')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-04', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-04', 'report', 'racism')

代码实现：

#select date_sub('2019-07-05',interval 1 day);
with t1 as (select distinct post_id,extra from actions where action_date=date_sub('2019-07-05',interval 1 day)  and extra is not null  and action  in ('report'))
select extra report_reason,count(post_id) report_count from t1 group by extra;

1126. 查询活跃业务

需求：

平均活动 是指有特定 event_type 的具有该事件的所有公司的 occurrences 的均值。

活跃业务 是指具有 多个 event_type 的业务，它们的 occurrences 严格大于 该事件的平均活动次数。

写一个解决方案，找到所有 活跃业务。

以 任意顺序 返回结果表。

数据准备：

Create table If Not Exists Events (business_id int, event_type varchar(10), occurrences int)
Truncate table Events
insert into Events (business_id, event_type, occurrences) values ('1', 'reviews', '7')
insert into Events (business_id, event_type, occurrences) values ('3', 'reviews', '3')
insert into Events (business_id, event_type, occurrences) values ('1', 'ads', '11')
insert into Events (business_id, event_type, occurrences) values ('2', 'ads', '7')
insert into Events (business_id, event_type, occurrences) values ('3', 'ads', '6')
insert into Events (business_id, event_type, occurrences) values ('1', 'page views', '3')
insert into Events (business_id, event_type, occurrences) values ('2', 'page views', '12')

代码实现：

with t1 as (select *,avg(occurrences)over(partition by event_type)avgg from events)
select business_id from t1 where occurrences>avgg group by business_id having count(event_type)>=2;

1127. 用户购买平台

需求：

编写解决方案找出每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。

以 任意顺序 返回结果表。

数据准备：

Create table If Not Exists Spending (user_id int, spend_date date, platform ENUM('desktop', 'mobile'), amount int)
Truncate table Spending
insert into Spending (user_id, spend_date, platform, amount) values ('1', '2019-07-01', 'mobile', '100')
insert into Spending (user_id, spend_date, platform, amount) values ('1', '2019-07-01', 'desktop', '100')
insert into Spending (user_id, spend_date, platform, amount) values ('2', '2019-07-01', 'mobile', '100')
insert into Spending (user_id, spend_date, platform, amount) values ('2', '2019-07-02', 'mobile', '100')
insert into Spending (user_id, spend_date, platform, amount) values ('3', '2019-07-01', 'desktop', '100')
insert into Spending (user_id, spend_date, platform, amount) values ('3', '2019-07-02', 'desktop', '100')

代码实现：

with t1 as (
    select spend_date,user_id,platform, sum(amount)sum1
    from spending group by spend_date,user_id,platform)
# t1排除掉一天中同一个用户购买相同的物品多次的情况
#    select * from t1;
,t2 as (
    select *,count(user_id) over(partition by spend_date,user_id) con
    from t1)
#  t2统计 去group by 后的数据,数据中只会出现同一个用户一天最多两次的情况，筛选出用户一天中有两条记录的情况，说明此时的用户购买了两类产品
#    select * from t2;
,t3 as (
    select spend_date,sum(sum1) total_amount ,count(distinct user_id) total_users,
           case when con=1 then platform when con=2 then 'both' end platform1
    from t2 group by spend_date,platform1)
#  t3统计 每天，每个商品的金额总数及不同用户数（排除掉both中用户有2的情况），此时已有both作为商品类别
#    select * from t3;
,t4 as (
# select * from (select distinct spend_date from spending)a1 join (select distinct platform1 platform from t3)a2   会报错，不知道为什么
    select spend_date, 'mobile' as platform from spending
    union
    select spend_date, 'desktop' as platform from spending
    union
    select spend_date, 'both' as platform from spending
)
#   t5作为一个全面表
#    select * from t5;
    select
        t4.spend_date, t4.platform ,
        coalesce(t3.total_amount,t3.total_amount,0) total_amount ,
        coalesce(t3.total_users,t3.total_users,0) total_users
    from t4 left join t3 on t4.spend_date=t3.spend_date and t4.platform=t3.platform1;

1132. 报告的记录2

需求：

编写解决方案，统计在被报告为垃圾广告的帖子中，被移除的帖子的每日平均占比，四舍五入到小数点后 2 位。

数据准备：

Create table If Not Exists Actions (user_id int, post_id int, action_date date, action ENUM('view', 'like', 'reaction', 'comment', 'report', 'share'), extra varchar(10))
create table if not exists Removals (post_id int, remove_date date)
Truncate table Actions
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'like', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'share', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '2', '2019-07-04', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '2', '2019-07-04', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('4', '3', '2019-07-02', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('4', '3', '2019-07-02', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-03', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-03', 'report', 'racism')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-03', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-03', 'report', 'racism')
Truncate table Removals
insert into Removals (post_id, remove_date) values ('2', '2019-07-20')
insert into Removals (post_id, remove_date) values ('3', '2019-07-18')

代码实现：

with t1 as (select post_id po1,action_date,extra from actions where extra='spam')
,t2 as (select * from t1 left join removals on t1.po1=Removals.post_id)
,t3 as (select action_date,count(distinct post_id)/count(distinct po1) con from t2 group by action_date)
select round((sum(con)/count(con))*100,2) average_daily_percent  from t3
;