Analyze Product Sales by Region

SQL coding challenge · Difficulty: easy · Topic: Aggregation · +50 XP

Problem

You are given a table named sales_data that contains product sales across different regions.

Write a query that returns one summary row per product.

For every product, return:

product: the product name
total_qty: total quantity sold across all regions
west_qty: total quantity sold only in the West region
region_count: number of distinct regions where the product was sold
order_count: total number of orders for that product

Order the result by product in ascending order.

Table: sales_data

| Column | Type | Description |
|---|---|---|
| `order_id` | INT | Unique order identifier |
| `product` | VARCHAR | Product name |
| `region` | VARCHAR | Region where the sale happened |
| `quantity` | INT | Quantity sold |

Example Input

| order_id | product | region | quantity |
|---|---|---|---|
| 1 | Laptop | East | 2 |
| 2 | Mobile | West | 5 |
| 3 | Laptop | East | 1 |
| 4 | Tablet | North | 3 |
| 5 | Mobile | West | 2 |
| 6 | Laptop | West | 4 |

Expected Output

| product | total_qty | west_qty | region_count | order_count |
|---|---|---|---|---|
| Laptop | 7 | 4 | 2 | 3 |
| Mobile | 7 | 7 | 1 | 2 |
| Tablet | 3 | 0 | 1 | 1 |

Explanation

For Laptop, total quantity is 2 + 1 + 4 = 7, West quantity is 4, it was sold in East and West, and it has 3 orders.

For Mobile, total quantity is 5 + 2 = 7, all sales are from West, it was sold in one region, and it has 2 orders.

For Tablet, total quantity is 3, there is no West sale so west_qty = 0, it was sold in one region, and it has 1 order.

Constraints

order_id is unique.
quantity is a positive integer.
product and region are not NULL.
A product may appear in multiple orders and multiple regions.
A product may have no sales in the West region.

Concepts Tested

GROUP BY
SUM
COUNT
COUNT(DISTINCT ...)
Conditional aggregation with CASE WHEN
Sorting with ORDER BY

What this SQL challenge teaches you

“Analyze Product Sales by Region” is a easy-level SQL challenge focused on Aggregation. Working through it gives you hands-on practice with GROUP BY, SUM, COUNT, COUNT DISTINCT, CASE WHEN, 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

GROUP BY
SUM
COUNT
COUNT DISTINCT
CASE WHEN
Aggregation

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:

Use GROUP BY product to produce one row per product.
Use SUM(CASE WHEN region = 'West' THEN quantity ELSE 0 END) for west_qty.
Use COUNT(DISTINCT region) for the number of regions.

Where this comes up

Variations of this problem have been reported in interviews at Amazon, Flipkart, Walmart, Target. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

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 easy and covers Aggregation.

Solve this challenge free on PySpark.in