g1101_1200.s1158_market_analysis_i.script.sql Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of leetcode-in-java Show documentation

Java-based LeetCode algorithm problem solutions, regularly updated

There is a newer version: 1.37

# Write your MySQL query statement below
# #Medium #Database #SQL_I_Day_9_Control_of_Flow
# #2023_09_03_Time_2511_ms_(78.92%)_Space_0B_(100.00%)
SELECT U.user_id AS buyer_id, U.join_date, IFNULL(USERS_ORDERED_IN_2019.orders_in_2019, 0) AS orders_in_2019 FROM Users U
LEFT JOIN (SELECT U.user_id AS user_id, COUNT(O.item_id) AS orders_in_2019 FROM Users U
LEFT JOIN Orders O ON O.buyer_id = U.user_id
WHERE YEAR(O.order_date) = 2019
GROUP BY U.user_id) AS USERS_ORDERED_IN_2019
ON U.user_id = USERS_ORDERED_IN_2019.user_id;