推荐:数据科学课程和书籍清单以及培训讲座


一亩三分地论坛

 找回密码
 获取更多干活,快来注册

一亩三分地官方iOS手机应用下载
查看: 2803|回复: 3
收起左侧

facebook SMB Data Analyst 电面

[复制链接] |试试Instant~ |关注本帖
taibaolao 发表于 2016-7-14 06:28:02 | 显示全部楼层 |阅读模式

2016(7-9月) 分析|数据科学类 硕士 全职@Facebook - 内推 - 技术电面 |Fail在职跳槽

注册一亩三分地论坛,查看更多干货!

您需要 登录 才可以下载或查看,没有帐号?获取更多干活,快来注册

x
面挂了
Anyway 可以发一下题目 大家以后好好准备

SQL: cummulative clients table 和 今天所有clients table,更新今天的clients信息到cummulative table里。update flag (new clients,ressurrecting clients,churn,。。。). From 1point 3acres bbs

Statistics:如何跟business的人解释 p-value

business question:如何评估一个marketing compaign的performance


评分

1

查看全部评分

小塔 发表于 2016-12-22 12:07:23 | 显示全部楼层
关注一亩三分地公众号:
Warald_一亩三分地
ressurrecting clients 要怎样flag呢?
回复 支持 反对

使用道具 举报

miles0302 发表于 2017-2-4 17:02:23 | 显示全部楼层
关注一亩三分地微博:
Warald
SQL的话,为什么不直接把今天的clients table 加上今天的date 然后append 到 cummulative的table里面去?
回复 支持 反对

使用道具 举报

kurokubs 发表于 2017-3-28 14:27:45 | 显示全部楼层
一种SQL解法供参考。

drop table if exists cummulative_client;
drop table if exists today_client;

create table cummulative_client(
  id int,
  new_client int,. Waral 鍗氬鏈夋洿澶氭枃绔,
  ressurrecting_client int,
  churn int,
  record_date date
);

create table today_client(
  id int,
  record_date date. From 1point 3acres bbs

);


insert into cummulative_client Values(1,0,0,0,'2017-3-27');
insert into cummulative_client Values(2,0,0,0,'2017-3-27');
insert into cummulative_client Values(5,0,0,1,'2017-3-27');
insert into cummulative_client Values(6,0,0,1,'2017-3-27');
insert into cummulative_client Values(7,0,1,0,'2017-3-27');
insert into cummulative_client Values(9,1,0,0,'2017-3-27');-google 1point3acres

insert into today_client Values(1,'2017-3-28');
insert into today_client Values(5,'2017-3-28');
insert into today_client Values(7,'2017-3-28');
insert into today_client Values(9,'2017-3-28');
insert into today_client Values(11,'2017-3-28');


with t as (
    select a.id as rowid, (SELECT c.record_date FROM today_client c LIMIT 1) as record_date,.1point3acres缃
    (CASE WHEN a.id IS NULL AND b.id IS NOT NULL THEN 1 ELSE 0 END) as new_client,
    (CASE WHEN b.id IS NULL THEN 1 ELSE 0 END) as churn,
    (CASE WHEN b.id IS NOT NULL AND a.id IS NOT NULL AND a.churn = 1 THEN 1 ELSE 0 END) as ressurrecting_client
    from cummulative_client  a . visit 1point3acres.com for more.
    full outer join today_client  b
    on a.id = b.id
   )
update cummulative_client
set new_client = t.new_client, churn = t.churn,  ressurrecting_client=t.ressurrecting_client, record_date=t.record_date
from t
where id = t.rowid
;

INSERT INTO  cummulative_client
SELECT b.id,1 as new_client,0 as ressurrecting_client,0 as churn,b.record_date as date
from cummulative_client  a . 1point3acres.com/bbs
right join today_client  b
on a.id = b.id .鐣欏璁哄潧-涓浜-涓夊垎鍦
WHERE a.id IS NULL
;

回复 支持 反对

使用道具 举报

本版积分规则

关闭

一亩三分地推荐上一条 /5 下一条

手机版|小黑屋|一亩三分地论坛声明

custom counter

GMT+8, 2017-7-26 18:50

Powered by Discuz! X3

© 2001-2013 Comsenz Inc. Design By HUXTeam

快速回复 返回顶部 返回列表