新浪数据分析sql第三题通不过啊

select xx.channel_type,round(xx.cnt2-xx.cnt1,4) as cn
from
(
select x.channel_type,sum(case when x.dt='20180707' then 1 else 0 end )/count(distinct case when x.dt='20180707' then x.uid else null end) as cnt1,
sum(case when x.dt='20180708' then 1 else 0 end )/count(distinct case when x.dt='20180708' then x.uid else null end) as cnt2
from
(
select a.uid,a.channel_type,a.dt,b.newsid,b.newsarea
from
(
select uid,substr(new_dt,1,4)||substr(new_dt,6,2)||substr(new_dt,9,2) as dt,channel_type
from new_user_info
where substr(new_dt,1,4)||substr(new_dt,6,2)||substr(new_dt,9,2)>='20180707'
and substr(new_dt,1,4)||substr(new_dt,6,2)||substr(new_dt,9,2) <='20180708'
group by uid,substr(new_dt,1,4)||substr(new_dt,6,2)||substr(new_dt,9,2) ,channel_type
)a
left join
(
select dt,uid,newsid,newsarea
from user_read
where dt>='20180707'
and dt<='20180708'
)b
on a.uid=b.uid
and a.dt=b.dt
)x
group by x.channel_type
)xx
#新浪#