Market Analysis I
<p>Table: <code>Users</code></p>

<pre>
+----------------+---------+
| 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.
</pre>

<p>&nbsp;</p>

<p>Table: <code>Orders</code></p>

<pre>
+---------------+---------+
| 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.
</pre>

<p>&nbsp;</p>

<p>Table: <code>Items</code></p>

<pre>
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+
item_id is the primary key (column with unique values) of this table.
</pre>

<p>&nbsp;</p>

<p>Write a solution&nbsp;to find for each user, the join date and the number of orders they made as a buyer in <code>2019</code>.</p>

<p>Return the result table in <strong>any order</strong>.</p>

<p>The&nbsp;result format is in the following example.</p>

<p>&nbsp;</p>
<p><strong class="example">Example 1:</strong></p>

<pre>
<strong>Input:</strong> 
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 | 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 table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+
<strong>Output:</strong> 
+-----------+------------+----------------+
| 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              |
+-----------+------------+----------------+
</pre>

[]
['Create table If Not Exists Users (user_id int, join_date date, favorite_brand varchar(10))', 'Create table If Not Exists Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int)', 'Create table If Not Exists Items (item_id int, item_brand varchar(10))', 'Truncate table Users', "insert into Users (user_id, join_date, favorite_brand) values ('1', '2018-01-01', 'Lenovo')", "insert into Users (user_id, join_date, favorite_brand) values ('2', '2018-02-09', 'Samsung')", "insert into Users (user_id, join_date, favorite_brand) values ('3', '2018-01-19', 'LG')", "insert into Users (user_id, join_date, favorite_brand) values ('4', '2018-05-21', 'HP')", 'Truncate table Orders', "insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('1', '2019-08-01', '4', '1', '2')", "insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('2', '2018-08-02', '2', '1', '3')", "insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('3', '2019-08-03', '3', '2', '3')", "insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('4', '2018-08-04', '1', '4', '2')", "insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('5', '2018-08-04', '1', '3', '4')", "insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('6', '2019-08-05', '2', '2', '4')", 'Truncate table Items', "insert into Items (item_id, item_brand) values ('1', 'Samsung')", "insert into Items (item_id, item_brand) values ('2', 'Lenovo')", "insert into Items (item_id, item_brand) values ('3', 'LG')", "insert into Items (item_id, item_brand) values ('4', 'HP')"]
[{"value": "mysql", "text": "MySQL", "defaultCode": "# Write your MySQL query statement below\n"}, {"value": "mssql", "text": "MS SQL Server", "defaultCode": "/* Write your T-SQL query statement below */\n"}, {"value": "oraclesql", "text": "Oracle", "defaultCode": "/* Write your PL/SQL query statement below */\n"}, {"value": "pythondata", "text": "Pandas", "defaultCode": "import pandas as pd\n\ndef market_analysis(users: pd.DataFrame, orders: pd.DataFrame, items: pd.DataFrame) -> pd.DataFrame:\n    "}, {"value": "postgresql", "text": "PostgreSQL", "defaultCode": "-- Write your PostgreSQL query statement below\n"}]
{"mysql": ["Create table If Not Exists Users (user_id int, join_date date, favorite_brand varchar(10))", "Create table If Not Exists Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int)", "Create table If Not Exists Items (item_id int, item_brand varchar(10))"], "mssql": ["Create table Users (user_id int, join_date date, favorite_brand varchar(10))", "Create table Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int)", "Create table Items (item_id int, item_brand varchar(10))"], "oraclesql": ["Create table Users (user_id int, join_date date, favorite_brand varchar(10))", "Create table Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int)", "Create table Items (item_id int, item_brand varchar(10))", "ALTER SESSION SET nls_date_format='YYYY-MM-DD'"], "database": true, "name": "market_analysis", "pythondata": ["Users = pd.DataFrame([], columns=['user_id', 'join_date', 'favorite_brand']).astype({'user_id':'Int64', 'join_date':'datetime64[ns]', 'favorite_brand':'object'})", "Orders = pd.DataFrame([], columns=['order_id', 'order_date', 'item_id', 'buyer_id', 'seller_id']).astype({'order_id':'Int64', 'order_date':'datetime64[ns]', 'item_id':'Int64', 'buyer_id':'Int64', 'seller_id':'Int64'})", "Items = pd.DataFrame([], columns=['item_id', 'item_brand']).astype({'item_id':'Int64', 'item_brand':'object'})"], "postgresql": ["\nCreate table If Not Exists Users (user_id int, join_date date, favorite_brand varchar(10))", "Create table If Not Exists Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int)", "Create table If Not Exists Items (item_id int, item_brand varchar(10))"], "database_schema": {"Users": {"user_id": "INT", "join_date": "DATE", "favorite_brand": "VARCHAR(10)"}, "Orders": {"order_id": "INT", "order_date": "DATE", "item_id": "INT", "buyer_id": "INT", "seller_id": "INT"}, "Items": {"item_id": "INT", "item_brand": "VARCHAR(10)"}}}
{"mysql": ["MySQL", "<p><code>MySQL 8.0</code>.</p>"], "mssql": ["MS SQL Server", "<p><code>mssql server 2019</code>.</p>"], "oraclesql": ["Oracle", "<p><code>Oracle Sql 11.2</code>.</p>"], "pythondata": ["Pandas", "<p>Python 3.10 with Pandas 2.2.2 and NumPy 1.26.4</p>"], "postgresql": ["PostgreSQL", "<p>PostgreSQL 16</p>"]}
['Database']