Hive sql 常见面试题-登录次数及交易次数统计

@

1. 题目需求

分别从登陆明细表（user_login_detail）和配送信息表中用户登录时间和下单时间统计登陆次数和交易次数

结果如下（截取部分）：

2. 需要用到的表

用户登录明细表：user_login_detail

配送信息表：delivery_info

3. 查询sql

SELECT
  A.user_id,
  A.login_date,
  if (A.login_count is not null, A.login_count, 0) as login_count,
  if (B.order_count is not null, B.order_count, 0) as order_count
FROM
  (
    select
      user_id,
      date_format (login_ts, 'yyyy-MM-dd') as login_date,
      count(login_ts) as login_count
    from
      user_login_detail
    group by
      user_id,
      date_format (login_ts, 'yyyy-MM-dd')
  ) A
  left join (
    select
      user_id,
      date_format (order_date, 'yyyy-MM-dd') as order_date,
      count(order_date) as order_count
    from
      delivery_info
    group by
      user_id,
      date_format (order_date, 'yyyy-MM-dd')
  ) B ON A.user_id = B.user_id
  and A.login_date = B.order_date