知乎面试，含面试题解答

live_id 直播id,
userid  用户id,
date_stamp  时间戳,
entry_type  登陆状态，登陆(enter)和登出(out)

select live_id,
       userid,
       date_stamp,
       sum(flag) over (order by date_stamp)
  from (
        select live_id,
               userid,
               date_stamp as date_stamp,
               case when entry_type = 'enter' then 1
                    when entry_type = 'out'   then -1
                    else 0
                     end flag
          from (
                select 1 live_id,
                       'A' userid,
                       '20200801 10:00:00' date_stamp,
                       'enter' entry_type
             union all select 1 live_id,
                       'B' userid,
                       '20200801 10:01:00' date_stamp,
                       'enter' entry_type
            union all select 1 live_id,
                       'A' userid,
                       '20200801 10:01:01' date_stamp,
                       'out' entry_type
             union all select 1 live_id,
                       'C' userid,
                       '20200801 10:02:03' date_stamp,
                       'enter' entry_type
               )
       )
 group by live_id,
          userid,
          date_stamp,
          flag

uid     用户id,
followid    关注用户id

with test as (
  select 'A' uid,
               'B' followid
     union all 
         select 'A' uid,
                'C' followid
     union all 
         select 'B' uid,
                'A' followid
)

select count(1)/2
 from test t1
inner join test t2
   on t1.uid = t2.followid
  and t1.followid = t2.uid;

with test as (
         select 'A' uid,
                'B' followid
     union all 
         select 'A' uid,
                'C' followid
     union all 
         select 'B' uid,
                'A' followid
)

select
	count(1)/2
from
 (select uid,
       followid from(
        	select uid,followid from test
         union all
         	select followid,uid from test
       ) t
 group by uid,
          followid
having count(1) > 1) tmp

class Solution {
    public List<List<Integer>> threeSum(int[] nums) {
        int n = nums.length;
        Arrays.sort(nums);
        List<List<Integer>> ans = new ArrayList<List<Integer>>();
        // 枚举 a
        for (int first = 0; first < n; ++first) {
            // 需要和上一次枚举的数不相同
            if (first > 0 && nums[first] == nums[first - 1]) {
                continue;
            }
            // c 对应的指针初始指向数组的最右端
            int third = n - 1;
            int target = -nums[first];
            // 枚举 b
            for (int second = first + 1; second < n; ++second) {
                // 需要和上一次枚举的数不相同
                if (second > first + 1 && nums[second] == nums[second - 1]) {
                    continue;
                }
                // 需要保证 b 的指针在 c 的指针的左侧
                while (second < third && nums[second] + nums[third] > target) {
                    --third;
                }
                // 如果指针重合，随着 b 后续的增加
                // 就不会有满足 a+b+c=0 并且 b<c 的 c 了，可以退出循环
                if (second == third) {
                    break;
                }
                if (nums[second] + nums[third] == target) {
                    List<Integer> list = new ArrayList<Integer>();
                    list.add(nums[first]);
                    list.add(nums[second]);
                    list.add(nums[third]);
                    ans.add(list);
                }
            }
        }
        return ans;
    }
}