发个FB电面SQL题目攒个人品希望H1B抽中

发信人: halahala007 (小胖鸭), 信区: JobHunting
标  题: 发个FB电面SQL题目攒个人品希望H1B抽中
发信站: BBS 未名空间站 (Thu Apr 10 21:07:12 2014, 美东)

刚电面完FB的Data team职位，SQL题目一共4题。头三题都挺简单，题目如下:
1. Given an EMPLOYEE table and a DEPARTMENT table, write a query to return
the list of Departments for which the total employee salary > $1m

2. Given an EMPLOYEE table, write a query that returns the employee(s) with
the 2nd highest salary.  There may be >1 employee with the top salary, >1
employee with second highest, and so on.

3. Given a table fruit_counts that has these three columns : DATE, FRUIT,
NUM, write a query that gives me the difference of Apples - Oranges for each
day.

DATE FRUIT NUM
4/1  Apple 12
4/1  Orange 5
4/1  Banana 71
4/2  Apple 10
4/2  Orange 7
4/2  Banana 64
4/3  Apple 0
4/3  Orange 5

第四题有点挑战
Given a VISITS tables that has these two columns: User ID and Visit Time and
contains one row for every user visit to a website, write a query that
produces : By Date, distinct number of users that visited on that date and
also onthe previous date - i.e. unique consecutive visitors

Visits
user_id visit_time
123     2014-04-01 12:34
456     2014-04-01 01:15
123     2014-04-01 2:35

123     2014-04-02 10:30
123     2014-04-02 11:27
888     2014-04-02 17:26

123     2014-04-03 16:32
888     2014-04-03 03:15
888     2014-04-03 04:34
456     2014-04-03 18:56



希望国人们H1B都能抽中！
--
※ 来源:·WWW 未名空间站 网址：mitbbs.com 移动：在应用商店搜索未名空间·[FROM: 208.]

http://www.mitbbs.com/article_t/JobHunting/32668245.html

FT中文十大 华尔街中文十大 水木十大 小百合十大 饮水思源十大 日月光华十大

618购物大趴，1000万京东优惠券免费领 ， 更多京东优惠券