9.12-知乎-数仓-笔试

链表重排

偶数一边，奇数一边，直接两次遍历就行

public ArrayList<Integer> getNewLinkedList (int[] linkeds) {
        ArrayList<Integer> ans = new ArrayList<Integer>();
        for(int i=0;i<linkeds.length;i++){
            if(linkeds[i]%2==0){
                ans.add(linkeds[i]);
            }
        }
        for(int i=0;i<linkeds.length;i++){
            if(linkeds[i]%2==1){
                ans.add(linkeds[i]);
            }
        }
        return ans;
    }

计算连续两次回答的最大时间窗口级平均回答质量

最大时间窗口还好求，平均回答质量题目中完全没有给指标的描述，一直没折腾出来。做完后，有佬说这是原题，搜了一下还真是。。。https://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c?tpId=240&&tqId=39189

select user_id,
days_window as max_interval_day,
round(days_window*cnt/dif_days,2) as avg_score_cnt
from(
select
    user_id,
    count(start_time) as cnt,
    datediff (max(start_time), min(start_time)) + 1 as dif_days,
    max(datediff (next_start_time, start_time)) + 1 as days_window
from
    (
        select
            user_id,
            question_id,
            start_time,
            lead (start_time) over (
                partition by
                    user_id
                order by
                    start_time
            ) as next_start_time
        from
            answer_score_tb
        where
            year (start_time) = 2023
    )as t1
    group by user_id
)t2
where dif_days>1 --至少有两天
order by days_window desc,avg_score_cnt desc;

计算每个房间当天达到最大人数时的数量和当时的时间戳

最多同时在线的变种题

select t1.room_id,
       t1.cnt                        as max_num,
       from_unixtime(t2.action_time) as action_time
from (

         --各房间最大同时在线人数
         select room_id,
                max(cnt) as cnt
         from tmp
         group by room_id
     ) t1 --要当时的时间戳 只能left join上
    left join(
    select *
    from tmp
) t2 on t1.room_id = t2.room_id and t1.cnt = t2.cnt

with tmp as (
    -- 与 同时在线人数 相同的处理方法
    select room_id,
           user_id,
           sum(tag) over (
               partition by room_id
               order by action_time
               ) as cnt,
           action_time
    from (
             select *,
                    1 as tag
             from room_user_action_log
             where action = 'enter'
             union all
             select *,
                    -1 as tag
             from room_user_action_log
             where action = 'quit'
         ) t1
)t2