Sql基础题
–用户表
user (user_id，user_name，user_birth，user_sex) –用户编号, 用户姓名, 出生年月,用户性别（primary key：user_id）

–商品表
merchandise (merchandise_id, merchandise_name，category_id) –商品编号, 商品名称, 商品分类编号（primary key：merchandise_id）

–商品分类表
category (category_id, category_name) --商品分类编号, 商品分类名称（primary key：category_id）

–销售表
sales(user_id，merchandise_id，quantity，gmv) –用户编号,商品编号,购买件数，销售额（primary key：user_id, merchandise_id）

1、 前提：购买过商品分类category_name为“平价果蔬”的用户
按用户购买“平价果蔬”件数1，2，3，4，4+件分类，求每个分类下的用户数、平均每个用户购买的“平价果蔬”商品数、平均每个用户购买“平价果蔬”件数、总“平价果蔬”gmv；

答：
select
case
when category_name > 4 then ‘4+’
when category_name = 4 then ‘4’
when category_name = 3 then ‘3’
when category_name = 2 then ‘2’
when category_name = 1 then ‘1’
else ‘其它’
end As 平价果蔬分类,
count(distinct(user_id)) AS 分类下用户数,
count(distinct(merchandise_id)) / count(distinct(user_id)) As 平均商品数,
sum(quantity) / count(distinct(user_id)) As 平均件数,
sum(gmv) As 分类下总gmv
from
(select
user_id
from
user
group by
user_id
) a left join
(select
user_id,
merchandise_id,
quantity,
gmv
from
sales
group by
user_id,
merchandise_id,
quantity,
gmv
)b on a.user_id =b.user_id
And b.merchandise_id = c.merchandise_id
left join
（select
merchandise_id,
merchandise_name,
category_id
from
merchandise
group by
merchandise_id,
merchandise_name,
category_id)c on b. merchandise_id = c. merchandise_id
and c. category_id = d. category_id
left join
（select
category_id,
category_name
from
category
group by
category_id,
category_name)d on c.category_id = d.category_id
where
category_name = ‘平价果蔬’

2、 前提：购买过商品分类category_name为“平价果蔬”用户
查询这类用户购买件数TOP100的商品名称，商品分类,销售件数，总GMV（注：用户购买所有商品的TOP，非平价果蔬下的商品）；

答：
select
merchandise_name AS 商品名称,
category_name As 商品分类,
sum(quantity) As 件数,
sum(gmv) As gmv
from
(select
user_id
from
user
group by
user_id
) a left join
(select
user_id,
merchandise_id,
quantity,
gmv
from
sales
group by
user_id,
merchandise_id,
quantity,
gmv
)b on a.user_id =b.user_id
And b.merchandise_id = c.merchandise_id
left join
(select
merchandise_id,
merchandise_name,
category_id
from
merchandise
group by
merchandise_id,
merchandise_name,
category_id)c on b. merchandise_id = c. merchandise_id
and c. category_id = d. category_id
left join
(select
category_id,
category_name
from
category
group by
category_id,
category_name)d on c.category_id = d.category_id
having
category_name = ‘平价果蔬’

order by quantity DESC

limit 100;

3、-日志表
Log_detail ( visittime，eventype，user_id) – 访问时间，访问事件，用户
查询 访问事件的UV转化率
注：不同事件间可来回切换访问，不同用户间访问事件的访问时间先后顺序不同
(例：用户1从A时间跳转至B事件后再跳转至C事件，从C事件后跳转至A时间，最后有从A事件跳转到C事件，求A->B，A->C，B->A，B->C，C->B，C->A的UV转化率)