SQL面试题：如何分析平台业务？

原创

mob604756ef5a44 2021-04-13 22:51:50 ©著作权

文章标签 Java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者mob604756ef5a44的原创作品，请联系作者获取转载授权，否则将追究法律责任

文末扫码领【10+大厂数据岗面试题】

作者：小AI同学

** 选择题 **

(单选题）在SQL 中，建立表用的命令是（）？

CREATE SCHEMA

CREATE VIEW

CREATE TABLE

CREATE INDEX

（点击选项查看答案）

SQL取数题

取数背景

目前你是一大型平台数据分析师，平台主要做同城商户即时配送服务，平台业务如图，目前你正在通过MySQL 取数，构建各指标分析体系，并用于完善数据分析报告。

用户（C 端）一般操作流程是用户可登录APP/微信/支付宝/淘宝/其他渠道，访问各地区商户，对于喜欢的商品下单购买，即时配送。

商户（B 端）一般操作流程是商户登录APP 商户后台营销自身商品，让更多用户购买。

因此，用户和商户的增量对于APP 平台有正向作用，也是平台重点监控指标。

目前APP 平台数据库dw 和dim,目前你有权限的是dw，且拥有表权限的表有：

1、访客表user_visit_data

注：访问一次就生成一个访问ID，每个用户只有一个用户ID。

2、订单表uesr_order_data

3、商户表shop_wide_data

假如你的老板想看下目前的访客、订单、转化率等，请取出对应数据：

1、想看下11 月各渠道去重用户数有多少? 各渠道用户访问次数是多少？ （需要使用访客表完成操作并根据访客数降序排列）

select  visit_channel
        ,count(distinct visit_user) as '去重访客数'
        ,count(visit_id) as '访问次数'
 from dw.user_visit_data t1
 where visit_ds between '2020-11-01' and '2020-11-30'
 group by visit_channel
 order by count(distinct visit_user) desc;

2、11 月城市总订单和A 区、B 区有多少订单,order_gmv>30 有多少订单,单均SKU 是多少（保留两位小数）请使用订单表完成答题，要求答案在一页显示。

注：城市A 区中包含A1 ,A2 等等以A 做区分，同理B 区，单均SKU= 订单数/(order_sku 总数)

 
select t.new_district
      ,count(distinct t.order_id) as '订单数'
      ,round(count(distinct t.order_id)/sum(t.order_sku) ,2) as '单均SKU'
      ,count(distinct case when t.order_gmv>30 then t.order_id end) as '30+'
from(select 
         case when order_district like 'A%' then 'A' 
              when order_district like 'B%' then 'B' end as new_district
        ,order_id
        ,order_sku
        ,order_gmv
from dw.uesr_order_data
where order_dt between '2020-11-01' and '2020-11-30')t
group by t.new_district
union all
select  a.city
       ,count(distinct a.order_id) as '订单数'
       ,round(count(distinct a.order_id)/sum(a.order_sku) ,2) as '单均SKU'
       ,count(distinct case when a.order_gmv>30 then a.order_id end) as '30+'
from dw.uesr_order_data a
where order_dt between '2020-11-01' and '2020-11-30'
group by a.city;

3、请利用商户表和订单表关联，求创建时间在11 月的商户在12 月每日订单：

-- 返回字段：11月商户"shop_id"字段、12月日期字段(应该在订单表)、每日的订单数


SELECT  daily
        ,a.shop_id
        ,order_num
from(SELECT  DATE_FORMAT(order_dt,"%Y-%m-%d") as daily
            ,shop_id
            ,count(order_id) as order_num
FROM user_order_data -- 订单表
where month(order_dt)=12
group by  DATE_FORMAT(order_dt,"%Y-%m-%d")
         ,shop_id) a
inner join
(SELECT shop_id
from shop_wide_data -- 商户表
where month(creat_time) = 11)b
on a.shop_id = b.shop_id

【语法考点】