Scribd
Upload
8 views3 pages

Solution 2

The document provides SQL queries to analyze sales data, including identifying the highest sales product category, top and bottom sales regions, and total sales for appliances in Ontario. It also includes queries to find the bottom 10 customers by sales, shipping costs by method, valuable customers, and specific customer segments with the highest sales or returns. Additionally, it highlights the most profitable consumer customer and the corporate customer with the most orders between 2009 and 2012.

Uploaded by

Yomi Brain
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
8 views3 pages

Solution 2

The document provides SQL queries to analyze sales data, including identifying the highest sales product category, top and bottom sales regions, and total sales for appliances in Ontario. It also includes queries to find the bottom 10 customers by sales, shipping costs by method, valuable customers, and specific customer segments with the highest sales or returns. Additionally, it highlights the most profitable consumer customer and the corporate customer with the most orders between 2009 and 2012.

Uploaded by

Yomi Brain
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Solution

1. Which product category had the highest sales?


SELECT
`Product Category`,
SUM(Sales) AS Total_Sales
FROM
records
GROUP BY
`Product Category`
ORDER BY
Total_Sales DESC
LIMIT 1;

2. What are the Top 3 and Bottom 3 regions in terms of sales?

🔼 Top 3 Regions:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM
records
GROUP BY
Region
ORDER BY
Total_Sales DESC
LIMIT 3;
🔽 Bottom 3 Regions:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM
records
GROUP BY
Region
ORDER BY
Total_Sales ASC
LIMIT 3;

3. What were the total sales of appliances in Ontario?


SELECT
SUM(Sales) AS Total_Sales
FROM
records
WHERE
Province = 'Ontario'
AND `Product Sub-Category` = 'Appliances';

✅ 4. Advise KMS on bottom 10 customers

This is a theory answer, not SQL. But to write a query that finds bottom 10 customers by sales:
SELECT
`Customer Name`,
SUM(Sales) AS Total_Sales
FROM
records
GROUP BY
`Customer Name`
ORDER BY
Total_Sales ASC
LIMIT 10;

5. KMS incurred the most shipping cost using which shipping method?
SELECT
`Ship Mode`,
SUM(`Shipping Cost`) AS Total_Shipping_Cost
FROM
records
GROUP BY
`Ship Mode`
ORDER BY
Total_Shipping_Cost DESC
LIMIT 1;

6. Who are the most valuable customers and what do they typically purchase?
SELECT
`Customer Name`,
SUM(Sales) AS Total_Sales,
COUNT(DISTINCT `Product Name`) AS Unique_Products
FROM
records
GROUP BY
`Customer Name`
ORDER BY
Total_Sales DESC
LIMIT 5;

7. Which small business customer had the highest sales?


SELECT
`Customer Name`,
SUM(Sales) AS Total_Sales
FROM
records
WHERE
`Customer Segment` = 'Small Business'
GROUP BY
`Customer Name`
ORDER BY
Total_Sales DESC
LIMIT 1;

8. Which corporate customer placed the most number of orders in 2009 – 2012?
SELECT
`Customer Name`,
COUNT(DISTINCT `Order ID`) AS Number_of_Orders
FROM
records
WHERE
`Customer Segment` = 'Corporate'
AND YEAR(`Order Date`) BETWEEN 2009 AND 2012
GROUP BY
`Customer Name`
ORDER BY
Number_of_Orders DESC
LIMIT 1;

9. Which consumer customer was the most profitable one?


SELECT
`Customer Name`,
SUM(Profit) AS Total_Profit
FROM
records
WHERE
`Customer Segment` = 'Consumer'
GROUP BY
`Customer Name`
ORDER BY
Total_Profit DESC
LIMIT 1;

10. Which customer returned items, and what segment do they belong to?

Assuming the return info is in a column called Returned and the value for returned items is
'Yes':

SELECT
`Customer Name`,
`Customer Segment`
FROM
records
WHERE
Returned = 'Yes'
GROUP BY
`Customer Name`, `Customer Segment`;

You might also like