Count Every User’s Buyer Orders during 2019

### Problem Return buyer_id, join_date and orders_in_2019 for every Users row. Count only orders made by that user as buyer during calendar year 2019, including zero when none match. Result order is unrestricted; seller activity is not the requested count. ### Tables Table: `Users` ```text +----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ user_id is the primary key (column with unique values) of this table. This table has the info of the users of an online shopping website where users can sell and buy items. ``` Table: `Orders` ```text +---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | item_id | int | | buyer_id | int | | seller_id | int | +---------------+---------+ order_id is the primary key (column with unique values) of this table. item_id is a foreign key (reference column) to the Items table. buyer_id and seller_id are foreign keys to the Users table. ``` Table: `Items` ```text +---------------+---------+ | Column Name | Type | +---------------+---------+ | item_id | int | | item_brand | varchar | +---------------+---------+ item_id is the primary key (column with unique values) of this table. ``` ### Example ```text Users (user_id,join_date,favorite_brand): (1,2018-01-01,A), (2,2018-03-01,B), (3,2018-04-01,A) Items (item_id,item_brand): (8,A) Orders (order_id,order_date,item_id,buyer_id,seller_id): (1,2019-01-01,8,1,2), (2,2019-12-31,8,1,3), (3,2020-01-01,8,2,1) Output (buyer_id,join_date,orders_in_2019): (1,2018-01-01,2), (2,2018-03-01,0), (3,2018-04-01,0) ``` ### What a Strong Answer Covers - Include every user, even when they placed no orders that year. - Count only buyer orders in 2019 and return the requested fields. ### Follow-up Questions - How would you report separate buyer and seller counts? - How would the result change for a rolling 12-month window?