Burlington Stores, Inc. Interview Questions

Walmart is analyzing how product performance changes across American seasons: Spring, Summer, Fall, Winter. The company stores sales records and seasonal product assignments in separate tables. A product may belong to different seasonal collections in different years.

Write a SQL query to compute the total revenue for each (season, year) pair by joining the two tables on multiple columns. Your final output should include season, year and total_revenue (rounded to 2 decimals)

Sort results by year ascending, then by season order: Spring → Summer → Fall → Winter.

Notes

• The join must match on product_id AND year.
• Seasonal assignment varies by year.
• Revenue is computed from: quantity * price
• Every season/year combination appearing in the data must be included.

Schema

Input

seasonal_products table

sales table

Output

Example

Input

seasonal_products table

sales table

Output

Explanation

In Spring 2024, product 10 appears with total sales of (2 × 50) + (1 × 40) = 140. In Summer 2024, the same product is assigned to a different season within the same year, so its total is again 140. In Fall 2024, only product 20 appears, giving 3 × 30 = 90. In Winter 2024, only product 30 appears with 1 × 100 = 100.

The key detail:

A product may appear in multiple seasons in the same year, but JOIN must match both the product_id and year, ensuring that seasonal assignment is year-specific.