SQL: Customer Purchase Analysis

Sales Data EDA Assignment by AnalytixLabs Noida

Q15. For all customers aged between 25 to 35 years find what is the net total revenue generated by these consumers in last 30 days of transactions from max transaction date available in the data?

Output

Required_Revenue
494023.4

Q14. What is percentage of sales and returns by product sub category; display only top 5 sub categories in terms of sales

Output

Top 5 Sub Categories in Terms of Sales	Total_Sales	Percentage_Sales	Total_Returns	Percentage_Returns
Women	7020080.41	14.45	-852897.7	1.76
Mens	6905870.83	14.22	-733517.85	1.51
Kids	4806698.53	9.89	-550611.65	1.13
Mobiles	2508648.57	5.16	-259945.77	0.54
Fiction	2492901.17	5.13	-260650.73	0.54

Q13 Which store-type sells the maximum products; by value of sales amount and by quantity sold?

Output

Description	Store_type	Total
Maximum Sales is from the store :	e-Shop	22185613
Maximum Quantity overall is from the store :	e-Shop	22763

Q12 Which product category has seen the max value of returns in the last 3 months of transactions?

Output

Category	Total_Returns
Books	-134408.9

Q11 Find the average and total revenue by each subcategory for the categories which are among top 5 categories in terms of quantity sold.

Output

prod_cat	prod_subcat	Average_Revenue	Total_Revenue
Books	Academic	2125.49	2055344.43
Books	Children	2136.67	2211451.18
Books	Comics	2037.68	2100848.41
Books	DIY	2108.37	2085180.6
Books	Fiction	2140.22	2232250.44
Books	Non-Fiction	2129.1	2137620.47
Clothing	Kids	2136.6	2110960.06
Clothing	Mens	2128.26	2058030.8
Clothing	Women	2071.79	2082147.33
Electronics	Audio and video	2247.96	2140058.1
Electronics	Cameras	2165.88	2133390.59
Electronics	Computers	2181.75	2090116.61
Electronics	Mobiles	2181.09	2248702.79
Electronics	Personal Appliances	2170.98	2110196.56
Footwear	Kids	2125.99	2145126.81
Footwear	Mens	2112.06	1989564.94
Footwear	Women	1989.58	2085083.38
Home and kitchen	Bath	2059.85	2107226.39
Home and kitchen	Furnishing	2084.01	2098595.35
Home and kitchen	Kitchen	2008.96	2083289.92
Home and kitchen	Tools	2024.37	2149882.8

Q10 What are the categories for which average revenue is above the overall average

Output

prod_cat	Average_Revenue
Electronics	2189.15
Books	2112.82
Clothing	2111.87

Q9 What is the total revenue generated from “Male” customers in “Electronics” category? Output should display total revenue by prod sub-cat.

Output

prod_cat	prod_subcat	Total_Revenue
Electronics	Mobiles	1192413.25
Electronics	Cameras	1172702.34
Electronics	Audio and video	1138983.19
Electronics	Personal Appliances	1107593.5
Electronics	Computers	1091417.54

Q8 What is the combined revenue earned from the “Electronics” & “Clothing” categories, from “Flagship stores”?

Output

| Combines Revenue is = 3409559.52 | prod_subcat | |———————————-|————-|

Q7 How many customers have >10 transactions with us, excluding returns?

Output

| Customers with Transactions > 10 excluding returns | 6 | |—————————————————-|—|

Q6 What is the net total revenue generated in categories Electronics and Books?

Output

| Net total revenue is = | 23545160.21 | |————————|————-|

Q5 What is the maximum quantity of products ever ordered?

Output

prod_cat	Maximum_Quantity
Books	14669

Q4 How many sub-categories are there under the Books category?

Output

prod_cat	Number of sub categories
Books	6

Q3 From which city do we have the maximum number of customers and how many?

Output

city_code	Customers
3	595

Q2 What is the count of Male and Female customers in the database

Output

Gender	Total Customers
M	2892
F	2753

Q1 Which channel is most frequently used for transactions

Output

| e-Shop | |——–|