LeetCode 1158. Market Analysis I SQL Solution

Problem

LeetCode SQL Problem

1158. Market Analysis I

Users table

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 table

order_id	order_date	item_id	buyer_id	seller_id
1	2019-08-01 00:00:00.000	4	1	2
2	2018-08-02 00:00:00.000	2	1	3
3	2019-08-03 00:00:00.000	3	2	3
4	2018-08-04 00:00:00.000	1	4	2
5	2018-08-04 00:00:00.000	1	3	4
6	2019-08-05 00:00:00.000	2	2	4

Items table

item_id	item_brand
1	Samsung
2	Lenovo
3	LG
4	HP

Solution

MySQL

Market Analysis I

-- Perform a LEFT JOIN to retrieve purchase orders in 2019 for each user
-- LEFT JOIN is required so we can output users who have made no purchase in 2019
SELECT U.user_id AS buyer_id
	,U.join_date
	,count(O.order_id) AS orders_in_2019
FROM Users AS U
LEFT JOIN Orders O ON U.user_id = O.buyer_id
	AND YEAR(O.order_date) = 2019
GROUP BY U.user_id
	,U.join_date
ORDER BY U.user_id

TSQL

Market Analysis I

-- Perform a LEFT JOIN to retrieve purchase orders in 2019 for each user
-- LEFT JOIN is required so we can output users who have made no purchase in 2019
SELECT U.user_id AS buyer_id
	,U.join_date
	,count(O.order_id) AS orders_in_2019
FROM Users AS U
LEFT JOIN Orders O ON U.user_id = O.buyer_id
	AND YEAR(O.order_date) = 2019
GROUP BY U.user_id
	,U.join_date
ORDER BY U.user_id

Query Output

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