Question

1158. 市场分析 I

English Version

题目描述

表： Users

+----------------+---------+
| Column Name  | Type  |
+----------------+---------+
| user_id    | int   |
| join_date    | date  |
| favorite_brand | varchar |
+----------------+---------+
user_id 是此表主键（具有唯一值的列）。
表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。

 

表： Orders

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| order_id    | int   |
| order_date  | date  |
| item_id     | int   |
| buyer_id    | int   |
| seller_id   | int   |
+---------------+---------+
order_id 是此表主键（具有唯一值的列）。
item_id 是 Items 表的外键（reference 列）。
（buyer_id，seller_id）是 User 表的外键。

 

表：Items

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| item_id     | int   |
| item_brand  | varchar |
+---------------+---------+
item_id 是此表的主键（具有唯一值的列）。

 

编写解决方案找出每个用户的注册日期和在 2019 年作为买家的订单总数。

以 任意顺序 返回结果表。

查询结果格式如下。

 

示例 1:

输入：
Users 表:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1     | 2018-01-01 | Lenovo     |
| 2     | 2018-02-09 | Samsung    |
| 3     | 2018-01-19 | LG       |
| 4     | 2018-05-21 | HP       |
+---------+------------+----------------+
Orders 表:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1    | 2019-08-01 | 4     | 1    | 2     |
| 2    | 2018-08-02 | 2     | 1    | 3     |
| 3    | 2019-08-03 | 3     | 2    | 3     |
| 4    | 2018-08-04 | 1     | 4    | 2     |
| 5    | 2018-08-04 | 1     | 3    | 4     |
| 6    | 2019-08-05 | 2     | 2    | 4     |
+----------+------------+---------+----------+-----------+
Items 表:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1     | Samsung  |
| 2     | Lenovo   |
| 3     | LG     |
| 4     | HP     |
+---------+------------+
输出：
+-----------+------------+----------------+
| buyer_id  | join_date  | orders_in_2019 |
+-----------+------------+----------------+
| 1     | 2018-01-01 | 1        |
| 2     | 2018-02-09 | 2        |
| 3     | 2018-01-19 | 0        |
| 4     | 2018-05-21 | 0        |
+-----------+------------+----------------+

解法

方法一

# Write your MySQL query statement below
SELECT
  u.user_id AS buyer_id,
  u.join_date,
  COUNT(order_id) AS orders_in_2019
FROM
  Users AS u
  LEFT JOIN Orders AS o ON u.user_id = o.buyer_id AND YEAR(order_date) = 2019
GROUP BY user_id;

方法二

# Write your MySQL query statement below
SELECT
  user_id AS buyer_id,
  join_date,
  IFNULL(SUM(YEAR(order_date) = 2019), 0) AS orders_in_2019
FROM
  Users AS u
  LEFT JOIN Orders AS o ON u.user_id = buyer_id
GROUP BY 1;