Quarterly Sales Pivot by Product

SQL coding challenge · Difficulty: medium · Topic: CASE WHEN Pivoting · +100 XP

A retail company wants to compare each product's sales across the four quarters. Pivot the sales table so each product is one row and each quarter is its own column.

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

Tables

| column | type | description |
| --- | --- | --- |
| sales_id | INT | identifies the sale |
| product_id | INT | the product sold |
| quarter | TEXT | one of 'Q1', 'Q2', 'Q3', 'Q4' |
| amount | DECIMAL(10,2) | value of the sale |

Return one row per product_id with exactly these columns:

A product with no sales in a quarter must show 0.00, not NULL — a quarter with nothing sold is a zero total, not a missing one.

Row order is not graded, but the row count is: return one row per product and no others.

Example input

sales

| sales_id | product_id | quarter | amount |
| --- | --- | --- | --- |
| 1 | 101 | Q1 | 1500.00 |
| 2 | 101 | Q2 | 2000.00 |
| 3 | 101 | Q3 | 2500.00 |
| 4 | 101 | Q4 | 3000.00 |
| 5 | 102 | Q1 | 800.00 |
| 6 | 102 | Q3 | 1200.00 |
| 7 | 103 | Q2 | 500.00 |

Expected output

| product_id | Q1_sales | Q2_sales | Q3_sales | Q4_sales |
| --- | --- | --- | --- | --- |
| 101 | 1500.00 | 2000.00 | 2500.00 | 3000.00 |
| 102 | 800.00 | 0.00 | 1200.00 | 0.00 |
| 103 | 0.00 | 500.00 | 0.00 | 0.00 |

Product 101 sold in every quarter. Product 102 sold only in Q1 and Q3, so Q2 and Q4 read 0.00 rather than NULL. Product 103 sold once, in Q2.

What this SQL challenge teaches you

“Quarterly Sales Pivot by Product” is a medium-level SQL challenge focused on CASE WHEN Pivoting. Working through it gives you hands-on practice with CASE WHEN, pivot, aggregation — 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

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_id, so group by product_id alone. Grouping by quarter as well would give one row per product per quarter, 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 quarter becomes its own column, all computed from the same grouped rows.
  3. The quarter column already holds 'Q1' to 'Q4', so the CASE compares it directly - no date function is needed here. Write one aggregate per quarter and alias each to the required column name.
  4. A quarter 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_id 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_id, Q1_sales, Q2_sales, Q3_sales, Q4_sales. Row order is not graded, but the row count is: one row per product 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

Helpful resources

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 medium and covers CASE WHEN Pivoting.

Solve this challenge free on PySpark.in