SELECT product_name, count(product_name) AS Count
FROM product
JOIN sales_order
ON product.product_id = sales_order.product_id
GROUP BY product_name
ORDER BY Count DESC;2. Product Summary
First let us get all the unique products sold during the selected period.
| Product Name | Count |
|---|---|
| Serveware | 200 |
| Platters | 200 |
| Cocktail Glasses | 195 |
| Accessories | 194 |
| Wreaths | 190 |
| Rugs | 184 |
| Wardrobes | 183 |
| Ornaments | 182 |
| Sculptures | 182 |
| Dining Furniture | 181 |
| TV and video | 180 |
| Collectibles | 179 |
| Phones | 178 |
| Furniture Cushions | 178 |
| Stemware | 177 |
| Pendants | 176 |
| Bakeware | 175 |
| Photo Frames | 173 |
| Bathroom Furniture | 173 |
| Table Lamps | 172 |
| Home Fragrances | 172 |
| Baseball | 170 |
| Bar Tools | 170 |
| Clocks | 170 |
| Candleholders | 169 |
| Blankets | 168 |
| Floral | 168 |
| Wine Storage | 168 |
| Table Linens | 168 |
| Dinnerware | 166 |
| Outdoor Decor | 166 |
| Festive | 165 |
| Vanities | 164 |
| Floor Lamps | 164 |
| Wall Frames | 163 |
| Mirrors | 163 |
| Cookware | 162 |
| Candles | 158 |
| Outdoor Furniture | 157 |
| Basketball | 156 |
| Wall Coverings | 156 |
| Computers | 154 |
| Bedroom Furniture | 152 |
| Audio | 152 |
| Bean Bags | 147 |
| Vases | 145 |
| Pillows | 126 |
There are 47 unique products sold from the above table and Serveware & platters have the highest number of unique transactions involved in, while the pillows product have the fewest.
Following the number of times each product was sold, what are the top 3 products
based on the number of order placed for them by customers?
SELECT product_name, SUM(order_quantity) AS Total_Order
FROM sales_order
JOIN product
ON sales_order.product_id = product.product_id
GROUP BY product_name
ORDER BY Total_Order DESC
LIMIT 3;| Product Name | Total Order |
|---|---|
| Accessories | 956 |
| Platters | 896 |
| Cocktail Glasses | 879 |
The top 3 product by total number of order for all periods are Accessories, Platters and Cocktail Glasses with 585, 635 and 644 order respectively.
We know the top three must order products, let us get the cost based on
Average discount paid for each order to customers.
WITH prod_dis AS (
SELECT product_id,
(order_quantity * unit_price) * discount_applied AS discount
FROM sales_order
)
SELECT product_name, round(AVG(discount), 2) AS Average_Discount
FROM prod_dis
JOIN product
ON prod_dis.product_id = product.product_id
GROUP BY product_name
ORDER BY Average_Discount DESC;| Product Name | Average Discount |
|---|---|
| Mirrors | 1,511.28 |
| Basketball | 1,489.14 |
| Rugs | 1,428.02 |
| Serveware | 1,417.79 |
| Collectibles | 1,342.40 |
| Bar Tools | 1,333.43 |
| Photo Frames | 1,331.88 |
| Sculptures | 1,329.62 |
| Accessories | 1,327.77 |
| Cocktail Glasses | 1,314.29 |
| Blankets | 1,309.64 |
| Ornaments | 1,302.24 |
| Table Linens | 1,290.50 |
| Outdoor Furniture | 1,259.69 |
| Candles | 1,259.37 |
| Vanities | 1,242.55 |
| Dining Furniture | 1,224.22 |
| Candleholders | 1,219.86 |
| TV and video | 1,203.12 |
| Wall Frames | 1,195.80 |
| Bathroom Furniture | 1,193.01 |
| Furniture Cushions | 1,183.27 |
| Wine Storage | 1,178.00 |
| Table Lamps | 1,168.92 |
| Baseball | 1,158.46 |
| Dinnerware | 1,151.19 |
| Floor Lamps | 1,151.03 |
| Clocks | 1,150.46 |
| Wall Coverings | 1,145.30 |
| Audio | 1,144.74 |
| Floral | 1,119.88 |
| Platters | 1,113.96 |
| Bean Bags | 1,113.67 |
| Phones | 1,110.22 |
| Pendants | 1,109.18 |
| Home Fragrances | 1,093.76 |
| Wardrobes | 1,085.89 |
| Bedroom Furniture | 1,080.13 |
| Festive | 1,076.17 |
| Wreaths | 1,069.82 |
| Bakeware | 1,052.59 |
| Cookware | 1,033.39 |
| Vases | 1,014.09 |
| Pillows | 1,010.88 |
| Outdoor Decor | 998.12 |
| Stemware | 993.84 |
| Computers | 971.26 |
The highest average discount was paid for the Mirror product, while the lowest was paid for Computers.
Profit summary of all products during:
WITH prod_rev AS (
SELECT product_id,
(order_quantity * unit_price) AS sales,
((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS cost,
(order_quantity * unit_price) - ((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS profit
FROM sales_order
)
SELECT product_name,
round(SUM(sales), 2) AS Total_Sales,
round(SUM(cost), 2) AS Total_Cost,
round(SUM(profit), 2) AS Total_Profit
FROM prod_rev pv
JOIN product p
ON pv.product_id = p.product_id
GROUP BY product_name
ORDER BY Total_Profit DESC;| Product Name | Total Sales | Total Cost | Total Profit |
|---|---|---|---|
| Accessories | 2,358,788.61 | 1,707,556.41 | 651,232.19 |
| Photo Frames | 2,005,638.31 | 1,452,454.21 | 553,184.09 |
| Bathroom Furniture | 2,011,333.31 | 1,466,743.10 | 544,590.20 |
| Cocktail Glasses | 1,976,895.30 | 1,437,143.51 | 539,751.79 |
| Table Linens | 1,981,973.90 | 1,457,329.60 | 524,644.30 |
| Stemware | 1,849,923.60 | 1,326,215.96 | 523,707.64 |
| Collectibles | 2,049,958.80 | 1,528,929.09 | 521,029.72 |
| Platters | 2,052,886.70 | 1,532,489.23 | 520,397.47 |
| Furniture Cushions | 1,925,111.00 | 1,419,117.29 | 505,993.71 |
| Rugs | 2,130,841.20 | 1,626,318.04 | 504,523.16 |
| Ornaments | 1,885,326.39 | 1,381,236.43 | 504,089.96 |
| Serveware | 2,071,546.20 | 1,568,826.98 | 502,719.22 |
| Floral | 1,821,837.20 | 1,325,442.68 | 496,394.52 |
| TV and video | 1,825,415.00 | 1,332,367.23 | 493,047.76 |
| Blankets | 1,870,921.39 | 1,378,187.43 | 492,733.97 |
| Mirrors | 1,921,372.41 | 1,429,144.65 | 492,227.76 |
| Table Lamps | 1,813,589.50 | 1,322,768.98 | 490,820.52 |
| Festive | 1,754,783.61 | 1,271,794.57 | 482,989.03 |
| Dinnerware | 1,727,112.60 | 1,249,620.72 | 477,491.87 |
| Vanities | 1,790,675.50 | 1,317,732.19 | 472,943.32 |
| Wardrobes | 1,868,140.90 | 1,395,663.94 | 472,476.96 |
| Wine Storage | 1,742,422.10 | 1,275,602.72 | 466,819.38 |
| Wall Frames | 1,785,362.40 | 1,321,318.13 | 464,044.27 |
| Wreaths | 1,786,441.10 | 1,329,945.18 | 456,495.92 |
| Bar Tools | 1,821,080.09 | 1,365,587.21 | 455,492.89 |
| Pendants | 1,881,668.20 | 1,428,759.83 | 452,908.37 |
| Dining Furniture | 1,846,178.30 | 1,395,752.51 | 450,425.78 |
| Sculptures | 1,837,501.79 | 1,387,507.70 | 449,994.10 |
| Candles | 1,607,028.49 | 1,179,443.98 | 427,584.51 |
| Home Fragrances | 1,709,974.00 | 1,285,435.57 | 424,538.43 |
| Phones | 1,685,217.50 | 1,266,257.12 | 418,960.37 |
| Baseball | 1,644,092.90 | 1,225,832.74 | 418,260.16 |
| Clocks | 1,703,629.10 | 1,288,831.10 | 414,798.00 |
| Floor Lamps | 1,528,303.49 | 1,116,922.87 | 411,380.62 |
| Outdoor Decor | 1,529,234.79 | 1,126,161.76 | 403,073.03 |
| Basketball | 1,697,592.39 | 1,301,471.79 | 396,120.60 |
| Vases | 1,402,685.20 | 1,017,257.08 | 385,428.11 |
| Wall Coverings | 1,536,377.00 | 1,151,271.42 | 385,105.58 |
| Cookware | 1,436,687.70 | 1,052,834.55 | 383,853.14 |
| Bakeware | 1,642,706.00 | 1,259,054.72 | 383,651.28 |
| Audio | 1,542,400.30 | 1,160,707.77 | 381,692.53 |
| Computers | 1,422,322.90 | 1,041,765.48 | 380,557.41 |
| Bedroom Furniture | 1,401,372.00 | 1,035,294.02 | 366,077.98 |
| Outdoor Furniture | 1,620,314.60 | 1,260,024.55 | 360,290.05 |
| Candleholders | 1,584,898.40 | 1,228,773.44 | 356,124.96 |
| Pillows | 1,268,933.11 | 932,424.53 | 336,508.57 |
| Bean Bags | 1,334,231.30 | 1,006,079.64 | 328,151.66 |
Across all sales period the highest amount of profit made from a single product is 651,232.19 e.i the Accessories products.
WITH prod_rev AS (
SELECT product_id,
year(order_date) AS order_year,
(order_quantity * unit_price) AS sales,
((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS cost,
(order_quantity * unit_price) - ((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS profit
FROM sales_order
)
SELECT product_name,
round(SUM(sales), 2) AS Total_Sales,
round(SUM(cost), 2) AS Total_Cost,
round(SUM(profit), 2) AS Total_Profit
FROM prod_rev pv
JOIN product p
ON pv.product_id = p.product_id
WHERE order_year = 2020
GROUP BY product_name
ORDER BY Total_Profit DESC;| Product Name | Total Sales | Total Cost | Total Profit |
|---|---|---|---|
| Wall Frames | 916,312.09 | 652,669.15 | 263,642.95 |
| Furniture Cushions | 833,727.90 | 587,628.63 | 246,099.27 |
| Collectibles | 986,642.00 | 741,352.06 | 245,289.95 |
| Serveware | 957,128.50 | 716,216.80 | 240,911.70 |
| Festive | 848,280.31 | 627,086.37 | 221,193.94 |
| Floral | 738,306.50 | 517,185.77 | 221,120.74 |
| Accessories | 859,261.61 | 639,682.37 | 219,579.24 |
| Dinnerware | 776,081.10 | 559,657.50 | 216,423.59 |
| Bathroom Furniture | 807,443.80 | 599,268.91 | 208,174.89 |
| Wreaths | 810,693.30 | 603,893.78 | 206,799.52 |
| Vanities | 743,700.00 | 539,511.54 | 204,188.46 |
| Table Linens | 744,644.70 | 542,231.50 | 202,413.19 |
| Vases | 719,030.60 | 521,997.07 | 197,033.53 |
| TV and video | 687,560.70 | 496,048.10 | 191,512.60 |
| Cocktail Glasses | 693,898.90 | 505,063.79 | 188,835.11 |
| Wine Storage | 720,779.30 | 536,897.73 | 183,881.56 |
| Photo Frames | 699,553.70 | 516,005.86 | 183,547.84 |
| Basketball | 713,014.00 | 532,402.07 | 180,611.93 |
| Bean Bags | 694,327.70 | 514,755.81 | 179,571.89 |
| Bar Tools | 748,115.30 | 568,793.65 | 179,321.64 |
| Mirrors | 740,618.00 | 563,200.83 | 177,417.17 |
| Rugs | 750,574.20 | 577,366.17 | 173,208.03 |
| Platters | 665,417.20 | 493,979.48 | 171,437.72 |
| Stemware | 620,172.10 | 450,011.30 | 170,160.80 |
| Computers | 625,163.60 | 456,713.27 | 168,450.32 |
| Bakeware | 674,200.90 | 508,095.17 | 166,105.73 |
| Outdoor Decor | 553,674.60 | 390,312.69 | 163,361.91 |
| Candles | 586,203.09 | 423,082.05 | 163,121.04 |
| Bedroom Furniture | 604,648.20 | 444,052.22 | 160,595.98 |
| Table Lamps | 567,905.40 | 407,477.79 | 160,427.61 |
| Dining Furniture | 703,446.40 | 546,054.02 | 157,392.38 |
| Blankets | 638,128.10 | 482,461.11 | 155,666.99 |
| Cookware | 479,639.60 | 324,304.36 | 155,335.24 |
| Phones | 620,533.90 | 469,036.48 | 151,497.41 |
| Pendants | 683,748.40 | 532,515.00 | 151,233.40 |
| Wardrobes | 684,840.50 | 535,663.03 | 149,177.48 |
| Home Fragrances | 595,335.20 | 447,076.46 | 148,258.74 |
| Wall Coverings | 592,909.80 | 447,307.65 | 145,602.15 |
| Sculptures | 584,682.20 | 443,362.12 | 141,320.08 |
| Baseball | 593,559.70 | 452,422.93 | 141,136.77 |
| Outdoor Furniture | 672,572.80 | 536,338.96 | 136,233.84 |
| Floor Lamps | 551,946.00 | 418,936.97 | 133,009.03 |
| Clocks | 524,469.30 | 397,002.00 | 127,467.30 |
| Ornaments | 484,738.30 | 362,916.15 | 121,822.15 |
| Audio | 505,769.60 | 388,417.83 | 117,351.77 |
| Candleholders | 495,491.80 | 387,672.19 | 107,819.61 |
| Pillows | 365,552.00 | 262,819.90 | 102,732.11 |
In 2020, more profit was made on the Wall Frames product than other products including Accessories.
WITH prod_rev AS (
SELECT product_id,
year(order_date) AS order_year,
(order_quantity * unit_price) AS sales,
((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS cost,
(order_quantity * unit_price) - ((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS profit
FROM sales_order
)
SELECT product_name,
round(SUM(sales), 2) AS Total_Sales,
round(SUM(cost), 2) AS Total_Cost,
round(SUM(profit), 2) AS Total_Profit
FROM prod_rev pv
JOIN product p
ON pv.product_id = p.product_id
WHERE order_year = 2019
GROUP BY product_name
ORDER BY Total_Profit DESC;| Product Name | Total Sales | Total Cost | Total Profit |
|---|---|---|---|
| Wardrobes | 883,602.70 | 637,043.67 | 246,559.02 |
| Stemware | 850,102.70 | 606,337.88 | 243,764.83 |
| Bathroom Furniture | 824,274.20 | 585,916.95 | 238,357.26 |
| Blankets | 803,584.60 | 581,704.19 | 221,880.41 |
| Ornaments | 731,097.29 | 513,270.65 | 217,826.64 |
| Photo Frames | 810,251.10 | 596,597.95 | 213,653.15 |
| Rugs | 910,925.30 | 699,286.47 | 211,638.83 |
| Vanities | 739,103.80 | 532,101.71 | 207,002.10 |
| Accessories | 774,258.70 | 571,290.68 | 202,968.02 |
| Cocktail Glasses | 704,190.10 | 501,523.34 | 202,666.75 |
| Table Lamps | 812,160.60 | 610,783.29 | 201,377.31 |
| Phones | 774,339.10 | 573,754.57 | 200,584.53 |
| Floor Lamps | 720,095.89 | 519,894.64 | 200,201.25 |
| Mirrors | 769,823.30 | 570,849.25 | 198,974.05 |
| Pendants | 768,804.90 | 578,592.94 | 190,211.96 |
| Platters | 795,437.40 | 606,022.34 | 189,415.06 |
| Collectibles | 672,686.70 | 484,785.87 | 187,900.83 |
| Dinnerware | 653,069.10 | 465,210.35 | 187,858.75 |
| Table Linens | 714,501.40 | 527,203.57 | 187,297.83 |
| Furniture Cushions | 718,286.90 | 534,673.40 | 183,613.50 |
| Wine Storage | 697,094.80 | 513,840.02 | 183,254.78 |
| TV and video | 688,833.70 | 511,551.17 | 177,282.53 |
| Baseball | 659,320.20 | 483,682.32 | 175,637.89 |
| Candles | 629,270.70 | 455,856.48 | 173,414.22 |
| Floral | 715,017.30 | 542,483.93 | 172,533.37 |
| Dining Furniture | 631,213.70 | 459,832.83 | 171,380.87 |
| Serveware | 797,635.00 | 628,027.25 | 169,607.75 |
| Computers | 628,299.20 | 460,868.45 | 167,430.75 |
| Clocks | 664,653.40 | 497,816.70 | 166,836.70 |
| Candleholders | 739,820.70 | 575,704.27 | 164,116.43 |
| Wreaths | 593,894.70 | 431,177.97 | 162,716.73 |
| Home Fragrances | 629,056.30 | 473,323.51 | 155,732.79 |
| Audio | 650,657.10 | 495,531.90 | 155,125.20 |
| Sculptures | 596,949.90 | 443,889.61 | 153,060.29 |
| Bar Tools | 547,738.40 | 396,093.78 | 151,644.62 |
| Festive | 518,861.40 | 368,236.52 | 150,624.88 |
| Outdoor Decor | 510,084.40 | 359,548.13 | 150,536.27 |
| Basketball | 632,761.40 | 490,395.75 | 142,365.65 |
| Pillows | 544,053.40 | 403,033.11 | 141,020.29 |
| Wall Frames | 548,917.60 | 414,858.47 | 134,059.13 |
| Bedroom Furniture | 500,469.90 | 366,597.77 | 133,872.13 |
| Outdoor Furniture | 577,446.20 | 445,727.55 | 131,718.65 |
| Cookware | 554,780.10 | 428,819.57 | 125,960.53 |
| Bakeware | 580,367.40 | 454,439.56 | 125,927.84 |
| Wall Coverings | 495,860.30 | 378,937.06 | 116,923.24 |
| Bean Bags | 432,491.70 | 328,855.70 | 103,636.00 |
| Vases | 338,644.80 | 248,240.06 | 90,404.74 |
WITH prod_rev AS (
SELECT product_id,
year(order_date) AS order_year,
(order_quantity * unit_price) AS sales,
((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS cost,
(order_quantity * unit_price) - ((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS profit
FROM sales_order
)
SELECT product_name,
round(SUM(sales), 2) AS Total_Sales,
round(SUM(cost), 2) AS Total_Cost,
round(SUM(profit), 2) AS Total_Profit
FROM prod_rev pv
JOIN product p
ON pv.product_id = p.product_id
WHERE order_year = 2018
GROUP BY product_name
ORDER BY Total_Profit DESC;| Product Name | Total Sales | Total Cost | Total Profit |
|---|---|---|---|
| Accessories | 725,268.30 | 496,583.37 | 228,684.93 |
| Ornaments | 669,490.80 | 505,049.62 | 164,441.18 |
| Platters | 592,032.10 | 432,487.41 | 159,544.68 |
| Photo Frames | 495,833.51 | 339,850.40 | 155,983.11 |
| Sculptures | 655,869.70 | 500,255.97 | 155,613.73 |
| Cocktail Glasses | 578,806.30 | 430,556.37 | 148,249.93 |
| Table Linens | 522,827.80 | 387,894.53 | 134,933.28 |
| Table Lamps | 433,523.50 | 304,507.90 | 129,015.60 |
| Bar Tools | 525,226.40 | 400,699.77 | 124,526.63 |
| TV and video | 449,020.60 | 324,767.96 | 124,252.64 |
| Wall Coverings | 447,606.90 | 325,026.72 | 122,580.18 |
| Dining Furniture | 511,518.20 | 389,865.66 | 121,652.54 |
| Home Fragrances | 485,582.50 | 365,035.60 | 120,546.90 |
| Clocks | 514,506.40 | 394,012.40 | 120,494.01 |
| Rugs | 469,341.70 | 349,665.40 | 119,676.30 |
| Mirrors | 410,931.10 | 295,094.57 | 115,836.54 |
| Blankets | 429,208.70 | 314,022.13 | 115,186.57 |
| Pendants | 429,114.90 | 317,651.89 | 111,463.01 |
| Festive | 387,641.90 | 276,471.68 | 111,170.22 |
| Stemware | 379,648.80 | 269,866.79 | 109,782.01 |
| Audio | 385,973.60 | 276,758.04 | 109,215.56 |
| Floral | 368,513.40 | 265,772.99 | 102,740.41 |
| Cookware | 402,268.00 | 299,710.63 | 102,557.36 |
| Baseball | 391,213.00 | 289,727.50 | 101,485.50 |
| Wine Storage | 324,548.00 | 224,864.97 | 99,683.04 |
| Bathroom Furniture | 379,615.30 | 281,557.25 | 98,058.05 |
| Vases | 345,009.80 | 247,019.96 | 97,989.84 |
| Pillows | 359,327.70 | 266,571.53 | 92,756.17 |
| Outdoor Furniture | 370,295.60 | 277,958.04 | 92,337.56 |
| Serveware | 316,782.70 | 224,582.93 | 92,199.77 |
| Bakeware | 388,137.70 | 296,519.99 | 91,617.71 |
| Candles | 391,554.70 | 300,505.45 | 91,049.24 |
| Outdoor Decor | 465,475.80 | 376,300.95 | 89,174.85 |
| Collectibles | 390,630.10 | 302,791.16 | 87,838.94 |
| Wreaths | 381,853.10 | 294,873.43 | 86,979.67 |
| Candleholders | 349,585.90 | 265,396.98 | 84,188.92 |
| Floor Lamps | 256,261.60 | 178,091.26 | 78,170.34 |
| Wardrobes | 299,697.70 | 222,957.24 | 76,740.46 |
| Furniture Cushions | 373,096.20 | 296,815.26 | 76,280.94 |
| Dinnerware | 297,962.40 | 224,752.87 | 73,209.53 |
| Basketball | 351,817.00 | 278,673.97 | 73,143.03 |
| Bedroom Furniture | 296,253.90 | 224,644.03 | 71,609.87 |
| Phones | 290,344.50 | 223,466.07 | 66,878.43 |
| Wall Frames | 320,132.70 | 253,790.51 | 66,342.20 |
| Vanities | 307,871.70 | 246,118.94 | 61,752.76 |
| Bean Bags | 207,411.90 | 162,468.13 | 44,943.76 |
| Computers | 168,860.10 | 124,183.76 | 44,676.34 |
Insight
A total of 47 different products was sold over the selected period of which the Accessories products have the highest total number of order, on the cost side an average of 1,511.28 was paid for the Mirror product which was the highest overall. Again the Accessories products was the most profitable product overall for all selected years and also in 2020.