day22 活跃用户&&找到连续区间的开始和结束数字

1454. 活跃用户

写一个 SQL 查询, 找到活跃用户的 id 和 name.

活跃用户是指那些至少连续 5 天登录账户的用户.

返回的结果表按照 id 排序.

结果表格式如下例所示:

考点：日期相减函数date_sub（xxx,interval yyy day）

select
	   distinct t.id,
		name
from
		(select
				a.*,
				date_sub(l.login_date, interval rank() over(partition by a.id order by l.login_date)   DAY) as rk
		from
				Accounts as a left join (select  distinct(login_date) , id from Logins) as l on l.id = a.id) as t
group by
		t.id,t.rk
having 
		count(rk)>=5

1285. 找到连续区间的开始和结束数字

后来一些 ID 从 Logs 表中删除。编写一个 SQL 查询得到 Logs 表中的连续区间的开始数字和结束数字。

将查询表按照 start_id 排序。

查询结果格式如下面的例子。

select
		min(log_id) as start_id,
		max(log_id) as end_id
from
		(select
				*,
				log_id - rank() over(order by log_id) as rk
		from
				Logs as a ) as t
group by
		rk
order by
		start_id