1174. 即时食物配送 II
配送表: Delivery
+-----------------------------+---------+ | Column Name | Type | +-----------------------------+---------+ | delivery_id | int | | customer_id | int | | order_date | date | | customer_pref_delivery_date | date | +-----------------------------+---------+ delivery_id 是该表中具有唯一值的列。 该表保存着顾客的食物配送信息,顾客在某个日期下了订单,并指定了一个期望的配送日期(和下单日期相同或者在那之后)。
如果顾客期望的配送日期和下单日期相同,则该订单称为 「即时订单」,否则称为「计划订单」。
「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。
编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。
select
round(avg(order_date=customer_pref_delivery_date)*100,2) as immediate_percentage
from (select customer_id,min(order_date)as order_date,min(customer_pref_delivery_date) as
customer_pref_delivery_date
from delivery
group by customer_id) a
- 可以直接用avg求比例 avg=sum( ... )/count(*)
- 直接用min找出最小的order_date和customer_pref_delivery_date,customer_pref_delivery_date也要min,因为如果第一天有两个订单连个期望日期的话要取最小的作为首单
550. 游戏玩法分析 IV
Table: Activity
+--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ (player_id,event_date)是此表的主键(具有唯一值的列的组合)。 这张表显示了某些游戏的玩家的活动情况。 每一行是一个玩家的记录,他在某一天使用某个设备注销之前登录并玩了很多游戏(可能是 0)。
编写解决方案,报告在首次登录的第二天再次登录的玩家的 比率,四舍五入到小数点后两位。换句话说,你需要计算从首次登录日期开始至少连续两天登录的玩家的数量,然后除以玩家总数
select round(avg(b.event_date is not null),2) as fraction
from (select
player_id, min(event_date) as first_date
from activity
group by player_id) a
left join activity b on a.player_id=b.player_id and datediff(b.event_date,a.first_date)=1
- 先查出所有玩家第一次登录的结果,结果中每个玩家只有一条记录。
- 根据player_id以及datediff函数算出比第一次登陆日期打一天的数据left join左连接activity表就得到每个玩家首次登陆后连续登陆两天的唯一一条数据。
- 用avg函数求右表中日期不为空的的数据比率即可得出答案
2356. 每位教师所教授的科目种类的数量
表: Teacher
+-------------+------+ | Column Name | Type | +-------------+------+ | teacher_id | int | | subject_id | int | | dept_id | int | +-------------+------+ 在 SQL 中,(subject_id, dept_id) 是该表的主键。 该表中的每一行都表示带有 teacher_id 的教师在系 dept_id 中教授科目 subject_id。
查询每位老师在大学里教授的科目种类的数量。
以 任意顺序 返回结果表。
select teacher_id,count(distinct subject_id) as cnt from teacher group by teacher_id;
- 一个教师可能在不同部门教同一门科目,所以要用distinct去重。
1141. 查询近30天活跃用户数
表:Activity
+---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | session_id | int | | activity_date | date | | activity_type | enum | +---------------+---------+ 该表没有包含重复数据。 activity_type 列是 ENUM(category) 类型, 从 ('open_session', 'end_session', 'scroll_down', 'send_message') 取值。 该表记录社交媒体网站的用户活动。 注意,每个会话只属于一个用户。
编写解决方案,统计截至 2019-07-27
(包含2019-07-27),近 30
天的每日活跃用户数(当天只要有一条活动记录,即为活跃用户)。
以 任意顺序 返回结果表。
select activity_date as day, count(distinct user_id) as active_users
from activity
where datediff('2019-07-27',activity_date)>=0 and datediff('2019-07-27',activity_date)<30
group by activity_date;
- 一个用户再同一天可能会有多条活跃数据,用distinct去重。
- 近30天用datediff函数与activity_date字段计算>=0,<30。
1084. 销售分析III
表: Product
+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | | unit_price | int | +--------------+---------+ product_id 是该表的主键(具有唯一值的列)。 该表的每一行显示每个产品的名称和价格。
表:Sales
+-------------+---------+ | Column Name | Type | +-------------+---------+ | seller_id | int | | product_id | int | | buyer_id | int | | sale_date | date | | quantity | int | | price | int | +------ ------+---------+ 这个表可能有重复的行。 product_id 是 Product 表的外键(reference 列)。 该表的每一行包含关于一个销售的一些信息。
编写解决方案,报告2019年春季
才售出的产品。即仅在2019-01-01
至2019-03-31
(含)之间出售的商品。
select p.product_id, p.product_name
from product p
left join sales s on p.product_id=s.product_id
group by p.product_id
having min(sale_date)>='2019-01-01' and max(sale_date)<='2019-03-31';
- group by对于重复的数据只会保留第一行。
- having和where的其中一个区别,where在分组之前过滤,having在分组之后过滤。