大厂SQL题2-多表关联、转化率、打标签

一、红包流向

1.1 某表数据中不在另一个表中出现过的比例

红包发送方用户的基本信息缺失率有多高？（即有多少红包发送方用户无法在用户基本信息表中匹配？）

筛选出相关字段–左连接–不出现的即会为空—用count（1）与count（字段）来区分

select count(1),count(b.usr_id),1-count(b.usr_id)/count(1)
from
(select distinct snd_usr_id from tx_red_pkt_rcd) a
left join
(select distinct usr_id from tx_usr_bas_inf) b

1.2 哪一组红包金额的拒收率最高？

用case when 分组，用case when 打标签

select bq,sum(s)/count(1)
from
(
select *,
case when pjt_amt between 0 and 50 then " (0, 50元]"
when pjt_amt between 50 and 200 then " (50, 200元]"
else " [200元, +)" end  as bq
(case when rcv_datetime = '1900-01-01 00:00:00' then 1 else 0 end )as s
from tx_red_pkt_rcd
)
group by bq

二、电商购物

2.1 1.从展示到浏览、浏览到加购、加购到购买的转化率分别为？(按照用户数而非点击量算)

要对用户id，产品编号去重，同一用户对不同产品的行为路径是不同的

select count(a.cust_uid),count(b.cust_uid),count(c.cust_uid),count(d.cust_uid)
from (select distinct cust_uid,prod_id from tb_clk_rcd where if_snd=1) a

left join (select distinct cust_uid,prod_id from tb_clk_rcd where if_vw=1) b
on a.cust_uid=b.cust_uid and a.prod_id=b.prod_id

left join (select distinct cust_uid,prod_id from tb_clk_rcd where if_cart=1) c
on a.cust_uid=c.cust_uid and a.prod_id=c.prod_id

left join (select distinct cust_uid,prod_id from tb_clk_rcd where if_buy=1) d
on a.cust_uid=d.cust_uid and a.prod_id=d.prod_id

触达-浏览-加购-购买
同一个表因为条件限制不同，把满足条件的数据筛选出来，多次关联。
关联条件必须为 cust_uid & prd_id，两个都要写。

2.2 哪个商品的加购率最高？

下面这段就能把每类商品的加购率求出来

select a.prd_id, count(b.cust_uid), count(a.cust_uid), 
 count(b.cust_uid)/count(a.cust_uid) pct --求出每个商品的加购率
 from
 (select distinct cust_uid, prd_id from tb_clk_rcd where if_vw=1)a--从浏览
 left join
 (select distinct cust_uid,prd_id from tb_clk_rcd where if_cart=1)b--到加购，是加购率
 on a.cust_uid=b.cust_uid and a.prd_id = b.prd_id
 group by prd_id

最外面套，再套一个select 是因为要把产品名称对应出来

select t2.prd_nm, t1.*
from
 (select a.prd_id, count(b.cust_uid), count(a.cust_uid), 
 count(b.cust_uid)/count(a.cust_uid) pct --求出每个商品的加购率
 from
 (select distinct cust_uid, prd_id from tb_clk_rcd where if_vw=1)a--从浏览
 left join
 (select distinct cust_uid,prd_id from tb_clk_rcd where if_cart=1)b--到加购，是加购率
 on a.cust_uid=b.cust_uid and a.prd_id = b.prd_id
 group by prd_id)t1
inner join
 tb_prd_map t2
on t1.prd_id = t2.prd_id
order by pct desc;

2.3 购买哪个商品的用户的平均年龄最高？–会

select c.prd_id,avg(c.age)
from 
(select distinct cust_uid,prd_id
from tb_clk_rcd a
where a.if_buy = 1) c
left join tb_cst_bas_inf b on c.cust_id=b.cust_id
group by c.prd_id

2.4 以下哪组价格区间的购买人数最多？100元及以下 \100-500元\500元以上

逃不开用case打标签
法一：打三次标签，在第一列，满足‘100元及以下’记为1，其余为0，二三列类似，最后sum(列名) —能求出满足每种条件的行数
记得删选满足购买条件的去重用户

select sum(b.s1),sum(b.s2),sum(b.s3)
from 
(select distinct cust_uid,prd_id
from tb_clk_rcd 
where a.if_buy = 1) a
left join
(
select prd_id,
(case when price<=100 then 1 else 0 end ) s1,
(case when  price >100 and price <=500  then 1 else 0 end ) s2,
(case when price>500 then 1 else 0 end ) s3
from tb_prd_map
) b

法二：打一次标签bq列，根据bq分组后求count（用户id）–得出满足不同条件的人数

select case when price<= 100 then 'bin1'
when price >100 and price <=500 then 'bin2'
 else 'bin3'
 end as bq, count(distinct cust_uid)
from
(select a.*, b.price 
from 
tb_clk_rcd a
inner join 
tb_prd_map b on a.prd_id=b.prd_id
where a.if_buy=1)t
group by 1

2.5 将用户分为两组，A组（性别男，年龄在20-35之间），B组（性别女，年龄在45-55之间）考察AB两组对不同商品的展示到浏览的转化率

#先把满足A组的行为记录筛出来
view azu as 
(select t1.* from tb_clk_rcd t1 left join tb_cst_bas_inf t2 where t2.gdr=M and t2.age between 20 and 35)
#根据azu表中行为记录求每类商品的转化率---2.2题目
select a.prd_id,count(a.cust_uid),count(b.cust_uid)
from (select distinct cust_uid,prod_id from azu where if_snd=1) a
left join (select distinct cust_uid,prod_id from azu where if_vw=1) b
on a.cust_uid=b.cust_uid and a.prod_id=b.prod_id
group by a.prd_id

#同理，计算B组的。。一样的步骤
view bzu as
(select t1.* from tb_clk_rcd t1 left join tb_cst_bas_inf t2 where t2.gdr=F and t2.age between 45 and 55)