Houzz QA 面筋

找朋友内推了3个职位，然后HR很快就联系我面Quantitative Analyst.

技术店面跟FB很像，SQL + 产品sense。
三哥先是迟到了十分钟，然后全程冷漠，SQL题我是先把思路讲了一下再开始写的，中间有个小bug被提出来修复了一下。产品题真是简直了，完全木有互动，他一直问what else，也不针对我回答的点进行质疑或者深入讨论。。。果然第二天就直接拒掉了，可能我产品确实也答得不好吧。。
. 1point3acres
面筋见隐藏：

您好！
本帖隐藏的内容需要积分高于 180 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

游客，您好！
本帖隐藏的内容需要积分高于 180 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式

求大米啊啊啊啊啊啊~

这道题真的好难啊。。。。

SELECT TEAM, MAX(consecutive_winning) as Max_win
FROM (
  SELECT Team, match_diff, count(*) as consecutive_winning
  FROM (
    SELECT Team, WinnerFlag,
             ROW_NUMBER() OVER (PARTITION BY Team ORDER BY match_id) AS nth_match,
            SUM(CASE WHEN WinnerFlag = ‘Y’ THEN 1 ELSE 0 END) OVER (PARTITION BY Team ORDER BY         match_id) AS nth_winning,
             (nth_match – nth_winning) AS match_diff
    FROM world_cup
      ) temp1
  WHERE WinnerFlag = ‘Y’
  GROUP BY Team, match_diff
) temp2
GROUP BY Team

深夜大概写了一下，没细细验证。


select team, max(gap) - 1
from
    (
    select team, n_rank, lead(n_rank) over (partition by team order by n_rank) - n_rank gap

    from
        (
        select distinct team, 0 n_rank
        from world_cup
        union all

        select team, max(n_rank)+1
        from
            (
            select team, match_id,
                   rank() over (partition by team order by match_id) n_rank
            from world_cup
        )a
        where WinnerFlag = 'N'
        group by 1

        union all

        select team, n_rank
        from
        (
            select team, match_id,
                   rank() over (partition by team order by match_id) n_rank
            from world_cup
        )a
        where WinnerFlag = 'N'
    )a
    order by 1, 2
)t
where gap is not NULL
group by 1

select *
from
(select team,max(co) as cnt
from
(
select team,gap,count(*) co
from
(
select team,rank-ROW_NUMBER() OVER (Partition By team Order By rank) as gap
from
(
select distinct a.match_id, a.team, a.winner_flat,a.rank
from (
select *, ROW_NUMBER() over (partition by team order by match_id) as rank
from world_cup1
) a,
(
select *, ROW_NUMBER() over (partition by team order by match_id) as rank
from world_cup1
) b
where (a.rank + 1 =b.rank or a.rank - 1 = b.rank)
and a.winner_flat = 'Y' and b.winner_flat = 'Y'
and a.team = b.team
) p1
)p2
group by team,gap
) a
group by team
) t1
union
(
select team, count(winner_flat) as cnt
from world_cup1
where winner_flat = 'Y'
group by team
having count(winner_flat) = 1
)
union
(
select team, count(distinct winner_flat) as cnt
from
(
select c.team, c.winner_flat
from (
select *, ROW_NUMBER() over (partition by team order by match_id) as rank
. 1point 3acres from world_cup1
) c,
(
select *, ROW_NUMBER() over (partition by team order by match_id) as rank
from world_cup1
) d
where ((c.rank + 1 = d.rank) or (c.rank - 1 = d.rank))
and ((c.winner_flat = 'Y' and d.winner_flat = 'N') or (d.winner_flat = 'Y' and c.winner_flat = 'N'))
and c.team = d.team
and c.team not in (select team from (select team,count(winner_flat) as cnt
from
(
select distinct a.match_id, a.team, a.winner_flat
from (
select *, ROW_NUMBER() over (partition by team order by match_id) as rank
from world_cup1
) a,
(
select *, ROW_NUMBER() over (partition by team order by match_id) as rank
from world_cup1
) b
where (a.rank + 1 =b.rank or a.rank - 1 = b.rank)
and a.winner_flat = 'Y' and b.winner_flat = 'Y'
and a.team = b.team
) a
group by team
) tem
)
) t2
where winner_flat = 'Y'
group by team)

这个SQL很难看起来。

楼主摸摸头，我也面过这家的QA，同样三哥面试官，题目都答上来了，第二天无情据，感觉他们家的面试就是个过场，一点诚意都没有。move on吧~

请问楼主店面面了多久哇？

sybil0618 发表于 2019-3-15 01:38
请问楼主店面面了多久哇？

大概40分钟

HualingLax 发表于 2019-3-15 02:53
大概40分钟

十分感谢！

kkoufe 发表于 2019-2-21 15:51
楼主摸摸头，我也面过这家的QA，同样三哥面试官，题目都答上来了，第二天无情据，感觉他们家的面试就是个过 ...

可否请教下店面大致的流程吗，会go over resume and project/intern experience 还是直接做题呀另外会考stats 那些知识吗，recruiter啥也没说。。十分感谢！

感谢楼主分享！

zhuzaizai 发表于 2019-2-21 16:11
深夜大概写了一下，没细细验证。

这个ok吗

SELECT temp2.team, MAX(temp2.bk2bk_winning)
FROM
(
    SELECT temp1.team, temp1.rnkdiff, COUNT(*) AS bk2bk_winning  
    FROM
    (
        SELECT team, match_id,
                    ROW_NUMBER() OVER
                                             (PARTITION BY team ORDER BY match_id ASC) AS rnk,
                    match_id - ROW_NUMBER() OVER
                                             (PARTITION BY team ORDER BY match_id ASC) AS rnkdiff
        FROM world_cup
        WHERE winner_flat = 'Y'
        ) temp
    GROUP BY 1, 2
    ORDER BY 1, 2
    ) temp2
GROUP BY team