Analysis.sql

-- How much total revenue did the platform generate? --
SELECT 
    SUM(total_amount) AS total_revenue
FROM orders;

-- How many orders and customers do we have?-- 
SELECT
    COUNT(DISTINCT order_id) AS total_orders,
    COUNT(DISTINCT customer_id) AS total_customers
FROM orders;

-- How does revenue change month-wise?--
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(total_amount) AS monthly_revenue
FROM orders
GROUP BY month
ORDER BY month;

-- Who are the highest-value customers? --
SELECT
    c.customer_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_spent DESC
LIMIT 5;

-- Which cities generate the most revenue?--
SELECT
    c.city,
    SUM(o.total_amount) AS city_revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.city
ORDER BY city_revenue DESC;

-- Business question: Which products sell the most (money-wise)? --
SELECT
    p.product_name,
    SUM(oi.quantity * oi.item_price) AS product_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name
ORDER BY product_revenue DESC
LIMIT 5;

-- Business question: Which product category brings most revenue? --
SELECT
    p.category,
    SUM(oi.quantity * oi.item_price) AS category_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY category_revenue DESC;

-- Business question: On average, how much does a customer spend per order? -- 
SELECT
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders;

-- Business question: Who are repeat buyers? --
SELECT
    customer_id,
    COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

-- You can compare this with total customers to explain retention. --
SELECT
    COUNT(DISTINCT customer_id) AS repeat_customers
FROM (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(order_id) > 1
) rc;

-- This is what interviewers LOVE to see --
SELECT
    c.customer_name,
    SUM(o.total_amount) AS total_spent,
    RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS spending_rank
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

-- Monthly Revenue Growth --
WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY month
)
SELECT
    month,
    revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_growth
FROM monthly_sales;

-- How valuable is each customer over time? --
SELECT
    c.customer_name,
    SUM(o.total_amount) AS lifetime_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name
ORDER BY lifetime_value DESC;

-- Are we growing or just selling to old customers? --
SELECT
    customer_type,
    COUNT(*) AS customer_count
FROM (
    SELECT
        customer_id,
        CASE 
            WHEN COUNT(order_id) = 1 THEN 'New'
            ELSE 'Returning'
        END AS customer_type
    FROM orders
    GROUP BY customer_id
) t
GROUP BY customer_type;

-- Which category contributes how much % to revenue? --
SELECT
    p.category,
    ROUND(
        SUM(oi.quantity * oi.item_price) * 100 /
        (SELECT SUM(quantity * item_price) FROM order_items),
        2
    ) AS revenue_percentage
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category;

-- Which products are not selling well? --
SELECT
    p.product_name,
    SUM(oi.quantity) AS total_units_sold
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY total_units_sold ASC
LIMIT 5;

-- Are customers placing small or large orders? -- 
SELECT
    CASE
        WHEN total_amount < 5000 THEN 'Low'
        WHEN total_amount BETWEEN 5000 AND 20000 THEN 'Medium'
        ELSE 'High'
    END AS order_value_segment,
    COUNT(*) AS order_count
FROM orders
GROUP BY order_value_segment;

-- Which cities dominate sales? --
SELECT
    city,
    SUM(total_amount) AS city_revenue,
    DENSE_RANK() OVER (ORDER BY SUM(total_amount) DESC) AS city_rank
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY city;

-- How frequently customers purchase? --
SELECT
    customer_id,
    AVG(DATEDIFF(order_date,
        LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
    )) AS avg_days_between_orders
FROM orders
GROUP BY customer_id;

-- Do top customers generate most revenue? --
WITH ranked_customers AS (
    SELECT
        customer_id,
        SUM(total_amount) AS revenue,
        NTILE(5) OVER (ORDER BY SUM(total_amount) DESC) AS bucket
    FROM orders
    GROUP BY customer_id
)
SELECT
    bucket,
    SUM(revenue) AS revenue_contribution
FROM ranked_customers
GROUP BY bucket;