Day2 Script.sql

-- Find the total number of orders for each order status.
SELECT order_status, COUNT(order_id) AS Total_Orders
FROM orders
GROUP BY order_status
ORDER BY COUNT(order_id) DESC;

-- Find total delivered revenue generated by each city.
SELECT c.city_name, ROUND(SUM(o.net_amount),2) AS Total_Revenue
FROM cities AS c
JOIN orders as o
ON c.city_id = o.city_id
WHERE o.order_status = "Delivered"
GROUP BY c.city_name
ORDER BY ROUND(SUM(o.net_amount),2) DESC;

-- Find the average net order value for each payment method.
SELECT payment_method, ROUND(AVG(net_amount),2) AS Avg_order_value
FROM orders
GROUP BY payment_method
ORDER BY AVG(net_amount) DESC;

-- Find customers who placed more than 10 orders.
SELECT customer_id, COUNT(order_id) AS Total_Order
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 10;

-- Find total products available in each category.
SELECT c.category_name, COUNT(p.product_id)
FROM categories AS c
LEFT JOIN products as p
ON c.category_id = p.category_id
GROUP BY c.category_name;

-- Find total delivered revenue month-wise.
SELECT MONTHNAME(order_date) AS Month, ROUND(SUM(net_amount),2) AS Revenue
FROM orders
WHERE order_status = "Delivered"
GROUP BY Month;

-- Find average delivery time for each city.
SELECT c.city_name, ROUND(AVG(o.delivery_time_mins),0) AS Avg_Delivery_Time_Mins
FROM cities as c
LEFT JOIN orders as o
On c.city_id = o.city_id
GROUP BY c.city_id
ORDER BY AVG(o.delivery_time_mins);

-- Find campaigns generating more than ₹5,00,000 revenue.
SELECT c.campaign_name, SUM(o.net_amount) AS Revenue
FROM campaigns AS c
JOIN orders AS o
ON c.campaign_id = o.campaign_id
GROUP BY c.campaign_id
HAVING SUM(o.net_amount) > 500000;

-- Find total discount amount given by each payment method.
SELECT payment_method, SUM(discount_amount) AS Discounted_Amount
FROM orders
GROUP BY payment_method
ORDER BY SUM(discount_amount) DESC;

-- Find top 5 cities based on number of unique customers placing delivered orders.
SELECT c.city_name, COUNT(DISTINCT o.customer_id) AS Unique_customer
FROM cities AS C
LEFT JOIN orders AS o
ON c.city_id = o.city_id
WHERE o.order_status = "Delivered"
GROUP BY c.city_id
ORDER BY COUNT(DISTINCT o.customer_id) DESC
LIMIT 5;


-- QUESTION 1 — Monthly Revenue With Running Total
WITH monthly_revenue AS (
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month_year,
ROUND(SUM(net_amount), 2) AS monthly_revenue
FROM orders
WHERE order_status = 'Delivered'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT month_year, monthly_revenue,
ROUND(SUM(monthly_revenue)OVER (ORDER BY month_year),2) AS running_revenue
FROM monthly_revenue
ORDER BY month_year;

-- QUESTION 2 — Month-over-Month Revenue Growth %
WITH monthly_revenue AS (
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month_year,
ROUND(SUM(net_amount), 2) AS monthly_revenue
FROM orders
WHERE order_status = 'Delivered'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),revenue_with_previous AS (
SELECT month_year,monthly_revenue,
LAG(monthly_revenue)OVER (ORDER BY month_year) AS previous_month_revenue
FROM monthly_revenue
)
SELECT month_year, monthly_revenue, previous_month_revenue,
ROUND(((monthly_revenue - previous_month_revenue)/ previous_month_revenue) * 100,2) AS mom_growth_pct
FROM revenue_with_previous;

-- QUESTION 3 — Delivery Time Trend Analysis
WITH monthly_delivery AS (
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month_year,
ROUND(AVG(delivery_time_mins),2) AS avg_delivery_time
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),delivery_trend AS (
SELECT month_year, avg_delivery_time,
LAG(avg_delivery_time)OVER (ORDER BY month_year) AS previous_month_delivery
FROM monthly_delivery)
SELECT month_year, avg_delivery_time, previous_month_delivery,
ROUND(avg_delivery_time - previous_month_delivery,2) AS delivery_time_change
FROM delivery_trend;

-- QUESTION 4 — Customer Acquisition Running Total
WITH monthly_customers AS (
SELECT DATE_FORMAT(signup_date, '%Y-%m') AS month_year,
COUNT(customer_id) AS new_customers
FROM customers
GROUP BY DATE_FORMAT(signup_date, '%Y-%m')
)
SELECT month_year,new_customers,
SUM(new_customers)OVER (ORDER BY month_year) AS cumulative_customers
FROM monthly_customers
ORDER BY month_year;

-- QUESTION 5 — Campaign Revenue Next-Month Comparison
WITH monthly_campaign_revenue AS (
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month_year,
ROUND(SUM(net_amount), 2) AS monthly_revenue
FROM orders
WHERE order_status = 'Delivered'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT month_year, monthly_revenue,
LEAD(monthly_revenue) OVER (ORDER BY month_year) AS next_month_revenue
FROM monthly_campaign_revenue
ORDER BY month_year;