1.
这里我们可以看出有两种情况,要么活跃,要么不活跃,我的思路是统计出不活跃的, 并计算出他们所占比例,再用1减去他们所占比例,就可以得到留存率。大致思路就这样,具体代码后面补。下面代码为老师提供的一种查询代码,可供参考。
select t2.dt as dt, ifnull(round(new_user/dau,2), 0.00) as uv left rate
from(
-- 每天的新用户数
select dt,count(1) new_user
from(
-- 用户第一次登录时间
select uid,min(date(in_time)) dt
from tb_user_log
group by uid) as t
group by dt
) as t1
-- 因为有可能某天没有新用户数,因此要右连接
right join(
-- 每日活跃用户数
select dt,count(distinct uid) dau
from(
select uid,date(in_time) dt from tb_user_log -- 先在里面date()union all
select uid,date(out_time) dt from tb_user_log
) as t
group by dt
) as t2
on t1.dt = t2.dt
2.
# 编写SQL 语句,查找所有订购了数量至少100 个的 BRO1、BR02 或BRO3
# 的订单。你需要返回 Orderltems 表的订单号 (order num) 、产品ID (prod id)
# 和数量 (quantity) ,并按产品 ID 和数量进行过滤。
select order_num,prod_id,quantity
from OrderItems
where quantity>=100 and prod_id in('BR01','BR02','BR03')
order by order_num,prod_id;
3.
# 编写 SQL 语句,从 Products 表中检索所有的产品名称 (prod name) ,以及名为
# quant sold 的计算列,其中包含所售产品的总数 (在 Orderltems 表上使用子查询
# 和 SUM(quantity)检索)。
select prod_name,t1.quant_sold
from (select prod_id,prod_name from Products) t
join
(select prod_id,sum(quantity) as quant_sold from OrderItems group by prod_id) t1
on t.prod_id=t1.prod_id;
4.
# 检索每个顾客的名称(Customers表中的 cust_name)和所有的订单号(Orders 表中的 order_num),
# 列出所有的顾客,即使他们没有下过订单。最后根据顾客姓名cust_name升序返回
select c.cust_name,o.order_num
from Customers c
left join Orders o
on c.cust_id=o.cust_id
order by c.cust_name;
5.
# 2021.10有展示记录,退货率不大于0.5select product_id,round(sum(if_click)/count(uid),3) ctr,
round(sum(if_cart)/sum(if_click),3) cart_rate,
round(sum(if_payment)/sum(if_cart),3) payment_rate,
round(sum(if_refund)/sum(if_payment),3) payment_rate
from tb_user_event
where substr(event_time,1,7) = '2021-10'
group by product_id
order by product_id ASC