Advanced Querying in Factory Management System with SQLAlchemy

Objective: The objective of this assignment is to practice performing complex queries using SQLAlchemy in a factory management system. This includes utilizing Group By, Having, Joins, and Subqueries to extract meaningful insights and data from the database.

Problem Statement: You are tasked with implementing advanced querying operations in the factory management system to analyze employee performance, track production efficiency, and gain insights into customer behavior. This involves executing queries that involve grouping, filtering, joining multiple tables, and using subqueries to extract relevant information from the database.

Task 1: Analyze Employee Performance

Write a query to calculate the total quantity of products each employee has produced.
Group the results by employee name.
Use the Group By clause to group the data and the Sum function to calculate the total quantity.

Task 2: Identify Top Selling Products

Write a query to find the top-selling products based on the total quantity ordered.
Group the results by product name.
Use the Group By clause to group the data, the Sum function to calculate the total quantity ordered for each product, and the Order By clause to sort the results in descending order of quantity.

Task 3: Determine Customer Lifetime Value

Write a query to calculate the total value of orders placed by each customer.
Group the results by customer name.
Use the Group By clause to group the data and the Sum function to calculate the total order value.
Filter out customers with a total order value less than a certain threshold using the Having clause.

Task 4: Evaluate Production Efficiency

Write a query to calculate the total quantity produced for each product on a specific date.
Join the Production and Product tables on the product_id column.
Use a subquery to filter the production records for the specified date.
Group the results by product name.

Expected Outcomes:

Upon completing this assignment, students should achieve the following outcomes:

Proficiency in writing complex queries using SQLAlchemy to analyze various aspects of the factory management system.
Ability to utilize Group By, Having, Joins, and Subqueries to extract meaningful insights and data from the database.
Understanding of how to perform advanced querying operations to evaluate employee performance, track production efficiency, identify top-selling products, and determine customer lifetime value.

BE Module 13 Lesson 2: Assignment