一亩三分地论坛

 找回密码
 获取更多干货,去instant注册!

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

facebook SMB Data Analyst 电面

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

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

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

您需要 登录 才可以下载或查看,没有帐号?获取更多干货,去instant注册!

x
面挂了. Waral 鍗氬鏈夋洿澶氭枃绔,
Anyway 可以发一下题目 大家以后好好准备

SQL: cummulative clients table 和 今天所有clients table,更新今天的clients信息到cummulative table里。update flag (new clients,ressurrecting clients,churn,。。。). from: 1point3acres.com/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 发表于 昨天 14:27 | 显示全部楼层
一种SQL解法供参考。

drop table if exists cummulative_client;. 涓浜-涓夊垎-鍦帮紝鐙鍙戝竷
drop table if exists today_client;

create table cummulative_client(
  id int,-google 1point3acres
  new_client int,. from: 1point3acres.com/bbs
  ressurrecting_client int,
  churn int, 鏉ユ簮涓浜.涓夊垎鍦拌鍧.
  record_date date
);

create table today_client(
  id int,
  record_date date

);


insert into cummulative_client Values(1,0,0,0,'2017-3-27');. from: 1point3acres.com/bbs
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');

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 (. 鍥磋鎴戜滑@1point 3 acres
    select a.id as rowid, (SELECT c.record_date FROM today_client c LIMIT 1) as record_date,
    (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
    full outer join today_client  b
    on a.id = b.id
   )
update cummulative_client-google 1point3acres
set new_client = t.new_client, churn = t.churn,  ressurrecting_client=t.ressurrecting_client, record_date=t.record_date
from t. visit 1point3acres.com for more.
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
right join today_client  b
on a.id = b.id
WHERE a.id IS NULL
;

回复 支持 反对

使用道具 举报

本版积分规则

请点这里访问我们的新网站:一亩三分地Instant.

Instant搜索更强大,不扣积分,内容组织的更好更整洁!目前仍在beta版本,努力完善中!反馈请点这里

关闭

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

手机版|小黑屋|一亩三分地论坛声明 ( 沪ICP备11015994号 )

custom counter

GMT+8, 2017-3-29 05:04

Powered by Discuz! X3

© 2001-2013 Comsenz Inc. Design By HUXTeam

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