Assignment - Online Retail Sales Analysis

online-retail

Dataset Description: The dataset contains information about online retail sales transactions. It includes the following columns:

  1. Transaction_ID: Unique identifier for each transaction.
  2. Customer_ID: Unique identifier for each customer.
  3. Transaction_Date: Date of the transaction.
  4. Product_ID: Unique identifier for each product.
  5. Product_Name: Name of the product.
  6. Unit_Price: Price per unit of the product.
  7. Quantity: Number of units purchased in the transaction.
  8. Total_Price: Total price of the transaction.
  9. Country: Country where the transaction took place.
  10. Payment_Method: Payment method used for the transaction.

You can get a copy of the dataset here.

Instructions

For this assignment, you are expected to use the SQL editor below to write and run your SQL queries.

  1. Data Setup:

    • Create a table transactions.
    • Insert the dataset into the transactions table by using this script

  2. SQL Queries:

    Write SQL queries to perform the following tasks:

    • Retrieve the first 10 records from the dataset using the SELECT and LIMIT command.
    • Calculate the total number of transactions in the dataset using the SELECT and COUNT() fuction.
    • Find the total revenue generated from online sales using the SUM() function.
    • Identify the top 5 best-selling products by quantity sold using the SELECT, ORDER BY, and LIMIT command.
    • Calculate the average unit price of products sold ysing the AVG().
    • Determine the total sales revenue generated by each country.
    • Find the total quantity of products purchased by each customer.
    • Identify the most common payment method used for transactions.

  3. Data Management:

    • Create a new table named customer_info with columns for customer details such as 'Customer_ID', 'Customer_Name', and 'Email'.
      • Use the CREATE TABLE command for this.
    • Insert sample customer data into the newly created table.
      • Use the INSERT INTO command for this.
    • Update the dataset to include a new column 'Discount' and calculate a 10% discount for each transaction.
      • Use the UPDATE command for this.
    • Delete transactions with a quantity of 0 or negative values.
      • Use the DELETE command for this.

  4. Joining Tables:

    • Perform inner joins between the original dataset and the 'Customer_Info' table based on the 'Customer_ID' column.
    • Write a query to retrieve transaction details along with corresponding customer information.

  5. SQL Functions:

    • Utilize SQL functions to...
      • Calculate the total price for each transaction (Unit_Price * Quantity).
      • Extract the month and year from the 'Transaction_Date' column.
      • Concatenate the 'Product_Name' and 'Country' columns to create a new column 'Product_Location'.

Submission Guidelines:

  • Download your SQL query file to your computer.
    • Click on elipsis icon at the top-right of your onecompiler editor and select download. ./databases-and-sql/download.png
  • Attach the downloaded file below and submit.
    • There is no external submission for this project. Please ignore the Incomplete status for external submission.