sql笔试题目

1. 在电商中，流量漏斗是最基础的监控漏斗，因此希望你能够基于我提供的数据表和字段构建一个流量转化漏斗。可能会用到的数据表如下：

曝光表 exhibit：info_id （商品id），dt (日期），token（用户唯一标示）

详情表visit： info_id （商品id），dt (日期），token（用户唯一标示）

支付表pay：info_id （商品id），dt (日期），token（用户唯一标示），parent_order_id( 订单唯一标示）

请输出包含如下报表SQL： 日期（2020-03-01 至 2020-03-31）， 曝光商品量，列表流量uv，列表流量pv，详情流量uv，详情流量pv，订单量。

#1.
SELECT 
  COUNT(DISTINCT info_id) 曝光商品量,
  COUNT(DISTINCT token) 列表流量 uv,
  COUNT(token) 列表流量 pv 
FROM
  exhibit 
WHERE dt BETWEEN '2020-03-01' 
  AND '2020-03-31' ;
 SELECT 
    COUNT(DISTINCT token) 详情流量 uv,
    COUNT(token) 详情流量 pv 
  FROM
    visit 
  WHERE dt BETWEEN '2020-03-01' 
    AND '2020-03-31' ;
  SELECT 
      COUNT(DISTINCT parent_order_id) 订单量 
    FROM
      pay 
    WHERE dt BETWEEN '2020-03-01' 
      AND '2020-03-31' ;      

2. 请根据要求写出相应SQL。

用户表tableA：user_id（用户id）、date（注册时间）

订单表tableB：user_id（用户id）、order_id（订单id）、money（订单金额）、pay_time(支付时间)

a.    请查出在2020年8月份注册的用户在注册后30日内有支付行为的用户数及支付总额。

b.   查询在2020年8月，订单金额为(0,500)、[500,5000)、[5000,∞)的订单数量和订单总额。

 #2.a.
SELECT COUNT(*),SUM(tableb.money) 
FROM tableb 
JOIN tablea 
ON tablea.user_id = tableB.`user_id` 
      WHERE TIMESTAMPDIFF(
          DAY,
          tablea.date,
          tableb.`pay_time`
        ) <= 30;

#b.
SELECT 
  COUNT(
    IF(money < 500 
      AND money > 0, TRUE, NULL)
  ) g1,
  SUM(IF(money < 500 
      AND money > 0, money, 0)) m1,
  COUNT(
    IF(
      money < 5000 
      AND money >= 500,
      TRUE,
      NULL
    )
  ) g2,
  SUM(
    IF(money < 5000 
      AND money >= 500, money, 0)
  ) m2,
  COUNT(IF(money >= 5000, TRUE, NULL)) g3,
  SUM(IF(money >= 5000, money, 0)) m3 
FROM
  tableb ;

3. 查询下方表（表名：order）中每一个用户（uid）第一次支付完成时间，对应的商品id及金额（Pay_price）

#3.
SELECT *
FROM
  `order` X
WHERE pay_time =
  (SELECT 
    y.pay_time 
  FROM
    `order` Y
  WHERE y.uid = x.uid 
  ORDER BY pay_time 
  LIMIT 1);

注意事项：

1表名和关键字重复时，用``引起来。

2 有的sql版本不支持在使用 LIMIT 子句的 IN ALL ANY SOME 子查询.

3 timestampdiff ()

4 count(if()),sum(if())

5 毫秒的保存类型 bigint，位数13位。