Hive sql 常见面试题-复购率问题

现有电商订单表（order_detail）如下。

注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率

此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数

近90天指包含最大日期（以订单详情表(order_detail)中最后的日期）在内的近90天。结果中复购率保留2位小数，并按复购率倒序、商品ID升序排序。

期望结果如下：

查询sql

with
  t as (
    SELECT DISTINCT
      user_id,
      product_id,
      count(user_id) over (
        PARTITION BY
          user_id,
          product_id
      ) cn
    FROM
      (
        SELECT
          user_id,
          product_id,
          order_date,
          max(order_date) over (
            ORDER BY
              1
          ) max_od
        FROM
          order_detail
      ) t1
    WHERE
      order_date >= date_sub (max_od, 90)
  )
SELECT
  product_id,
  cast(sum(num) / count(user_id) as DECIMAL(16, 2)) cpr
FROM
  (
    SELECT
      user_id,
      product_id,
      IF (cn = 2, 1, 0) num
    FROM
      t
  ) t1
GROUP BY
  product_id
order by
  cpr desc