Leetcode Sql笔记1

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

1. 可以直接用avg求比例 avg=sum( ... )/count(*)
2. 直接用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

1. 先查出所有玩家第一次登录的结果，结果中每个玩家只有一条记录。
2. 根据player_id以及datediff函数算出比第一次登陆日期打一天的数据left join左连接activity表就得到每个玩家首次登陆后连续登陆两天的唯一一条数据。
3. 用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;

1. 一个教师可能在不同部门教同一门科目，所以要用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;

1. 一个用户再同一天可能会有多条活跃数据，用distinct去重。
2. 近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';

1.  group by对于重复的数据只会保留第一行。
2. having和where的其中一个区别，where在分组之前过滤，having在分组之后过滤。

 

---