项目实战:房产行业如何分析?

 

【题目】

“成交订单表”里记录了某房产平台(类似链家、贝壳等)每日房屋成交的明细。(贝壳面试题)

字段“成交客源渠道”中的值是“客源角色人”、“业主线上委托”、“”表示线下渠道,其余的成交客源渠道是线上。

1.当月截止昨天二手线上成交单量占比(含车位)>=50%的门店可获奖;

(线上成交占比=线上成交单量/总成交单量)

2.符合获奖条件的门店的第1单线上成交可获得200贝壳币(可以用于兑换奖金),第2单可获400贝壳币,第3单及以上可获800贝壳币,但车库不奖励(字段“房屋用途”中的值是”车位”、”车库”认为是车库);

3.在一个连续的SQL中实现以上需求,不能拆分成多个SQL,必须输出表格字段如下(可增加);

【解题思路】

房产平台贝壳的业务是什么?

贝壳业务的实质,是搭建了一个基于房地产服务的协作平台(ACN,agent cooperation network 经纪人协作网络)。房产经纪人、门店、品牌和其他服务提供者在这个平台上进行协同合作。

(图片来源贝壳IPO)

目前贝壳已成为国内最大的线上线下房产交易服务平台,覆盖了103所城市,连接265家地产经纪品牌,4.2万家门店,45.6万经纪人,2019年平台交易总额超2.12万亿,成交量超220万,手机APP月活达3900万。

 

(图片来源贝壳IPO)

对比原始数据表,可以发现本题要求输出表格中增加了3个字段,分别是:是否线上、经纪人所在门店的线上占比、该单应发的贝壳币。

 

1.线上成交占比

只有线上成交占比大于50%的门店订单才可以获得奖励,所以需要先得出每个门店的线上占比。

线上成交占比=线上成交单量/总成交单量

(1)哪些是线上成交的数据?

字段“成交客源渠道”中的值是“客源角色人”、“业主线上委托”、“”表示线下渠道,其余的成交客源渠道是线上。这种多条件判断的业务问题,要想到用《猴子 从零学会SQL》里讲过的case表达式。 利用case来对成交客源渠道字段赋值判断,如果为“线上”就返回1,不是就返回null(表示线下)。

select *,(case when 成交客源渠道 ="客源角色人"            or 成交客源渠道 ="业主线上委托"            or 成交客源渠道 =""       then null       else 1 end) as 是否线上from 成交订单表;

 

查询结果:

 

(2)每个门店的线上成交量

 

遇到“每个”这种业务问题,要想到用《猴子 从零学会SQL》里讲过的“分组汇总”。按门店分组(签约经纪人门店名称),然后统计线上成交数量(count)。

select *,count(case when 成交客源渠道 ="客源角色人"                 or  成交客源渠道 ="业主线上委托"                 or 成交客源渠道 =""            then null            else 1        end) as 是否线上from 成交订单表group by  签约经纪人门店名称;

(3)线上成交占比=线上成交单量/总成交单量

上面已经得到了每个门店的线上成交量。而总成交量可以用count(*)得到。

sql如下:

select *,count(case when 成交客源渠道 ="客源角色人"                    or  成交客源渠道 ="业主线上委托"                    or 成交客源渠道 =""           then null           else 1           end) / count(*)  as 经纪人所在门店的线上占比from 成交订单表group by  签约经纪人门店名称;

查询结果:

将上面的查询结果当作临时表b。

 

 

2.该单应发的贝壳币

符合获奖条件的门店的第1单线上成交可获得200贝壳币(可以用于兑换奖金),第2单可获400贝壳币,第3单及以上可获800贝壳币,但车库不奖励(字段“房屋用途”中的值是”车位”、”车库”认为是车库)

(1)哪些是符合条件的门店?

根据题目的业务说明:当月截止昨天二手线上成交单量占比(含车位)>=50%的门店可获奖。

各门店的线上单量占比已经在上面的临时表b中得出。

本月截止昨天用函数 month (签约时间)=month(now()) 得出。

字段“房屋用途”中的值是”车位”、”车库”认为是车库,用where 来筛选。

sql如下: ​​​​​​​

select a.*,b.经纪人所在门店的线上占比from 成交订单表 as aleft join b   ona.签约经纪人门店名称=b.签约经纪人门店名称whereb.经纪人所在门店的线上占比 >0.5 and month (a.签约时间)=month(now()) and  a.房屋用途 ="";

查询结果:

(2)订单如何排名?

题目的业务需求是:符合获奖条件的门店的第1单线上成交可获得200贝壳币(可以用于兑换奖金),第2单可获400贝壳币,第3单及以上可获800贝壳币。

这里的第1单第2单第3单,是按每个门店的签约时间对订单进行排名。涉及到排名问题,要想到《猴子 从零学会SQL》里讲过的“窗口函数。

用专用窗口函数(row_number)来排名,按签约时间排序(order by)

sql如下:​​​​​​​

select a.*,row_number() over (partition by a.签约经纪人门店名称                    order by a.签约时间 ) as 单量from 成交订单表 as aleft join bona.签约经纪人门店名称=b.签约经纪人门店名称whereb.经纪人所在门店的线上占比 >0.5 and month (a.签约时间)=month(now()) and  a.房屋用途 ="";

查询结果:

把上面的查询结果记为临时表c。

(3)根据单量得出对应的贝壳币

现在根据单量进行奖励,第1单线上成交可获得200贝壳币,第2单可获400贝壳币,第3单及以上可获800贝壳币,利用case when 来判断单量并进行赋值。

sql如下:​​​​​​​

select c.*,case when c.单量>= 3  then 800     when c.单量>= 2  then 400     else 200 end  as  该单应发的贝壳币   from (select *,row_number()      over  (partition by 签约经纪人门店名称             order by 签约时间 ) as 单量from 成交订单表) as c;


查询结果:

 

 

 

3.合并字段

得出 是否线上,经纪人所在门店的线上占比,该单应发的贝壳币 3个字段的代码之后,我们合并在一个连续的SQL中实现题目的业务需求。

合并的sql如下:​​​​​​​

select  c.*,       case when c.单量>= 3  then 800      when c.单量>= 2  then 400      else 200 end  as  该单应发的贝壳币 from(select a.*, b.经纪人所在门店的线上占比,        case when  成交客源渠道 ="客源角色人"                    or  成交客源渠道 ="业主线上委托"                    or 成交客源渠道 =""         then  null else 1  end as  是否线上,  row_number ()    over (partition by  a.签约经纪人门店名称        order by a.签约时间) as 单量from  成交订单表 as aleft join(select 签约经纪人门店名称,      count(case when  成交客源渠道 ="客源角色人"                    or  成交客源渠道 ="业主线上委托"                    or 成交客源渠道 =""         then  null else 1  end ) / count(*) as 经纪人所在门店的线上占比from成交订单表group by 签约经纪人门店名称) as b on a.签约经纪人门店名称 = b.签约经纪人门店名称whereb.经纪人所在门店的线上占比 > 0.5and month  (a.签约时间) = month(now())and a.房屋用途 = "") as c;

查询结果:

【本题考点】

1.用多维度拆解分析方法,将复杂的业务问题拆解为可以解决的简单问题。

2.遇到多条件判断的问题,要想到用case语句来实现。

3.排名问题,要想到用窗口函数来实现。

4.遇到只有一个表且只能用一条SQL语句完成,可以联想到用多表联结,来实现复杂的业务。

 

©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页