Facebook Data Scientist SQL面经+参考答案

在地里看到的面筋有2个痛点：
1. 逻辑混乱，非常confusing （还不如不贴）
2. 没有答案

在这里我抛砖引玉，贴一些我整理+亲自经历过的电面SQL题， 并附上我的答案。希望能帮助到大家（求大米）。

1. There is atable that tracks every time a user turns a feature on or off, with columnsuser_id, action ("on" or "off), date, and time.

1) How manyusers turned the feature on today?

USER_ID || ACTION||DATE||TIME

SELECTCOUNT(DISTINCT USER_ID)  

FROMTABLE

WHEREDATE = CURDATE()

ANDACTION = 'on';

How manyusers have ever turned the feature on?

SELECTCOUNT(DISTINCT USER_ID)

FROMTABLE

WHEREACTION = 'on';

2) Create atable that tracks the user last status every day.

SELECT A.DATE, B.USER_ID, B.STATUS

(SELECT GENERATE_SERIES('2018-01-01'::DATE,'2018-09-01'::DATE, '1D')::DATE) TABLEA(DATE)  

LEFT JOIN  

(SELECT * FROM

TABLE  

QUALIFY ROW_NUMBER() OVER (PARTITION BY USER_ID, DATEORDER BY TIME DESC)  =1

) B

ON TABLEA.DATE >= B.DATE

QUALIFY ROW_NUMBER() OVER(PARTITION BY A.DATE, B.USER_IDORDER BY B.DATE DESC) =1

3) In a tablethat tracks the status of every user every day, how would you add today's datato it?   

/*ASSUMINGACCOUNTS KEY ARE UNIQUE IN TABLE_TODAY */

/*OTHERWISEWE CAN PICK THE LAST ACTION IN TODAY AS THE STATUS*/

SELECTA.*

FROMEVERYDAY_SATUS

UNION

(

SELECT  

COALESCE(A.USER_ID,B.USER_ID) AS USER_ID

,CASEWHEN A.USER_ID IS NULL THEN B.STATUS

WHENA.USER_ID IS NOT NULL AND B.USER_ID IS NULL THEN A.ACTION

WHENA.USER_ID IS NOT NULL AND B.USER_ID IS NOT NULL THEN A.ACTION

END ASSTATUS

,CURDATE()AS DATE

FROM

TABLE_TODAYA

FULL OUTER JOIN  

TABLE_EVERYDAYB

WHEREA.USER_ID = B.USER_ID

ANDB.DATE= CURDATE()-1

);

/*OR*/

INSERTINTO TABLE_EVERYDAY

SELECT  

COALESCE(A.USER_ID,B.USER_ID) AS USER_ID

,CASEWHEN A.USER_ID IS NULL THEN B.STATUS

WHENA.USER_ID IS NOT NULL AND B.USER_ID IS NULL THEN A.ACTION

WHENA.USER_ID IS NOT NULL AND B.USER_ID IS NOT NULL THEN A.ACTION

END ASSTATUS

,CURDATE()AS DATE

FROM

TABLE_TODAYA

OUTERJOIN  

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

游客，您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式
Unlock interview details and practice with AI
Curated Interview Questions from Top Companies

of advertiser

ad_id is idof an ad being run by advertiser

spend isamount of money in $ that advertiser pays Facebook for ad-id to show it to FBusers.

price is how much the user_id spend through this ad.,assuming all prices > 0.  

Questions- The fraction of advertisers has at least one conversion. - What metrics wouldyou show to advertisers.  

Q1: What would the averageadvertiser spend on Facebook? Your query should return a single number.

SELECTAVG(TOT_SPEND) AS AVG_ADVSR_SPNT  

FROM

(SELECTADVERTISER_ID

,SUM(SPEND)AS TOT_SPEND

FROMADV_INFO

GROUP BY1) A

追问：afterwe get this table, what will the distribution look like?

NOT SURE ABOUT THIS QUESTION

SELECT   

A.ADVERTISER_ID

,A.SPENT

,COUNT(B.*) AS IMPRESSIONS

,COUNT(DISTINCT B.USER_ID) AS UNIQUE_USER

,SUM(B.PRICE) AS SALES

,A.SPENT/NULLIFZERO(IMPRESSION)AS COST_PER_IMP

,A.SPENT/NULLIFZERO(UNIQUE_USER)AS COST_PER_USER

,A.SPENT/Z

FROM

ADV_INFOA

INNERJOIN AD_INFO B

ONA.AD_ID = B.AD_ID

GROUPBY 1,2

Q3.Thefraction of advertisers has at least one conversion.  

SELECTCOUNT(DISTINCT CASE WHEN B.PRICE>0 THEN A.ADVERTISER_ID END)/COUNT (DISTINCTA.ADVERTISER_ID) AS PCTG_CONVERSION

FROM

ADV_INFOA

INNERJOIN AD_INFO B

ONA.AD_ID =B.AD_ID

2) Create atable that tracks the user last status every day.
SELECT A.DATE, B.USER_ID, B.STATUS
(SELECT GENERATE_SERIES('2018-01-01'::DATE,'2018-09-01'::DATE, '1D')::DATE) TABLEA(DATE)  
LEFT JOIN  
(SELECT * FROM
TABLE  
. 1point3acresQUALIFY ROW_NUMBER() OVER (PARTITION BY USER_ID, DATEORDER BY TIME DESC)  =1
) B
ON TABLEA.DATE >= B.DATE
QUALIFY ROW_NUMBER() OVER(PARTITION BY A.DATE, B.USER_IDORDER BY B.DATE DESC) =1

这部分为什么要用left join呢？

请问这个为什么要Join呢？ 直接Select target_id, Count(Content_id) where target_id is not NULL (或者type=comment) 不可以吗？
根据题意：好像Comment只能是对Post的，不能对Comment进行Comment

1) 有content_id, content_type (comment/ post),target_id。如果是comment，target_id就是post的content id，如果是post则target_id为NULL。求commentdistribution。
CONTENT_ID|| CONTENT_TYPE || TARGET_ID
123|| COMMENT || 100
100|| POST || NULL  

SELECTNBR_COMMENT
,COUNT(CONTENT_ID) AS NBR_POST
FROM
(SELECTA.CONTENT_ID
,COUNT(B.CONTENT_ID) AS NBR_COMMENT
FROMTABLE A
LEFTJOIN TABLE B
ONA.CONTENT_ID = B.TARGET_ID
ANDA.TARGET_ID IS NULL
ANDB.TARGET_ID IS NOT NULL
GROUPBY 1) C
GROUPBY 1
ORDERBY 1

补充内容 (2018-10-1 14:00):
周末脑子不灵光了，看来得多练。 有Post没有回复

第三问第一题，如果没看到广告值为空，是不是NULL， 没看到是不是不能算成CTR 分母里面。
所以 是不是把count(*) 改成count(event). 这样如果是NULL就count 为0， 要不会count 为1.

混乱指的是题目混乱

谢谢楼主

感谢楼主分享

感谢楼主分享！！！

1) How manyusers turned the feature on today?
是不是需要考虑一下， 如果有人先turn it on， and change it back to off?  我觉得需要 count 每个用户最后的value

serena2005 发表于 2018-10-1 22:08
1) How manyusers turned the feature on today?
是不是需要考虑一下， 如果有人先turn it on， and chang ...

I think the variable 'time' could indicate this situation. it will show when this person turned it on and when he turned it off