基础语句快速查询
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