TABLES OVERVIEW

Sales: Records each sale, including sale ID$,$ date, product ID$,$ quantity, customer ID$,$ and branch ID$.$

Customer Details: Stores customer information, including customer ID$,$ name, contact details, and loyalty program status.

Product Details: Contains product information, such as product ID$,$ name, category, price, and stock levels.

Staff Shift Details: Logs staff shifts, including staff ID$,$ branch ID$,$ shift date, start time, and end time.

Complaint Details: Records customer complaints, with complaint ID$,$ customer ID$,$ product ID$,$ complaint date, and resolution status.

Objective

Analyze "AussieRetailers"$\text{'}$ operational data using advanced SQL queries, focusing on sales performance, customer satisfaction, and staff efficiency. The analysis should culminate in a detailed report with insights, trends, and recommendations.

Data Preparation:

Draw an ERD diagram with foreign and primary keys for each table $($entity$).$

Normalize the data if necessary to eliminate redundancy and ensure database efficiency $($if applicable$).$

Analysis Requirements:

List all products and their categories with sales greater than $100$ units.

Calculate the total revenue per branch, considering the quantity sold and product prices.

Identify customers who have made purchases in more than $3$ different branches.

category for sales made in the last quarter.

$5.$ Rank products within each category based on the total sales quantity using a window function.

$6.$ Show the month$-$over$-$month percentage growth in sales for the top $5$ products by total quantity sold.

$7.$ Find all products that have not been sold in the last $6$ months $($from June to December $2022)$ but have stock levels above $50.$

$8.$ Calculate the total number of complaints lodged against products in each category.

$9.$ List the top $10$ customers by total spending and show their most frequently bought product category.

$10.$ Identify days of the week with the highest sales transactions volume.

$11.$ Determine the branch with the lowest stock levels across all products.

$12.$ Analyze the correlation between loyalty program status and the average transaction value per customer.

$13.$ Calculate the average duration between complaint registration and resolution.

$14.$ Identify staff members with shifts longer than $8$ hours and list their corresponding branches and shift dates.

$15.$ Find the product with the highest number of complaints and detail the nature of these complaints.