h*********7
发帖数: 169 | 1 刚电面完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都能抽中！ |
d*****c
发帖数: 605 | |
d**********g
发帖数: 15 | 3 请问LZ面的是什么position？全都是SQL，没有问algorithm的问题吗? |
F*******2
发帖数: 371 | 4 谢谢楼主，并bless！
初学SQL，用的是SAS的proc SQL写的。贴出来求教有没有更简洁的code。
1.
create table sumbydept AS
select department from department AS L join employee AS R
on L.name=R.name
group by department
having sum(salary)>1000000
;
2.
create table secondhigh AS
select name From employee
where salary IN (select max(salary) as salary from employee where salary
NOT IN (select Max(salary) from employee))
Or:
SELECT *
FROM Employee Emp1
WHERE (1) =
(
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary
)
3.
create table dfAPPORG AS
select (App.num-Org.num) AS DFAO from
(select * from fruits where fruit IN ('Apple')) AS App
join(select * from fruits where fruit IN ('Orange')) AS Org
on App.date=Org.date
第四个，一会儿想想。。。 |
B*****g
发帖数: 34098 | 5 别想了，FB的SQL就是self join，跑
【在 F*******2 的大作中提到】
: 谢谢楼主，并bless！
: 初学SQL，用的是SAS的proc SQL写的。贴出来求教有没有更简洁的code。
: 1.
: create table sumbydept AS
: select department from department AS L join employee AS R
: on L.name=R.name
: group by department
: having sum(salary)>1000000
: ;
: 2.
|
F*******2
发帖数: 371 | 6 不知道那些financial modeling的job description上说的SQL query是要求到什么程
度的
【在 B*****g 的大作中提到】
: 别想了，FB的SQL就是self join，跑
|
B*****g
发帖数: 34098 | 7 还有其他题吗？
with
each
【在 h*********7 的大作中提到】
: 刚电面完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
|
B*****g
发帖数: 34098 | 8 不知道，估计partition by，rollup，cube要搞定
【在 F*******2 的大作中提到】
: 不知道那些financial modeling的job description上说的SQL query是要求到什么程
: 度的
|
y*****9
发帖数: 149 | 9 SQL在哪练。我看都能看懂，但是平时不用自己不会写。 |
