Allocating forecast to deeper granularity in SQL


You're given the following tables, called 'sales_forecast' and 'current_inventory'. The 'sales_forecast' table shows projected sales volume (in terms of # of units) by location by year. The 'current_inventory' shows the current # of units available by location and by color. The tables are shown below:

Table: sales_forecast

year location fcst_num_sales
2021 New York 113456
2022 New York 115675
2023 New York 117894
2024 New York 120113
2025 New York 122332
2021 Los Angeles 56728
2022 Los Angeles 57838
2023 Los Angeles 58947
2024 Los Angeles 60057
2025 Los Angeles 61166

Table: current_inventory

location product_color current_total_inventory
New York Red 45382
New York Blue 79419
New York Green 11346
Los Angeles Red 28364
Los Angeles Blue 17018
Los Angeles Green 34037

Using these tables, write a SQL query to take the current sales forecast and allocate it down to a projection of colors that will be sold at each location. For the simplicity of this example, you can use the current_inventory table to map the projected color proportions by each location. (e.g. if Blue represents XX% of New York's current inventory you can use that percent to allocate New York's total sales forecast down to the color Blue, and so on).

Click here to view these tables in an interactive SQL fiddle.


Access restricted

Subscribe to premium account to see the solution.

Get premium now