8000 Create LeetCode 1084. Sales Analysis III · priyesh0453/SQL-LeetCode@ede2736 · GitHub
[go: up one dir, main page]

Skip to content

Commit ede2736

Browse files
committed
Create LeetCode 1084. Sales Analysis III
1 parent 690daac commit ede2736

File tree

1 file changed

+83
-0
lines changed

1 file changed

+83
-0
lines changed

Easy/1084. Sales Analysis III.sql

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,83 @@
1+
/*
2+
Table: Product
3+
4+
+--------------+---------+
5+
| Column Name | Type |
6+
+--------------+---------+
7+
| product_id | int |
8+
| product_name | varchar |
9+
| unit_price | int |
10+
+--------------+---------+
11+
product_id is the primary key of this table.
12+
Each row of this table indicates the name and the price of each product.
13+
Table: Sales
14+
15+
+-------------+---------+
16+
| Column Name | Type |
17+
+-------------+---------+
18+
| seller_id | int |
19+
| product_id | int |
20+
| buyer_id | int |
21+
| sale_date | date |
22+
| quantity | int |
23+
| price | int |
24+
+-------------+---------+
25+
This table has no primary key, it can have repeated rows.
26+
product_id is a foreign key to the Product table.
27+
Each row of this table contains some information about one sale.
28+
29+
30+
Required Query:
31+
32+
Report the products that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.
33+
34+
35+
SQL Schema:
36+
37+
Create table If Not Exists Product (product_id int, product_name varchar(10), unit_price int)
38+
Create table If Not Exists Sales (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int)
39+
Truncate table Product
40+
insert into Product (product_id, product_name, unit_price) values ('1', 'S8', '1000')
41+
insert into Product (product_id, product_name, unit_price) values ('2', 'G4', '800')
42+
insert into Product (product_id, product_name, unit_price) values ('3', 'iPhone', '1400')
43+
Truncate table Sales
44+
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '1', '1', '2019-01-21', '2', '2000')
45+
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '2', '2', '2019-02-17', '1', '800')
46+
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('2', '2', '3', '2019-06-02', '1', '800')
47+
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '4', '2019-05-13', '2', '2800')
48+
49+
50+
Example:
51+
52+
Input:
53+
Product table:
54+
+------------+--------------+------------+
55+
| product_id | product_name | unit_price |
56+
+------------+--------------+------------+
57+
| 1 | S8 | 1000 |
58+
| 2 | G4 | 800 |
59+
| 3 | iPhone | 1400 |
60+
+------------+--------------+------------+
61+
Sales table:
62+
+-----------+------------+----------+------------+----------+-------+
63+
| seller_id | product_id | buyer_id | sale_date | quantity | price |
64+
+-----------+------------+----------+------------+----------+-------+
65+
| 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
66+
| 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
67+
| 2 | 2 | 3 | 2019-06-02 | 1 | 800 |
68+
| 3 | 3 | 4 | 2019-05-13 | 2 | 2800 |
69+
+-----------+------------+----------+------------+----------+-------+
70+
Output:
71+
+-------------+--------------+
72+
| product_id | product_name |
73+
+-------------+--------------+
74+
| 1 | S8 |
75+
+-------------+--------------+
76+
*/
77+
78+
-- Solution:
79+
SELECT Product.product_id, product_name
80+
FROM Product JOIN Sales
81+
ON Product.product_id = Sales.product_id
82+
GROUP BY Product.product_id
83+
HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'

0 commit comments

Comments
 (0)
0