30 Days Of LeetCode Database Day 14 - 1158. Market Analysis I

1Example 1:
2
3Input: 
4Users table:
5+---------+------------+----------------+
6| user_id | join_date  | favorite_brand |
7+---------+------------+----------------+
8| 1       | 2018-01-01 | Lenovo         |
9| 2       | 2018-02-09 | Samsung        |
10| 3       | 2018-01-19 | LG             |
11| 4       | 2018-05-21 | HP             |
12+---------+------------+----------------+
13Orders table:
14+----------+------------+---------+----------+-----------+
15| order_id | order_date | item_id | buyer_id | seller_id |
16+----------+------------+---------+----------+-----------+
17| 1        | 2019-08-01 | 4       | 1        | 2         |
18| 2        | 2018-08-02 | 2       | 1        | 3         |
19| 3        | 2019-08-03 | 3       | 2        | 3         |
20| 4        | 2018-08-04 | 1       | 4        | 2         |
21| 5        | 2018-08-04 | 1       | 3        | 4         |
22| 6        | 2019-08-05 | 2       | 2        | 4         |
23+----------+------------+---------+----------+-----------+
24Items table:
25+---------+------------+
26| item_id | item_brand |
27+---------+------------+
28| 1       | Samsung    |
29| 2       | Lenovo     |
30| 3       | LG         |
31| 4       | HP         |
32+---------+------------+
33Output: 
34+-----------+------------+----------------+
35| buyer_id  | join_date  | orders_in_2019 |
36+-----------+------------+----------------+
37| 1         | 2018-01-01 | 1              |
38| 2         | 2018-02-09 | 2              |
39| 3         | 2018-01-19 | 0              |
40| 4         | 2018-05-21 | 0              |
41+-----------+------------+----------------+
42 

1SELECT 
2u.user_id AS buyer_id,
3u.join_date,
4COUNT(
5    CASE WHEN YEAR(order_date) = 2019 THEN o.order_id END
6) AS orders_in_2019
7FROM Users u LEFT JOIN Orders o ON o.buyer_id = u.user_id 
8GROUP BY u.user_id, u.join_date;