Unguided Assignment - Views & Optimization

[akash-coded]

Sure, let's create some complex scenarios related to views and their practical applications. The scenarios will involve real-world business situations that may be encountered in a typical data engineering workflow.

1. Scenario 1 - Analyzing Sales Trends: You are a Data Engineer at ClassicModels, and the sales team has asked you to create a view that allows them to quickly analyze sales trends. The view should include total sales per customer, per year, and per country.

2. Scenario 2 - Product Performance Tracking: The Product Management team wants a view that shows the total quantity ordered for each product line per quarter. They will use this view to track the performance of each product line and make decisions about future product development.

3. Scenario 3 - Employee Performance Analysis: The HR team would like a view that shows the total sales per employee. This will be used to track employee performance and determine bonuses.

4. Scenario 4 - Inventory Management: The inventory management team at ClassicModels needs a view that shows the quantity in stock for each product, along with the number of orders and total quantity ordered. This will be used to manage inventory levels and decide when to reorder products.

5. Compound Workflow Scenario - Customer and Employee Analysis: In this scenario, you will create a workflow that involves both the ClassicModels and HR databases. The goal is to create a view that shows the total sales per customer, along with the employee responsible for that customer (from the ClassicModels database), and the employee's department and manager (from the HR database).

Assignment:

Based on the above scenarios, create the necessary views using SQL. Test your views with appropriate queries and ensure they return the correct data. Make sure your views are optimized and follow best practices for view creation.

Remember, these views should be created in a way that they can be used in other SQL queries or by BI tools for further analysis.

In the compound workflow scenario, you will need to use JOINs to combine data from the two databases. Make sure you understand how to do this, and ensure the combined data is accurate and meaningful.

For each view, also consider how you would handle updates to the underlying data. Would you need to update the view, or is it automatically updated? What are the performance implications of these updates?

After you've created the views, think about how you would explain them to the teams that requested them. Can you explain what each view shows and how it can be used to answer business questions? Can you explain any limitations or caveats with each view?

This exercise will not only help you understand how to create and use views in MySQL, but also how to work with different teams and understand their data needs. It will also give you a sense of the kind of complex, real-world scenarios you might encounter in a data engineering role.