每天几道SQL题(1)

基础语句快速查询
https://www.runoob.com/sql/sql-func-max.html.
https://www.runoob.com/sqlite/sqlite-limit-clause.html

网易2020校招笔试

现在数据库中有一张用户交易表order，其中有userid（用户ID）、orderid（订单ID）、amount（订单金额）、paytime（支付时间），请写出对应的SQL语句，查出每个月的新客数（新客指在严选首次支付的用户），当月有复购的新客数，新客当月复购率（公式=当月有复购的新客数/月总新客数）。

思路：先建立视图，选出相关的需要的信息，然后再从视图里进行查询

create View t as
(select userid,paytime from order o
where  paytime = (select min(paytime) from order
where o.userid = userid)

select  a.newguests,a.fgguests/a.allnewguests as 复购率
from (select paytime ,count(1) as newguests 
from t
group by paytime
)as a,
(select distict userid  as fgguests 
from order t2
group by paytime
having userid in 
(select userid from t where paytime = t2.paytime) and count