SELECT * FROM walmartsales
LIMIT 200;

-- Task 1 TO Task 5
--
--
-- TASK 1

WITH monthly_sales AS (
SELECT branch,
MONTH(STR_TO_DATE(`date`, '%d-%m-%Y')) AS month,
SUM(Total) AS total_sales
FROM walmartsales
GROUP BY branch, month
),

prev_sales AS (
SELECT branch,month,total_sales,
LAG(total_sales) OVER (PARTITION BY branch ORDER BY month) AS prev_sales
FROM monthly_sales
),

growth_rate AS (
SELECT branch,month,total_sales,prev_sales,
ROUND(((total_sales - prev_sales) / prev_sales) * 100, 2) AS growth_sales
FROM prev_sales
WHERE prev_sales IS NOT NULL
)

SELECT branch,
ROUND(AVG(growth_sales), 2) AS avg_growth_sales
FROM growth_rate
GROUP BY branch
ORDER BY avg_growth_sales DESC
LIMIT 1;


-- TASK 2

SELECT Branch,`Product line`,Total_Profit
FROM (
SELECT Branch,`Product line`,
ROUND(SUM(`gross income`),0) AS Total_Profit,
RANK() OVER (PARTITION BY Branch ORDER BY SUM(`gross income`) DESC) AS rank_in_branch
FROM walmartsales
GROUP BY Branch, `Product line`
) ranked_profit
WHERE rank_in_branch = 1;

-- Task 3

WITH Cust_avg_spends AS(
SELECT `customer id`, ROUND(AVG(total),2) AS Avg_spending
FROM walmartsales
GROUP BY `customer id`
ORDER BY avg_spending DESC)

SELECT *,
CASE
WHEN avg_spending <300 THEN 'low spenders'
WHEN avg_spending < 340 THEN 'Normal spenders'
ELSE 'High spenders'
END AS Spenders_classify
FROM cust_avg_spends;

-- TASK 4

WITH sales_stats AS(
SELECT `product line`,`invoice id`,total,
AVG(total) OVER(PARTITION BY `product line`) AS avg_sales,
STDDEV(total) OVER(PARTITION BY `product line`) as standard_deviation
FROM walmartsales),

Anomlies_flagged AS(
SELECT `Invoice ID`,`Product line`,Total,
ROUND(avg_sales),ROUND(standard_deviation),
CASE 
WHEN total > avg_sales + (2 * standard_deviation) THEN 'high anomaly'
WHEN total < avg_sales - (2 * standard_deviation) THEN 'low anomaly'
END AS Anomalies
FROM sales_stats)
SELECT * 
FROM Anomlies_flagged
WHERE Anomalies IS NOT NULL;

-- task 5

WITH payment_count AS(
SELECT City,payment,count(Payment) AS total_payments
FROM walmartsales
GROUP BY city, payment
),
Ranked_payments AS (
SELECT city,payment,total_payments,
RANK() OVER(PARTITION BY CITY ORDER BY total_payments DESC) AS RANK_
FROM Payment_count
)
SELECT City,Payment,total_payments
FROM ranked_payments
WHERE RANK_ = 1;

