Objective

Analyze "AussieRetailers'" 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.

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.

TASMS

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 $(..$ applicable$).$

$11$:$47$

vucollaborate.vu$.$edu.au

$2.$ 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.

Determine the average sale quantity of products by category for sales made in the last quarter.

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

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

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

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

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

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

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

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

Calculate the average duration between complaint registration and resolution.

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

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

Additionally:

Design $5$ of your own SQL questions that : relevant to the dataset and aimed at uncovel insightful analytics to support business decis

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.

Determine the average sale quantity of products by category for sales made in the last quarter.

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

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

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

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

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

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

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

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

Calculate the average duration between complaint registration and resolution.

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

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

Additionally:

Design $5$ of your own SQL questions that : relevant to the dataset and aimed at uncover insightful analytics to support business decis