Monthly Sales Pivot by Product Type

SQL coding challenge · Difficulty: hard · Topic: CASE WHEN Pivoting · +200 XP

A retail company tracks every sale in a sales table. Pivot it so each product

type is one row and each month is its own column.

Engine: queries run on MySQL 8.0, so use MySQL-compatible syntax and functions.

Tables

| column | type | description |
| --- | --- | --- |
| sale_id | INT | identifies the sale |
| product_type | TEXT | the category sold |
| sale_date | DATE | when the sale happened |
| amount | DECIMAL(10,2) | value of the sale |

Return one row per product_type with exactly these columns:

• product_type
• Jan, Feb, Mar — the total amount sold in that month

The data covers January to March only, so those are the only month columns

required. A product type with no sales in a month must show 0.00, not

NULL — an empty month is a zero total, not a missing one.

Row order is not graded, but the row count is: return one row per product type

and no others.

Example input

sales

| sale_id | product_type | sale_date | amount |
| --- | --- | --- | --- |
| 1 | Electronics | 2024-01-05 | 250.00 |
| 2 | Electronics | 2024-02-15 | 180.00 |
| 3 | Clothing | 2024-01-18 | 320.00 |
| 4 | Clothing | 2024-03-10 | 95.00 |
| 5 | Furniture | 2024-01-22 | 410.00 |
| 6 | Electronics | 2024-03-15 | 220.00 |
| 7 | Clothing | 2024-02-20 | 75.00 |

Expected output

| product_type | Jan | Feb | Mar |
| --- | --- | --- | --- |
| Clothing | 320.00 | 75.00 | 95.00 |
| Electronics | 250.00 | 180.00 | 220.00 |
| Furniture | 410.00 | 0.00 | 0.00 |

Clothing sold in all three months. Furniture sold only in January, so February

and March are 0.00 rather than NULL.

What this SQL challenge teaches you

“Monthly Sales Pivot by Product Type” is a hard-level SQL challenge focused on CASE WHEN Pivoting. Working through it gives you hands-on practice with CASE WHEN, pivot, aggregate functions, date, group by — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

Concepts covered

• CASE WHEN
• pivot
• aggregate functions
• date
• group by

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

1. The result has one row per product_type, so group by product_type alone. Grouping by month as well would give one row per product per month, which is the shape a pivot exists to avoid.
2. MySQL has no PIVOT clause. The usual approach is conditional aggregation: put a CASE inside the aggregate so each month becomes its own column, all computed from the same grouped rows.
3. MONTH(sale_date) returns 1-12 and DATE_FORMAT(sale_date, '%m') returns '01'-'12'. Either can drive the CASE - pick one and use it consistently for all three months.
4. A month with no sale must read 0.00, not NULL. Both ELSE 0 inside the CASE and COALESCE(SUM(...), 0) around it work here, because every product_type in the result already has at least one row in sales, so SUM is never asked to aggregate zero rows. COALESCE is the habit worth keeping for queries where a group genuinely can be empty - a LEFT JOIN from a product list, say - since ELSE 0 would not save you there.
5. Return exactly product_type, Jan, Feb, Mar - the data covers January to March only. Row order is not graded, but the row count is: one row per product type and no others.

How to practise it on PySpark.in

Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related SQL challenges

• Quarterly Sales Pivot by Product
• Top 3 Products per Category
• Running Total Revenue
• Find Duplicate Emails
• Median Salary per Department
• Second Highest Salary

Helpful resources

• All SQL practice questions
• Open the notebook

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated hard and covers CASE WHEN Pivoting.

Solve this challenge free on PySpark.in