注册一亩三分地论坛,查看更多干货!
您需要 登录 才可以下载或查看附件。没有帐号?注册账号
x
在地里看到的面筋有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 TABLE_EVERYDAYB WHEREA.USER_ID = B.USER_ID ANDB.DATE= CURDATE()-1
5), 如何找出在一天之内始终保持feature on的人,(given status table and action table). /*The only case is: YESTEDAY WAS ON AND THERE IS NO ACTION TODAY*/ SELECTDISTINCT A.USER_ID FROMTABLE_EVERYDAY A LEFTJOIN TABLE_TODAYB ONA.USER_ID = B.USER_ID 您好! 本帖隐藏的内容需要积分高于 188 才可浏览 您当前积分为 0。 使用VIP即刻解锁阅读权限或查看其他获取积分的方式 游客,您好! 本帖隐藏的内容需要积分高于 188 才可浏览 您当前积分为 0。 VIP即刻解锁阅读权限 或 查看其他获取积分的方式
 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
|