力扣SQL刷题10

618. 学生地理信息报告–完全不会的新题型

max()函数的功效：（‘jack’, null, null）中得出‘jack’，（null,null）中得出null。 min()函数也可以。
窗口 先打标签再group by再case when：

select
max(case when continent = 'America' then name else null end) as America,
max(case when continent = 'Asia' then name else null end)as Asia,
max(case when continent = 'Europe' then name else null end) as Europe
from 
(select *, row_number() over(partition by continent order by name) rk
from student) t
group by rk

1097. 游戏玩法分析 V - 重难点

题型：读题

解答：
看的评论区大佬的解答
由题目可知，需要计算以下几个值：

1. 每个玩家第一个登录日
2. 第一个登陆日之后有没有登录
3. 第一个登录日的玩家的数量，第一个登录日的第二天登录玩家的数量

方法1：建临时表后，再和原表左联，通过datediff(activity.event_date, t1.first_date) = 1限制次日登录

1. 每个玩家第一个登录日，作为 t1 表

select 
	player_id,
	min(event_date) as first_date
from activity group by player_id;

2. 将 t1 表和 activity 左连

select * from t1 
left join activity 
on t1.player_id = activity.player_id 
and datediff(activity.event_date, t1.first_date) = 1;

datediff(大日期，小日期) --日期之差

3. 左联之后的表，再按照第一个登陆日进行分组

完整：

with t as
(select player_id,min(event_date) as first_date
from Activity
group by player_id)


select t.first_date install_dt,count(t.player_id) installs,
round(count(a.event_date)/count(t.first_date),2) day1_retention
from  t 
left join Activity a 
    on t.player_id = a.player_id
        and datediff(a.event_date, t.first_date) = 1
group by t.first_date

方法2：使用了窗口函数代替了分组。

1. 使用窗口函数建临时表

select 
	player_id,
	event_date,
	min(event_date) over(partition by player_id) as first_date
from activity;

t1表：

2. 查询 t1 ，按照 first_date 进行分组

with t1 as(
	select 
		player_id,
		event_date,
		min(event_date) over(partition by player_id) as first_date
	from activity
)


select
	first_date as install_dt,
	count(distinct player_id) as installs,
	round(
		sum(if(date_add(first_date, interval 1 day) = event_date, 1, 0)) 
		/ count(distinct player_id),
	2) as day1_retention
from  t1 group by first_date;

date_add(first_date, interval 1 day) = event_date

1127. 用户购买平台–难且不会

评论区大佬的思路：

1. 统计每天不同平台的消费额，类似于其他的购物指标消费额一般都是以用户和时间作为分组
   这道题的分组是按用户和时间天为分组条件
   group by user_id,spend_date
2. if(count(distinct platform)=2,‘both’,platform)
3. 第二步构造列中如果当日没有同时使用’mobile’和’desktop’购买的用户，就无法输出’both’字段
   因此我们需要构造出一个固定输出三个字段 ‘both’ ‘desktop’ ‘mobile’ 的列

(
select 'desktop' as platform union
select 'mobile' as platform union
select 'both' as platform
) b

完整：

select spend_date,b.platform,
sum(if(a.platform=b.platform,amount,0)) as total_amount,
count(if(a.platform=b.platform,1,null)) as total_users
from(
    select spend_date,user_id,
    if(count(distinct platform)=2,'both',platform) as platform,
    sum(amount) as amount
    from spending
    group by user_id,spend_date
) a,(
    select 'desktop' as platform union
    select 'mobile' as platform union
    select 'both' as platform
) b
group by spend_date,platform