mirror of
https://gitee.com/coder-xiaomo/leetcode-problemset
synced 2025-09-05 15:31:43 +08:00
144 lines
5.4 KiB
HTML
144 lines
5.4 KiB
HTML
<p>Table: <code>sales</code></p>
|
|
|
|
<pre>
|
|
+---------------+---------+
|
|
| Column Name | Type |
|
|
+---------------+---------+
|
|
| sale_id | int |
|
|
| product_id | int |
|
|
| sale_date | date |
|
|
| quantity | int |
|
|
| price | decimal |
|
|
+---------------+---------+
|
|
sale_id is the unique identifier for this table.
|
|
Each row contains information about a product sale including the product_id, date of sale, quantity sold, and price per unit.
|
|
</pre>
|
|
|
|
<p>Table: <code>products</code></p>
|
|
|
|
<pre>
|
|
+---------------+---------+
|
|
| Column Name | Type |
|
|
+---------------+---------+
|
|
| product_id | int |
|
|
| product_name | varchar |
|
|
| category | varchar |
|
|
+---------------+---------+
|
|
product_id is the unique identifier for this table.
|
|
Each row contains information about a product including its name and category.
|
|
</pre>
|
|
|
|
<p>Write a solution to find the most popular product category for each season. The seasons are defined as:</p>
|
|
|
|
<ul>
|
|
<li><strong>Winter</strong>: December, January, February</li>
|
|
<li><strong>Spring</strong>: March, April, May</li>
|
|
<li><strong>Summer</strong>: June, July, August</li>
|
|
<li><strong>Fall</strong>: September, October, November</li>
|
|
</ul>
|
|
|
|
<p>The <strong>popularity</strong> of a <strong>category</strong> is determined by the <strong>total quantity sold</strong> in that <strong>season</strong>. If there is a <strong>tie</strong>, select the category with the highest <strong>total revenue</strong> (<code>quantity × price</code>).</p>
|
|
|
|
<p>Return <em>the result table ordered by season in <strong>ascending</strong> order</em>.</p>
|
|
|
|
<p>The result format is in the following example.</p>
|
|
|
|
<p> </p>
|
|
<p><strong class="example">Example:</strong></p>
|
|
|
|
<div class="example-block">
|
|
<p><strong>Input:</strong></p>
|
|
|
|
<p>sales table:</p>
|
|
|
|
<pre class="example-io">
|
|
+---------+------------+------------+----------+-------+
|
|
| sale_id | product_id | sale_date | quantity | price |
|
|
+---------+------------+------------+----------+-------+
|
|
| 1 | 1 | 2023-01-15 | 5 | 10.00 |
|
|
| 2 | 2 | 2023-01-20 | 4 | 15.00 |
|
|
| 3 | 3 | 2023-03-10 | 3 | 18.00 |
|
|
| 4 | 4 | 2023-04-05 | 1 | 20.00 |
|
|
| 5 | 1 | 2023-05-20 | 2 | 10.00 |
|
|
| 6 | 2 | 2023-06-12 | 4 | 15.00 |
|
|
| 7 | 5 | 2023-06-15 | 5 | 12.00 |
|
|
| 8 | 3 | 2023-07-24 | 2 | 18.00 |
|
|
| 9 | 4 | 2023-08-01 | 5 | 20.00 |
|
|
| 10 | 5 | 2023-09-03 | 3 | 12.00 |
|
|
| 11 | 1 | 2023-09-25 | 6 | 10.00 |
|
|
| 12 | 2 | 2023-11-10 | 4 | 15.00 |
|
|
| 13 | 3 | 2023-12-05 | 6 | 18.00 |
|
|
| 14 | 4 | 2023-12-22 | 3 | 20.00 |
|
|
| 15 | 5 | 2024-02-14 | 2 | 12.00 |
|
|
+---------+------------+------------+----------+-------+
|
|
</pre>
|
|
|
|
<p>products table:</p>
|
|
|
|
<pre class="example-io">
|
|
+------------+-----------------+----------+
|
|
| product_id | product_name | category |
|
|
+------------+-----------------+----------+
|
|
| 1 | Warm Jacket | Apparel |
|
|
| 2 | Designer Jeans | Apparel |
|
|
| 3 | Cutting Board | Kitchen |
|
|
| 4 | Smart Speaker | Tech |
|
|
| 5 | Yoga Mat | Fitness |
|
|
+------------+-----------------+----------+
|
|
</pre>
|
|
|
|
<p><strong>Output:</strong></p>
|
|
|
|
<pre class="example-io">
|
|
+---------+----------+----------------+---------------+
|
|
| season | category | total_quantity | total_revenue |
|
|
+---------+----------+----------------+---------------+
|
|
| Fall | Apparel | 10 | 120.00 |
|
|
| Spring | Kitchen | 3 | 54.00 |
|
|
| Summer | Tech | 5 | 100.00 |
|
|
| Winter | Apparel | 9 | 110.00 |
|
|
+---------+----------+----------------+---------------+
|
|
</pre>
|
|
|
|
<p><strong>Explanation:</strong></p>
|
|
|
|
<ul>
|
|
<li><strong>Fall (Sep, Oct, Nov):</strong>
|
|
|
|
<ul>
|
|
<li>Apparel: 10 items sold (6 Jackets in Sep, 4 Jeans in Nov), revenue $120.00 (6×$10.00 + 4×$15.00)</li>
|
|
<li>Fitness: 3 Yoga Mats sold in Sep, revenue $36.00</li>
|
|
<li>Most popular: Apparel with highest total quantity (10)</li>
|
|
</ul>
|
|
</li>
|
|
<li><strong>Spring (Mar, Apr, May):</strong>
|
|
<ul>
|
|
<li>Kitchen: 3 Cutting Boards sold in Mar, revenue $54.00</li>
|
|
<li>Tech: 1 Smart Speaker sold in Apr, revenue $20.00</li>
|
|
<li>Apparel: 2 Warm Jackets sold in May, revenue $20.00</li>
|
|
<li>Most popular: Kitchen with highest total quantity (3) and highest revenue ($54.00)</li>
|
|
</ul>
|
|
</li>
|
|
<li><strong>Summer (Jun, Jul, Aug):</strong>
|
|
<ul>
|
|
<li>Apparel: 4 Designer Jeans sold in Jun, revenue $60.00</li>
|
|
<li>Fitness: 5 Yoga Mats sold in Jun, revenue $60.00</li>
|
|
<li>Kitchen: 2 Cutting Boards sold in Jul, revenue $36.00</li>
|
|
<li>Tech: 5 Smart Speakers sold in Aug, revenue $100.00</li>
|
|
<li>Most popular: Tech and Fitness both have 5 items, but Tech has higher revenue ($100.00 vs $60.00)</li>
|
|
</ul>
|
|
</li>
|
|
<li><strong>Winter (Dec, Jan, Feb):</strong>
|
|
<ul>
|
|
<li>Apparel: 9 items sold (5 Jackets in Jan, 4 Jeans in Jan), revenue $110.00</li>
|
|
<li>Kitchen: 6 Cutting Boards sold in Dec, revenue $108.00</li>
|
|
<li>Tech: 3 Smart Speakers sold in Dec, revenue $60.00</li>
|
|
<li>Fitness: 2 Yoga Mats sold in Feb, revenue $24.00</li>
|
|
<li>Most popular: Apparel with highest total quantity (9) and highest revenue ($110.00)</li>
|
|
</ul>
|
|
</li>
|
|
</ul>
|
|
|
|
<p>The result table is ordered by season in ascending order.</p>
|
|
</div>
|