calculating actual sales achievement value

Information: Calculating the sales values based on territory of each brand for a each month is vital to understand their performance and growth in commercial space.

 

Requirement: Create a Databricks SQL query to calculate the actual_value for each brand_name,country_code and territory_id by summing the sales_value from the t3_itm_territory_sales table and the sales_net_price_local from the t3_ttm_territory_sales table. Join the tables on the following columns:

 

country_code* (from both tables),

brand_name* (from both tables),

The month and year* are extracted from the sales_month column in the t3_itm_territory_sales table and the fiscal_date column in the t3_ttm_territory_sales table.

territory_id* (from both tables).

 

Ensure that the source_system_name in both tables exist in the control_table under the source_system column. If the source_system_name is not found in the control table, exclude those brand_name records from the actual_value calculation.

 

 

 

Unity catalog information: purgo_playground.t3_ttm_territory_sales, purgo_playground.t3_itm_territory_sales and purgo_playground.control_table

 

Expected Output: Create a Databricks SQL query