E-Commerce Sales and Customer Insights Analysis

Project Overview

This SQL project involves the analysis of an e-commerce platform's sales data to extract meaningful insights about customer behavior, product performance, seller activity, and order fulfillment. The dataset contains key business entities such as customers, orders, products, sellers, reviews, and payments, allowing us to perform a detailed analysis of the e-commerce lifecycle. The goal of this project is to use SQL queries to answer important business questions, ranging from basic data retrieval to complex analysis of trends and patterns.

Key Areas of Analysis:

• Customer Behavior: Understanding how customers interact with the platform, including order frequency, geolocation-based insights, and review patterns.
• Product and Seller Performance: Identifying top-selling products, revenue contributions by sellers, and which product categories perform best.
• Sales and Payment Trends: Analyzing payment methods and their impact on order values, as well as the frequency of installments and the most common payment types.
• Order Fulfillment: Evaluating on-time deliveries and late orders to assess the operational efficiency of the platform.

Each query is designed to address a specific analytical need, with explanations provided to understand the logic behind them.

SQL Query Analysis

1. List of Unique States Where Sellers Are Located:

          SELECT DISTINCT SELLER_STATE as unique_state
          FROM Sellers;
    

2. Count of Orders Shipped in 2018:

          SELECT COUNT(order_id) as number_of_orders_2018
          FROM Orders
          WHERE YEAR(order_purchase_timestamp) = 2018;
    

3: Find the total sales amount for each product category:

          SELECT UPPER(PRODUCTS.product_category_name) as product_category,
              SUM("Order Payments".PAYMENT_VALUE) AS TOTAL_SALES
          FROM PRODUCTS
          JOIN "Order Items" ON PRODUCTS.PRODUCT_ID = "Order Items".PRODUCT_ID
          JOIN "Order Payments" ON "Order Payments".ORDER_ID = "Order Items".ORDER_ID
          GROUP BY product_category_name;
    

4: Calculate the percentage of orders delivered on weekends:

          SELECT ROUND(100.0 * SUM(
          CASE WHEN DATEPART(WEEKDAY, order_delivered_customer_date) IN (1, 7) THEN 1
          ELSE 0 END) / COUNT(*), 2) AS PERCENTAGE_DELIVERED_ON_WEEKENDS
          FROM ORDERS;
    

5:Count the number of orders placed by customers from each city:

          SELECT CUSTOMER_CITY, COUNT(ORDER_ID) AS ORDER_COUNT
          FROM CUSTOMERS
          JOIN ORDERS ON CUSTOMERS.CUSTOMER_ID = ORDERS.CUSTOMER_ID
          GROUP BY CUSTOMER_CITY
          ORDER BY ORDER_COUNT DESC;