Table: Product
+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | | unit_price | int | +--------------+---------+ product_id is the primary key (column with unique values) of this table. Each row of this table indicates the name and the price of each product.
Table: Sales
+-------------+---------+ | Column Name | Type | +-------------+---------+ | seller_id | int | | product_id | int | | buyer_id | int | | sale_date | date | | quantity | int | | price | int | +-------------+---------+ This table can have duplicate rows. product_id is a foreign key (reference column) to the Product table. Each row of this table contains some information about one sale.
Write a solution to report the products that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Product table: +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+ Sales table: +-----------+------------+----------+------------+----------+-------+ | seller_id | product_id | buyer_id | sale_date | quantity | price | +-----------+------------+----------+------------+----------+-------+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 2 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 4 | 2019-05-13 | 2 | 2800 | +-----------+------------+----------+------------+----------+-------+ Output: +-------------+--------------+ | product_id | product_name | +-------------+--------------+ | 1 | S8 | +-------------+--------------+ Explanation: The product with id 1 was only sold in the spring of 2019. The product with id 2 was sold in the spring of 2019 but was also sold after the spring of 2019. The product with id 3 was sold after spring 2019. We return only product 1 as it is the product that was only sold in the spring of 2019.
This approach involves using SQL to join the Product and Sales tables on the product_id column. We filter the sales to include only those made in the first quarter of 2019 and use GROUP BY to ensure the product was sold only during this period. We left join again to ensure there are no sales for these products outside this period.
This Python solution uses Panda's DataFrame to filter sales within the first quarter of 2019 and excludes any products sold outside this period. It then finds products only sold in the specified period by subtracting sets of product IDs.
JavaScript
Time Complexity: O(n + m), where n is the number of rows in the Product table and m is the number in the Sales table. Space Complexity: O(n + m) for storing filter results.
This approach involves using a subquery to find product IDs that have sales exclusively in the first quarter of 2019. We first identify sales in this period and ensure there are no other sales for these products outside this period using NOT EXISTS with filtered sales.
This SQL solution uses a subquery to verify that the product was sold in Q1 2019 and ensures no sales exist for the product outside this period.
Time Complexity: O(n*m), Space Complexity: O(1) when using database indexing.
| Approach | Complexity |
|---|---|
| Using SQL JOIN and GROUP BY | Time Complexity: O(n + m), where n is the number of rows in the Product table and m is the number in the Sales table. Space Complexity: O(n + m) for storing filter results. |
| Using SQL Subquery | Time Complexity: O(n*m), Space Complexity: O(1) when using database indexing. |
Product Sales Analysis III | Leetcode 1070 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 5,394 views views
Watch 9 more video solutions →Practice Sales Analysis III with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor