20240102 SQL基础50题打卡

发布时间:2024年01月02日

20240102 SQL基础50题打卡

550. 游戏玩法分析 IV


Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id,event_date)是此表的主键(具有唯一值的列的组合)。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录,他在某一天使用某个设备注销之前登录并玩了很多游戏(可能是 0)。

编写解决方案,报告在首次登录的第二天再次登录的玩家的 比率四舍五入到小数点后两位。换句话说,你需要计算从首次登录日期开始至少连续两天登录的玩家的数量,然后除以玩家总数。

结果格式如下所示:

示例 1:

输入:
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出:
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
解释:
只有 ID 为 1 的玩家在第一天登录后才重新登录,所以答案是 1/3 = 0.33

题解:

select IFNULL(round(count(distinct(Result.player_id)) / count(distinct(Activity.player_id)),2),0) as fraction
from (
    select Activity.player_id as player_id
    from (
        select player_id, DATE_ADD(MIN(event_date),INTERVAL 1 DAY) as second_date
        from Activity
        group by player_id
    ) as  Expected, Activity
    where Activity.event_date = Expected.second_date and Activity.player_id = Expected.player_id
) as Result,Activity;

基本思路:

算法
首先,求出所有用户首次登录的第二天的时间。方法是查询出 Activity 表中每个用户的第一天时间,并加上 1.

将此表命名为 Expected。随后我们要从 Activity 表中查询 event_date 与 Expected.sencond_date 重叠的部分,注意此判定要限定在用户相同的前提下。这部分用户即为在首次登录后第二天也登录了的用户。

将此表命名为 Result。随后我们只需要得到 Result 表中用户的数量,以及 Activity 表中用户的数量,相除并保留两位小数即可。

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。

查询每位老师在大学里教授的科目种类的数量。

任意顺序 返回结果表。

查询结果格式示例如下。

示例 1:

输入: 
Teacher 表:
+------------+------------+---------+
| teacher_id | subject_id | dept_id |
+------------+------------+---------+
| 1          | 2          | 3       |
| 1          | 2          | 4       |
| 1          | 3          | 3       |
| 2          | 1          | 1       |
| 2          | 2          | 1       |
| 2          | 3          | 1       |
| 2          | 4          | 1       |
+------------+------------+---------+
输出:  
+------------+-----+
| teacher_id | cnt |
+------------+-----+
| 1          | 2   |
| 2          | 4   |
+------------+-----+
解释: 
教师 1:
  - 他在 3、4 系教科目 2。
  - 他在 3 系教科目 3。
教师 2:
  - 他在 1 系教科目 1。
  - 他在 1 系教科目 2。
  - 他在 1 系教科目 3。
  - 他在 1 系教科目 4。

题解:

# Write your MySQL query statement below
select teacher_id, count(distinct(subject_id)) as cnt
from Teacher
group by teacher_id;

思路:这个题很简单,知识对教师的 id 进行一个分组,然后计数科目不同的数目就可以了。

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 天的每日活跃用户数(当天只要有一条活动记录,即为活跃用户)。

任意顺序 返回结果表。

结果示例如下。

示例 1:

输入:
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1       | 1          | 2019-07-20    | open_session  |
| 1       | 1          | 2019-07-20    | scroll_down   |
| 1       | 1          | 2019-07-20    | end_session   |
| 2       | 4          | 2019-07-20    | open_session  |
| 2       | 4          | 2019-07-21    | send_message  |
| 2       | 4          | 2019-07-21    | end_session   |
| 3       | 2          | 2019-07-21    | open_session  |
| 3       | 2          | 2019-07-21    | send_message  |
| 3       | 2          | 2019-07-21    | end_session   |
| 4       | 3          | 2019-06-25    | open_session  |
| 4       | 3          | 2019-06-25    | end_session   |
+---------+------------+---------------+---------------+
输出:
+------------+--------------+ 
| day        | active_users |
+------------+--------------+ 
| 2019-07-20 | 2            |
| 2019-07-21 | 2            |
+------------+--------------+ 
解释:注意非活跃用户的记录不需要展示。

题解:

# Write your MySQL query statement below
select 
    activity_date as day,
    count(distinct(user_id)) as active_users
from Activity
where DATEDIFF('2019-07-27',activity_date) <=29 && DATEDIFF('2019-07-27',activity_date) >= 0
group by activity_date;

思路:通过筛选日期相差小于 30 天的,然后对日期进行分组,然后把用户进行区分开来进行计数就好了。

文章来源:https://blog.csdn.net/Go_ahead_forever/article/details/135350049
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。