-- TASK 6 TO 10
--
--
--
-- Task 6 


SELECT gender,MONTH(STR_TO_DATE(`date`, '%d-%m-%Y')) AS month,
ROUND(SUM(total),0) AS Total_sales
FROM walmartsales
GROUP BY gender,month
ORDER BY gender, month;


-- Task 7

SELECT `Customer type`,`Product line`,
COUNT(*) AS no_of_customers
FROM walmartsales
GROUP BY `Customer type`, `Product line`
ORDER BY no_of_customers DESC;

-- Task 8

WITH date_purchases AS (
SELECT `Customer ID`,
STR_TO_DATE(`Date`, '%d-%m-%Y') AS purchase_date,
LAG(STR_TO_DATE(`Date`, '%d-%m-%Y')) 
OVER (PARTITION BY `Customer ID` ORDER BY STR_TO_DATE(`Date`, '%d-%m-%Y')
) AS prev_date_purchase
FROM walmartsales
)

SELECT `Customer ID`,prev_date_purchase, purchase_date,
DATEDIFF(purchase_date, prev_date_purchase) AS days_between
FROM date_purchases
WHERE prev_date_purchase IS NOT NULL 
AND DATEDIFF(purchase_date, prev_date_purchase) <= 30;


-- Task 9

WITH cust_totalsales AS(
SELECT `Customer id`,
ROUND(SUM(total),2) AS Sales_revenue
FROM walmartsales
GROUP BY `Customer ID`
ORDER BY sales_revenue DESC
)
SELECT *,
RANK() OVER(ORDER BY sales_revenue DESC) AS rank_acc_sales
FROM cust_totalsales
LIMIT 5;

-- Task 10

SELECT
DAYNAME(STR_TO_DATE(`date`,'%d-%m-%y')) AS day_of_week,
ROUND(SUM(total),2) AS total_sales
FROM walmartsales
GROUP BY day_of_week
ORDER BY total_sales DESC;


