近期论坛无法登录的解决方案


一亩三分地论坛

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

一亩三分地官方iOS手机应用下载
查看: 2693|回复: 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,。。。)

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解法供参考。
. 鍥磋鎴戜滑@1point 3 acres
drop table if exists cummulative_client;
drop table if exists today_client;

create table cummulative_client(
  id int,
  new_client int,
  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');
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');. Waral 鍗氬鏈夋洿澶氭枃绔,
insert into cummulative_client Values(7,0,1,0,'2017-3-27');
insert into cummulative_client Values(9,1,0,0,'2017-3-27');.鐣欏璁哄潧-涓浜-涓夊垎鍦
. 鍥磋鎴戜滑@1point 3 acres
insert into today_client Values(1,'2017-3-28');. 鍥磋鎴戜滑@1point 3 acres
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,. From 1point 3acres bbs
    (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 .1point3acres缃
    full outer join today_client  b . 1point 3acres 璁哄潧
    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. visit 1point3acres.com for more.
;-google 1point3acres

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
;
.鐣欏璁哄潧-涓浜-涓夊垎鍦
回复 支持 反对

使用道具 举报

本版积分规则

关闭

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

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

custom counter

GMT+8, 2017-6-29 19:05

Powered by Discuz! X3

© 2001-2013 Comsenz Inc. Design By HUXTeam

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