Questions - Ecommerce.sql

-- ============================================================
-- E-Commerce Store Management System
-- SQL Questions & Views
-- ============================================================

USE Ecommerce;
GO

-- ============================================================
-- Q1) Customer Master View
-- ============================================================

CREATE VIEW e_commerce.vw_CustomerMaster AS
SELECT
    c.customer_id                          AS CustomerID,
    c.FullName,
    c.Email,
    c.phone                                AS Phone,
    a.city                                 AS City,
    MIN(o.order_date)                      AS 'Registration/FirstOrder Date',
    COUNT(DISTINCT o.order_id)             AS TotalOrders,
    ISNULL(SUM(o.order_amount), 0)         AS TotalSpend
FROM e_commerce.customer c
LEFT JOIN e_commerce.address a
    ON c.customer_id = a.customer_id
LEFT JOIN e_commerce.order_table o
    ON c.customer_id = o.customer_id
LEFT JOIN e_commerce.payment p
    ON o.order_id = p.order_id
GROUP BY
    c.customer_id,
    c.FullName,
    c.Email,
    c.phone,
    a.city;
GO

-- Top 20 customers by TotalSpend
SELECT TOP 20
    CustomerID,
    FullName,
    City,
    TotalOrders,
    TotalSpend
FROM e_commerce.vw_CustomerMaster
ORDER BY TotalSpend DESC;
GO


-- ============================================================
-- Q2) Product Catalog View (with Stock Status)
-- ============================================================

CREATE VIEW e_commerce.vw_ProductCatalog AS
SELECT
    p.product_id                           AS ProductID,
    p.SKU,
    p.product_name                         AS ProductName,
    ISNULL(c.category_name, 'Uncategorized') AS CategoryName,
    p.MRP                                  AS Price,
    p.stock_quantity                       AS StockQuantity,
    CASE
        WHEN p.stock_quantity <= 5  THEN 'Out of Stock'
        WHEN p.stock_quantity <= 80 THEN 'Low Stock'
        ELSE                             'In Stock'
    END                                    AS StockStatus
FROM e_commerce.product p
LEFT JOIN e_commerce.category c
    ON p.category_id = c.category_id;
GO

-- Products that are Low Stock or Out of Stock
SELECT
    ProductID,
    SKU,
    ProductName,
    CategoryName,
    StockQuantity,
    StockStatus
FROM e_commerce.vw_ProductCatalog
WHERE StockStatus IN ('Low Stock', 'Out of Stock')
ORDER BY StockQuantity ASC;
GO


-- ============================================================
-- Q3) Daily Sales Summary (KPIs)
-- ============================================================

-- Set your date range here
DECLARE @StartDate DATE = '2025-06-01';
DECLARE @EndDate   DATE = '2025-12-31';

SELECT
    CAST(o.order_date AS DATE)             AS SalesDate,
    COUNT(DISTINCT o.order_id)             AS OrdersCount,
    SUM(o.order_amount)                    AS TotalRevenue,
    CAST(
        SUM(o.order_amount) * 1.0
        / NULLIF(COUNT(DISTINCT o.order_id), 0)
    AS DECIMAL(10,2))                      AS AverageOrderValue,
    ISNULL(SUM(oi.quantity), 0)            AS ItemsSold
FROM e_commerce.order_table o
JOIN e_commerce.payment p
    ON o.order_id = p.order_id            -- paid orders only
LEFT JOIN e_commerce.orderitem oi
    ON o.order_id = oi.order_id
WHERE CAST(o.order_date AS DATE) >= @StartDate
  AND CAST(o.order_date AS DATE) <= @EndDate
GROUP BY CAST(o.order_date AS DATE)
ORDER BY SalesDate DESC;
GO


-- ============================================================
-- Q4) Order Details View
-- ============================================================

CREATE VIEW e_commerce.vw_OrderDetails AS
SELECT
    o.order_id                             AS OrderID,
    o.order_date                           AS OrderDate,
    c.FullName                             AS CustomerName,
    ISNULL(p.product_name, 'Unknown')      AS ProductName,
    oi.quantity                            AS Quantity,
    oi.MRP                                 AS UnitPrice,
    CAST(oi.quantity * oi.MRP AS DECIMAL(10,2)) AS LineTotal,
    CASE
        WHEN pay.order_id IS NOT NULL THEN 'Paid'
        ELSE 'Unpaid'
    END                                    AS PaymentStatus,
    ISNULL(s.ShipmentStatus, 'Not Shipped') AS ShipmentStatus
FROM e_commerce.order_table o
JOIN e_commerce.orderitem oi
    ON o.order_id = oi.order_id
LEFT JOIN e_commerce.product p
    ON oi.product_id = p.product_id
LEFT JOIN e_commerce.customer c
    ON o.customer_id = c.customer_id
LEFT JOIN e_commerce.payment pay
    ON o.order_id = pay.order_id
LEFT JOIN e_commerce.shipment s
    ON o.order_id = s.OrderID;
GO

-- Preview Order Details
SELECT TOP 50 *
FROM e_commerce.vw_OrderDetails
ORDER BY OrderID;
GO


-- ============================================================
-- Q5) Cart-to-Order Conversion
-- ============================================================

-- Monthly Conversion Rate
SELECT
    FORMAT(o.order_date, 'yyyy-MM')        AS OrderMonth,
    COUNT(DISTINCT c.cart_id)              AS TotalCartsCreated,
    COUNT(DISTINCT o.order_id)             AS CartsConvertedToOrders,
    CAST(
        100.0 * COUNT(DISTINCT o.order_id)
        / NULLIF(COUNT(DISTINCT c.cart_id), 0)
    AS DECIMAL(10,2))                      AS ConversionRatePercentage
FROM e_commerce.cart c
LEFT JOIN e_commerce.order_table o
    ON c.cart_id = o.cart_id
GROUP BY FORMAT(o.order_date, 'yyyy-MM')
ORDER BY OrderMonth DESC;
GO

-- Abandoned (Lost) Carts
SELECT
    c.cart_id,
    c.customer_id,
    c.grandtotal                           AS CartValue,
    c.itemtotal                            AS ItemCount
FROM e_commerce.cart c
WHERE c.is_converted = 0;                 -- not converted to order
GO


-- ============================================================
-- Q6) Top Products by Category (Ranking)
-- ============================================================

WITH ProductSales AS (
    SELECT
        p.category_id,
        cat.category_name,
        p.product_id,
        p.product_name,
        SUM(oi.quantity)                   AS TotalQuantitySold,
        SUM(oi.quantity * oi.MRP)          AS TotalRevenue
    FROM e_commerce.product p
    JOIN e_commerce.category cat
        ON p.category_id = cat.category_id
    JOIN e_commerce.orderitem oi
        ON p.product_id = oi.product_id
    GROUP BY
        p.category_id,
        cat.category_name,
        p.product_id,
        p.product_name
),
RankedProducts AS (
    SELECT
        category_id,
        category_name,
        product_name,
        TotalQuantitySold,
        TotalRevenue,
        DENSE_RANK() OVER (
            PARTITION BY category_id
            ORDER BY TotalRevenue DESC
        )                                  AS SalesRank
    FROM ProductSales
)
SELECT
    category_name                          AS CategoryName,
    SalesRank,
    product_name                           AS ProductName,
    TotalQuantitySold,
    TotalRevenue
FROM RankedProducts
WHERE SalesRank <= 3
ORDER BY category_name, SalesRank;
GO


-- ============================================================
-- Q7) Returns & Refunds Analytics View
-- ============================================================

CREATE VIEW e_commerce.vw_ReturnRefundSummary AS
SELECT
    r.return_id                            AS ReturnID,
    r.order_id                             AS OrderID,
    c.FullName                             AS CustomerName,
    p.product_name                         AS ProductName,
    r.quantity_returned                    AS QuantityReturned,
    r.return_reason                        AS ReturnReason,
    r.return_date                          AS ReturnDate,
    r.refund_amount                        AS RefundAmount,
    r.refund_status                        AS RefundStatus,
    r.refund_date                          AS RefundDate
FROM e_commerce.product_return r
JOIN e_commerce.order_table o
    ON r.order_id = o.order_id
JOIN e_commerce.customer c
    ON o.customer_id = c.customer_id
JOIN e_commerce.product p
    ON r.product_id = p.product_id;
GO

-- Top 5 Return Reasons
SELECT TOP 5
    ReturnReason,
    COUNT(*)                               AS TotalReturns,
    SUM(RefundAmount)                      AS TotalRefundedValue
FROM e_commerce.vw_ReturnRefundSummary
GROUP BY ReturnReason
ORDER BY TotalReturns DESC;
GO


-- ============================================================
-- Q8) Delivery Performance & Delay Report
-- ============================================================

-- Last 3 months from 2025-12-31

DECLARE @EndDate1    DATE = '2025-12-31';
DECLARE @FromDate DATE = DATEADD(MONTH, -3, @EndDate1);

SELECT
    s.Courier,
    COUNT(*)                               AS TotalShipments,
    SUM(CASE
            WHEN s.IsLateDelivery = 'No'  THEN 1
            ELSE 0
        END)                               AS OnTimeDeliveries,
    SUM(CASE
            WHEN s.IsLateDelivery = 'Yes' THEN 1
            ELSE 0
        END)                               AS LateDeliveries,
    CAST(AVG(CAST(s.DeliveryDays AS FLOAT))
        AS DECIMAL(8,2))                   AS AvgDeliveryDays
FROM e_commerce.shipment s
WHERE s.ShippedDate >= @FromDate
  AND s.ShipmentStatus = 'Delivered'
GROUP BY s.Courier
ORDER BY LateDeliveries DESC;
GO


-- ============================================================
-- Q9) Revenue by Payment Method + Outstanding Payments
-- ============================================================

-- Set your month range here
DECLARE @MonthStart DATE = '2025-06-01';
DECLARE @MonthEnd   DATE = '2025-12-31';

SELECT
    ISNULL(p.paymentMode, 'Unpaid')        AS PaymentMethod,
    SUM(CASE
            WHEN p.payment_id IS NOT NULL  THEN o.order_amount
            ELSE 0
        END)                               AS TotalPaid,
    COUNT(CASE
            WHEN o.order_status = 'Cancelled' THEN 1
          END)                             AS CountCancelled,
    SUM(CASE
            WHEN o.order_status = 'Delivered'
                 AND p.payment_id IS NULL
            THEN o.order_amount
            ELSE 0
        END)                               AS TotalOutstanding
FROM e_commerce.order_table o
LEFT JOIN e_commerce.payment p
    ON o.order_id = p.order_id
WHERE CAST(o.order_date AS DATE) >= @MonthStart
  AND CAST(o.order_date AS DATE) <= @MonthEnd
GROUP BY p.paymentMode
ORDER BY TotalPaid DESC;
GO


-- ============================================================
-- Q10) Customer Support SLA View
-- ============================================================

CREATE VIEW e_commerce.vw_SupportSLA AS
SELECT
    t.ticket_id                            AS TicketID,
    c.FullName                             AS CustomerName,
    t.order_id                             AS OrderID,
    t.category                             AS Category,
    t.subject                              AS Subject,
    t.priority                             AS Priority,
    t.status                               AS Status,
    t.created_at                           AS CreatedAt,
    t.closed_at                            AS ClosedAt,

    -- Compute ResolutionHours from actual dates
    CASE
        WHEN t.closed_at IS NOT NULL
        THEN DATEDIFF(HOUR, t.created_at, t.closed_at)
        ELSE NULL
    END                                    AS ResolutionHours,

    -- SLA threshold per priority
    CASE t.priority
        WHEN 'High'   THEN 24
        WHEN 'Medium' THEN 48
        WHEN 'Low'    THEN 72
    END                                    AS SLA_ThresholdHours,

    -- SLA Breached flag (computed, not from CSV)
    CASE
        WHEN t.status = 'Closed'
             AND t.closed_at IS NOT NULL
             AND DATEDIFF(HOUR, t.created_at, t.closed_at) >
                 CASE t.priority
                     WHEN 'High'   THEN 24
                     WHEN 'Medium' THEN 48
                     WHEN 'Low'    THEN 72
                     ELSE 999999
                 END
        THEN 'Yes'
        ELSE 'No'
    END                                    AS SLA_Breached

FROM e_commerce.support_tickets t
LEFT JOIN e_commerce.customer c
    ON t.customer_id = c.customer_id;
GO

-- All SLA breached tickets in the last month
SELECT *
FROM e_commerce.vw_SupportSLA
WHERE SLA_Breached = 'Yes'
  AND CreatedAt >= DATEADD(
        MONTH, -1,
        (SELECT MAX(created_at) FROM e_commerce.support_tickets)
      )
ORDER BY Priority ASC, CreatedAt DESC;
GO

-- SLA Breach Summary by Priority
SELECT
    Priority,
    SLA_ThresholdHours,
    COUNT(*)                               AS TotalTickets,
    SUM(CASE WHEN SLA_Breached = 'Yes' THEN 1 ELSE 0 END) AS Breached,
    SUM(CASE WHEN SLA_Breached = 'No'  THEN 1 ELSE 0 END) AS WithinSLA,
    CAST(
        100.0 * SUM(CASE WHEN SLA_Breached = 'Yes' THEN 1 ELSE 0 END)
        / COUNT(*)
    AS DECIMAL(5,2))                       AS BreachRate_Pct,
    CAST(AVG(ResolutionHours * 1.0)
    AS DECIMAL(8,2))                       AS AvgResolutionHours
FROM e_commerce.vw_SupportSLA
WHERE Status = 'Closed'
GROUP BY Priority, SLA_ThresholdHours
ORDER BY SLA_ThresholdHours;
GO