Product Sales Analysis III

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You have two tables: Sales and Product. Each row in Sales records a sale of a product in a given year. Write a SQL query to select the product_id, the year of its first sale (first_year), the quantity sold, and the price for each product's first year of sale. Return the result in any order.

Database Schema

Sales

Column Name	Type	Description
sale_id	INT	Part of primary key
product_id	INT	FK to Product
year	INT	Year of sale (part of primary key)
quantity	INT	Quantity sold
price	INT	Price per unit

Product

Column Name	Type	Description
product_id	INT	Primary key
product_name	VARCHAR	Name of product

Example

Sales

sale_id	product_id	year	quantity	price
1	100	2008	10	5000
2	100	2009	12	5000
7	200	2011	15	9000

Product

product_id	product_name
100	Nokia
200	Apple
300	Samsung

Output

product_id	first_year	quantity	price
100	2008	10	5000
200	2011	15	9000

Explanation:

Product 100 (Nokia) first appeared in 2008 with quantity=10, price=5000. Product 200 (Apple) first appeared in 2011 with quantity=15, price=9000. Product 300 has no sales.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.