SELECT customer_names AS Customer, count(customer_names) AS Count
FROM customer
JOIN sales_order
ON customer.customer_id = sales_order.customer_id
GROUP BY customer_names
ORDER BY Count DESC;3. Transaction Summary Of Customers
Given that the company sell products to both customers and businesses, let’s get the total number of transaction conducted with each customers.
| Customer | Count |
|---|---|
| Medline | 210 |
| Eminence Corp | 186 |
| Elorac, Corp | 181 |
| Apotheca, Ltd | 179 |
| Apollo Ltd | 178 |
| Victory Ltd | 176 |
| OUR Ltd | 176 |
| Pure Group | 175 |
| OHTA'S Corp | 173 |
| Fenwal, Corp | 172 |
| Ole Group | 171 |
| Ei | 171 |
| Niconovum Corp | 168 |
| ETUDE Ltd | 167 |
| Pacific Ltd | 167 |
| Qualitest | 165 |
| Ohio | 164 |
| E. Ltd | 164 |
| Sundial | 163 |
| Prasco Group | 162 |
| Llorens Ltd | 161 |
| Weimei Corp | 161 |
| Procter Corp | 159 |
| S.S.S. Group | 159 |
| 21st Ltd | 158 |
| U.S. Ltd | 157 |
| Linde | 157 |
| O.E. Ltd | 156 |
| Nipro | 156 |
| Winthrop | 156 |
| Trigen | 156 |
| Mylan Corp | 153 |
| New Ltd | 153 |
| AuroMedics Corp | 152 |
| Avon Corp | 152 |
| Uriel Group | 152 |
| Bare | 152 |
| Exact-Rx, Corp | 151 |
| Burt's Corp | 151 |
| Select | 150 |
| Ascend Ltd | 150 |
| Amylin Group | 145 |
| Dharma Ltd | 145 |
| Wuxi Group | 144 |
| PEDIFIX, Corp | 143 |
| Rochester Ltd | 142 |
| Medsep Group | 142 |
| Capweld | 140 |
| 3LAB, Ltd | 135 |
| WakeFern | 135 |
First the number of customers that have made at least a single order or more over the three year period is 50 and the top 3 customers based on the total unique transactions conducted are Medline, Eminence Corp & Elorac, Corp.
Now that we know the total number of unique customers and their overall transaction count, we can now proceed to the total order quantity and sales from each customer.
WITH cus_qos AS (
SELECT customer_id,
order_quantity,
(order_quantity * unit_price) AS sales
FROM sales_order
)
SELECT customer_names,
SUM(order_quantity) AS Total_Order_Quantity,
round(SUM(sales), 2) AS Total_Sales
FROM cus_qos
JOIN customer
ON cus_qos.customer_id = customer.customer_id
GROUP BY customer_names
ORDER BY Total_Sales DESC;| Customer Names | Total Order Quantity | Total Sales |
|---|---|---|
| Medline | 970 | 2,248,332 |
| Apotheca, Ltd | 828 | 2,112,222 |
| Pure Group | 783 | 1,962,015 |
| OUR Ltd | 796 | 1,937,828 |
| Trigen | 767 | 1,895,189 |
| Apollo Ltd | 805 | 1,883,410 |
| Ohio | 778 | 1,881,628 |
| Ole Group | 765 | 1,868,322 |
| OHTA'S Corp | 819 | 1,856,356 |
| Eminence Corp | 785 | 1,845,783 |
| Elorac, Corp | 880 | 1,831,947 |
| Ei | 818 | 1,830,038 |
| Victory Ltd | 812 | 1,823,941 |
| E. Ltd | 763 | 1,793,523 |
| ETUDE Ltd | 781 | 1,770,582 |
| 21st Ltd | 706 | 1,763,943 |
| Prasco Group | 735 | 1,762,783 |
| Qualitest | 787 | 1,723,535 |
| Sundial | 720 | 1,696,226 |
| Fenwal, Corp | 783 | 1,670,109 |
| Llorens Ltd | 761 | 1,653,815 |
| S.S.S. Group | 732 | 1,640,368 |
| Select | 720 | 1,636,897 |
| Nipro | 669 | 1,630,030 |
| AuroMedics Corp | 699 | 1,622,258 |
| O.E. Ltd | 658 | 1,620,904 |
| Procter Corp | 717 | 1,609,233 |
| Niconovum Corp | 739 | 1,579,626 |
| Exact-Rx, Corp | 666 | 1,564,276 |
| Mylan Corp | 714 | 1,561,783 |
| Amylin Group | 697 | 1,555,070 |
| 3LAB, Ltd | 630 | 1,547,774 |
| New Ltd | 723 | 1,544,062 |
| Weimei Corp | 689 | 1,533,047 |
| PEDIFIX, Corp | 639 | 1,530,756 |
| Winthrop | 714 | 1,529,992 |
| Pacific Ltd | 754 | 1,523,888 |
| Rochester Ltd | 627 | 1,512,726 |
| U.S. Ltd | 659 | 1,508,083 |
| Bare | 653 | 1,503,467 |
| Capweld | 614 | 1,494,033 |
| Linde | 681 | 1,452,225 |
| Uriel Group | 665 | 1,450,061 |
| Burt's Corp | 649 | 1,429,961 |
| Ascend Ltd | 640 | 1,413,988 |
| Medsep Group | 650 | 1,411,523 |
| Dharma Ltd | 624 | 1,408,621 |
| Wuxi Group | 652 | 1,398,009 |
| WakeFern | 611 | 1,346,265 |
| Avon Corp | 635 | 1,322,279 |
It is no surprise that Medline is the customer with the highest number of order and sales given that they had the highest number of purchase transactions with the company.
A quick dive into Medline and the amount of order and sales for each product.
WITH cus_prod_ors AS (
SELECT customer_id,
product_id,
order_quantity,
(order_quantity * unit_price) AS sales
FROM sales_order
)
SELECT product_name,
SUM(order_quantity) AS Total_Order_Quantity,
round(SUM(sales), 2) AS Total_Sales
FROM cus_prod_ors
JOIN customer
ON cus_prod_ors.customer_id = customer.customer_id
JOIN product
ON cus_prod_ors.product_id = product.product_id
WHERE customer_names = 'Medline'
GROUP BY product_name
ORDER BY Total_Sales DESC;| Product Name | Total Order Quantity | Total Sales |
|---|---|---|
| Floral | 48 | 144,023.20 |
| Accessories | 43 | 140,117.10 |
| TV and video | 43 | 104,392.70 |
| Wall Coverings | 28 | 101,940.50 |
| Bean Bags | 31 | 94,590.60 |
| Serveware | 26 | 92,051.30 |
| Floor Lamps | 34 | 86,986.10 |
| Blankets | 36 | 77,820.50 |
| Photo Frames | 22 | 75,060.10 |
| Phones | 28 | 67,201.00 |
| Wreaths | 26 | 66,330.00 |
| Platters | 31 | 64,775.60 |
| Bar Tools | 29 | 60,728.80 |
| Outdoor Decor | 14 | 56,400.60 |
| Ornaments | 26 | 55,187.90 |
| Mirrors | 29 | 51,965.20 |
| Baseball | 28 | 51,040.60 |
| Pillows | 14 | 50,779.30 |
| Bakeware | 18 | 49,439.30 |
| Cookware | 22 | 47,415.90 |
| Rugs | 14 | 46,290.30 |
| Vases | 23 | 45,988.80 |
| Dinnerware | 26 | 44,407.60 |
| Vanities | 14 | 43,576.80 |
| Home Fragrances | 32 | 42,779.50 |
| Wine Storage | 20 | 38,900.20 |
| Stemware | 12 | 37,071.10 |
| Furniture Cushions | 17 | 36,890.20 |
| Bathroom Furniture | 27 | 35,235.30 |
| Pendants | 16 | 32,836.70 |
| Table Lamps | 15 | 32,541.90 |
| Computers | 14 | 31,503.40 |
| Wardrobes | 20 | 30,371.10 |
| Basketball | 16 | 29,614.00 |
| Wall Frames | 13 | 28,381.20 |
| Dining Furniture | 13 | 27,778.20 |
| Festive | 12 | 22,780.00 |
| Table Linens | 5 | 20,240.70 |
| Audio | 20 | 19,550.60 |
| Collectibles | 13 | 17,929.20 |
| Candleholders | 5 | 17,554.00 |
| Sculptures | 8 | 8,629.60 |
| Bedroom Furniture | 7 | 5,420.30 |
| Cocktail Glasses | 12 | 4,984.80 |
| Candles | 9 | 4,361.70 |
| Outdoor Furniture | 3 | 2,753.70 |
| Clocks | 8 | 1,715.20 |
The most purchased product from Medline is the Floral product with 48 orders and 144,023.2 in sales.
Moving forward, it is important to know which of the customers are bringing in the highest amount of profit for the business, so let’s get the top 10 customers based on the total amount of profit made from the sales of products.
WITH cus_sp AS (
SELECT customer_id,
(order_quantity * unit_price) - ((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS profit
FROM sales_order
)
SELECT customer_names,
round(AVG(profit), 2) AS Average_Profit,
round(SUM(profit), 2) AS Total_Profit
FROM cus_sp
JOIN customer
ON cus_sp.customer_id = customer.customer_id
GROUP BY customer_names
ORDER BY Total_Profit DESC
LIMIT 10;| Customer Names | Average Profit | Total Profit |
|---|---|---|
| Medline | 2,936.76 | 616,719.24 |
| Pure Group | 3,051.59 | 534,027.38 |
| Apotheca, Ltd | 2,970.79 | 531,770.69 |
| ETUDE Ltd | 3,155.58 | 526,981.06 |
| Ei | 2,924.95 | 500,166.45 |
| Sundial | 3,057.58 | 498,384.99 |
| OUR Ltd | 2,820.56 | 496,418.67 |
| Trigen | 3,175.92 | 495,444.26 |
| Apollo Ltd | 2,739.41 | 487,614.23 |
| Ohio | 2,923.07 | 479,383.08 |
Medline maintains it position as the top customer with the highest profit made while Trigen had the highest average profit for all periods.
And lastly on customer exploration, it will be useful to get the highest amount spent on a particular product by a customer.
WITH prod_cus_sale AS (
SELECT customer_id,
product_id,
(order_quantity * unit_price) AS sales
FROM sales_order
)
SELECT customer_names,
product_name,
round(SUM(sales), 2) AS Total_Sales
FROM prod_cus_sale
JOIN customer
ON prod_cus_sale.customer_id = customer.customer_id
JOIN product
ON prod_cus_sale.product_id = product.product_id
GROUP BY customer_names, product_name
ORDER BY Total_Sales DESC
LIMIT 5;| Customer Names | Product Name | Total Sales |
|---|---|---|
| Eminence Corp | Wardrobes | 158,414.80 |
| Trigen | Collectibles | 155,245.70 |
| Niconovum Corp | Bathroom Furniture | 144,385.00 |
| Medline | Floral | 144,023.20 |
| E. Ltd | Wall Frames | 140,679.90 |
Customer Eminence Corp on the Wardrobes product.
Insight
All customers have made 630 and above product order within the selected period and the top customer based on number of order, sales and profit is Medline while highest profit made on an average can from Trigen.