面试常考sql

面试常考sql

连续登陆最大值

select 
uid,
max(continuous_days) as max_log_days  
from (select 
uid,
date_sub(log_time,rk) as groups,
min(log_time) as start_time,
max(log_time) as end_time,
count(1) as continuous_days 
from (
select uid,log_time,row_number() over(partition by uid order by log_time asc) as rk from lianxu_denglu_day 
) a 
group by uid,date_sub(log_time,rk)) b group by uid;

连续登陆180天日活

select  bb.uid, count(1) as num  from 
(select 
distinct b.dayno, 
         b.uid,        
from act_user_info a
inner join act_user_info b on a.uid = b.uid  and b.dayno > a.dayno 
      and b.dayno - a.dayno >=180) as bb
group by bb.uid     
where count(1) >=180 
;

---------------------------------

T1
user_id     score
1                90
2                80
3                70

求所有大于平均分以上的用户明细数据

T2
user_id     score
1                90

select * from
(select 
 user_id,score,
 avg(score) over() as rn 
from dmf_dev.test_zh1) t1
where score > rn；

计算相互关注的人

select 
  a.from_user,
  a.to_user,
  if( sum(1) over (partition by feature) > 1, 1, 0) as is_friend
from 
(
  select 
    a.from_user,
    a.to_user,
    if(from_user > to_user, concat(to_user, from_user), concat(from_user, to_user)) as feature
  from table_relation 
)a
;

hive 最长连续登陆天数讲解

UID loadtime
201 3
202 4
203 2

思路 第一步
2017-01-01 1
2017-01-02 2
2017-01-04 3
2017-01-05 4
2017-01-06 5

select uid,
loadtime,
row_number()over(partition by uid order by loadtime asc) r
from user

思路第二步、
2016-12-31
2016-12-31
2017-01-01
2017-01-01
2017-01-01

select b.uid,max(n)
(select
b.uid,
count(1) n
from
(select a.uid,
a.loadtime
date_sub(a.laodtime,r)
from
(select uid,
loadtime,
row_number()over(partition by uid order by loadtime asc) r
from user) a)b
group by b.uid,b.s) c
group by c.uid
————————————————
 

核桃编程面试题

1:样例数据中的数据含义是： 
用户UserA，在LocationA位置，从8点开始， 停留了60分钟 
用户UserA，在LocationA位置，从9点开始， 停留了60分钟 
用户UserA，在LocationB位置，从10点开始，停留了60分钟 
用户UserA，在LocationA位置，从11点开始，停留了60分钟 
该样例期待输出： 
UserA, LocationA, 2018-01-01 08:00:00, 120 
UserA, LocationB, 2018-01-01 10:00:00, 60 
UserA, LocationA, 2018-01-01 11:00:00, 60 
处理逻辑： 
a> 对同一个用户，在同一个位置，连续的多条记录进行合并
b> 合并原则：开始时间取最早时间，停留时长加和

select 
user_id,
location_id,
start_time,
all_time
from (select 
user_id,
location_id,
group_start,
min(start_time) as start_time,
sum(long_time) as all_time
from (select user_id,
       location_id,
       start_time,
       end_time,
       all_time,long_time,
       from_unixtime(unix_timestamp(end_time)-all_time*60,'yyyy-MM-dd HH:mm:ss') as group_start
from (select user_id,
       location_id,
       start_time,long_time,
       from_unixtime(unix_timestamp(start_time)+long_time*60,'yyyy-MM-dd HH:mm:ss') as end_time,
       sum(long_time) over(partition by user_id,location_id order by start_time) as all_time
from dmf_dev.tmp_20210304_user_location_1) t ) d 
group by user_id,
location_id,
group_start) e

第一步结果：

第二部：

第三部

求开机你的电脑开机时间击败了多少人

select
a,--人
b,--开机时间
total-rn,--击败多少人
total,--总数
(total-rn)/total --击败多少人占比
from
(
select
a,
b,
row_number() over(order by b desc ) as rn,--排名
sum(1)over() as total --总数
from
(
select '111' as a,30 as b 
union all 
select '222' as a,40 as b 
union all 
select '333' as a,50 as b
union all 
select '444' as a,70 as b  
) tmp 
) t