大数据SQL经典面试题系列(3) -网易严选/考拉数据分析SQL题

查询每个用户第一单的消费金额

现在数据库中有一张用户交易表order，表结构为：

userid	---	用户ID
amount	--- 消费金额
paytime	--- 支付时间

请写出对应的SQL语句，查出每个用户第一单的消费金额。

select 
    a.userid,
    a.amount
from
    (select 
        * ,
        rank() over(partition by userid order by paytime) as paytime_rank 
    from 
        order
    ) a 
where 
    paytime_rank=1;

查询每个月的新客数，当月有复购的新客数，新客当月复购率

现在数据库中有一张用户交易表order，表结构为：

userid	---	用户ID
orderid	---	订单ID
amount	--- 消费金额
paytime	--- 支付时间

请写出对应的SQL语句，查出每个月的新客数（新客指在严选首次支付的用户）

-- sql1  :  获取年月，提升数据粒度
select
    *,
    data_format(paytime,'%Y-%m') as ym
from
    order
;
 
-- sql2  :  获取用户第一次支付的时间

select
    userid,
    min(ym) as min_ym
from
    sql1
group by
    userid;

-- sql3  :  统计每月新增人数
select
    count(*)
from
    sql2
group by
    min_ym;
     
--组合起来:
select
    count(*)
from
    (
    select
        userid,
        min(ym) as min_ym
    from
        (
        select
            *,
            data_format(paytime,'%Y-%m') as ym
        from
            order
        ) t1
    group by
        tuserid
    ) t2
group by
    min_ym;

请写出对应的SQL语句，当月有复购的新客数。

-- sql1  :  获取年月，提升数据粒度
select
    *,
    data_format(paytime,'%Y-%m') as ym
from
    order
;
 
-- sql2  :  获取第一次支付当月支付记录大于1的用户ID

select
    userid
from
    sql1
group by
    userid,
    ym
having
    ym = min(ym) and count(*) > 1
;


-- 组合
select
    userid
from
    (
    select
        *,
        data_format(paytime,'%Y-%m') as ym
    from
        order
	) t1
group by
    userid,
    ym
having
    ym = min(ym) and count(*) > 1
;

请写出对应的SQL语句，新客当月复购率（公式=当月有复购的新客数/月总新客数）。

第二题的查询结果除以第一题的查询结果，不多说。