#2158
The marketing department is evaluating the most effective promotional strategies for each product family.
You have been asked to find the total sales by media type for each category as a percentage of the overall sales for that family. For example the product family ELECTRONICS could be sold 57% through INTERNET and 43% through BROADCAST.
Your output should include the product family listed alphabetically, the media type, and the calculated percentage of sales rounded to the nearest whole number ordered from highest to lowest.
table name: online_orders
table name: online_sales_promotions
table name: online_products
Step 1: Join all 3 tables.
select *
from online_orders as o
join online_sales_promotions as p on o.promotion_id = p.promotion_id
join online_products as pp on o.product_id = pp.product_id
Step 2: To calculate the cost of product sold (online_orders.units_sold * online_orders.cost_in_dollars)
Step 3: To know the total cost by product_family
select pp.product_family,sum(o.units_sold*o.cost_in_dollars) as total
from online_orders as o
join online_sales_promotions as p on o.promotion_id = p.promotion_id
join online_products as pp on o.product_id = pp.product_id
group by pp.product_family
Step 4: To know the total cost by product_family and media_type
select pp.product_family, p.media_type,sum(o.units_sold*o.cost_in_dollars) as total
from online_orders as o
join online_sales_promotions as p on o.promotion_id = p.promotion_id
join online_products as pp on o.product_id = pp.product_id
group by p.media_type,pp.product_family
Step 5: Now, we need to calculate in CONSUMABEL product_family Broadcast is of what percentage.
- 196 is what percent of 677
- 301 is what percent of 677
- 180 is what percent of 180
- 75 is what percent of 226
- 106 is what percent of 226
- 40 is what percent of 226
- 5 is what percent of 226
Step 6: To know the percentage apply formula
100 * totalByMedia / totalByFamily
Final Solution:
with cte as(
select pp.product_family, p.media_type,sum(o.units_sold*o.cost_in_dollars) as total
from online_orders as o
join online_sales_promotions as p on o.promotion_id = p.promotion_id
join online_products as pp on o.product_id = pp.product_id
group by p.media_type,pp.product_family
)
select product_family,media_type,
cast(round((100.0 * total)/sum(total) over (partition by product_family),0) as int) as percentage
from cte
order by product_family
Final Output:
SQL Script:
USE [StrataScratch]
GO
/****** Object: Table [dbo].[online_orders] Script Date: 30-01-2024 14:38:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[online_orders](
[product_id] [int] NULL,
[promotion_id] [int] NULL,
[cost_in_dollars] [int] NULL,
[customer_id] [int] NULL,
[date] [datetime] NULL,
[units_sold] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[online_products] Script Date: 30-01-2024 14:38:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[online_products](
[product_id] [int] NULL,
[product_class] [varchar](50) NULL,
[brand_name] [varchar](50) NULL,
[is_low_fat] [varchar](50) NULL,
[is_recyclable] [varchar](50) NULL,
[product_category] [int] NULL,
[product_family] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[online_sales_promotions] Script Date: 30-01-2024 14:38:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[online_sales_promotions](
[promotion_id] [int] NULL,
[start_date] [datetime] NULL,
[end_date] [datetime] NULL,
[media_type] [varchar](50) NULL,
[cost] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (1, 1, 2, 1, CAST(N'2022-04-01T00:00:00.000' AS DateTime), 4)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (3, 3, 6, 3, CAST(N'2022-05-24T00:00:00.000' AS DateTime), 6)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (1, 2, 2, 10, CAST(N'2022-05-01T00:00:00.000' AS DateTime), 3)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (1, 2, 3, 2, CAST(N'2022-05-01T00:00:00.000' AS DateTime), 9)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (2, 2, 10, 2, CAST(N'2022-05-01T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (9, 3, 1, 2, CAST(N'2022-05-31T00:00:00.000' AS DateTime), 5)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (6, 1, 4, 1, CAST(N'2022-04-07T00:00:00.000' AS DateTime), 8)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (6, 2, 2, 1, CAST(N'2022-05-01T00:00:00.000' AS DateTime), 12)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (3, 3, 5, 1, CAST(N'2022-05-25T00:00:00.000' AS DateTime), 4)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (3, 3, 6, 2, CAST(N'2022-05-25T00:00:00.000' AS DateTime), 6)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (3, 3, 7, 3, CAST(N'2022-05-25T00:00:00.000' AS DateTime), 7)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (2, 2, 12, 3, CAST(N'2022-05-01T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (8, 2, 4, 3, CAST(N'2022-05-01T00:00:00.000' AS DateTime), 4)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (9, 1, 1, 10, CAST(N'2022-04-07T00:00:00.000' AS DateTime), 2)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (9, 5, 2, 3, CAST(N'2022-04-06T00:00:00.000' AS DateTime), 20)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (10, 1, 3, 2, CAST(N'2022-04-07T00:00:00.000' AS DateTime), 4)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (10, 1, 3, 1, CAST(N'2022-04-01T00:00:00.000' AS DateTime), 5)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (3, 1, 6, 1, CAST(N'2022-04-02T00:00:00.000' AS DateTime), 10)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (2, 1, 10, 10, CAST(N'2022-04-04T00:00:00.000' AS DateTime), 8)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (2, 1, 11, 3, CAST(N'2022-04-05T00:00:00.000' AS DateTime), 6)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (4, 2, 2, 2, CAST(N'2022-05-02T00:00:00.000' AS DateTime), 7)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (5, 2, 8, 1, CAST(N'2022-05-02T00:00:00.000' AS DateTime), 7)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (2, 3, 13, 1, CAST(N'2022-05-30T00:00:00.000' AS DateTime), 3)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (1, 1, 2, 2, CAST(N'2022-04-07T00:00:00.000' AS DateTime), 3)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (10, 2, 2, 3, CAST(N'2022-05-02T00:00:00.000' AS DateTime), 9)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (11, 1, 5, 1, CAST(N'2022-04-03T00:00:00.000' AS DateTime), 9)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (5, 1, 7, 10, CAST(N'2022-04-02T00:00:00.000' AS DateTime), 9)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (5, 4, 8, 1, CAST(N'2022-06-06T00:00:00.000' AS DateTime), 8)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (1, 1, 2, 2, CAST(N'2022-04-02T00:00:00.000' AS DateTime), 9)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (5, 2, 8, 15, CAST(N'2022-05-01T00:00:00.000' AS DateTime), 2)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (8, 2, 4, 3, CAST(N'2022-05-11T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[online_orders] ([product_id], [promotion_id], [cost_in_dollars], [customer_id], [date], [units_sold]) VALUES (8, 2, 4, 3, CAST(N'2022-06-11T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (1, N'ACCESSORIES', N'Fort West', N'N', N'N', 3, N'GADGET')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (2, N'DRINK', N'Fort West', N'N', N'Y', 2, N'CONSUMABLE')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (3, N'FOOD', N'Fort West', N'Y', N'N', 1, N'CONSUMABLE')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (4, N'DRINK', N'Golden', N'Y', N'Y', 3, N'CONSUMABLE')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (5, N'FOOD', N'Golden', N'Y', N'N', 2, N'CONSUMABLE')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (6, N'FOOD', N'Lucky Joe', N'N', N'Y', 3, N'CONSUMABLE')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (7, N'ELECTRONICS', N'Lucky Joe', N'N', N'Y', 2, N'GADGET')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (8, N'FURNITURE', N'Lucky Joe', N'N', N'Y', 3, N'GADGET')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (9, N'ELECTRONICS', N'Lucky Joe', N'N', N'Y', 2, N'GADGET')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (10, N'FURNITURE', N'American Home', N'N', N'Y', 2, N'GADGET')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (11, N'FURNITURE', N'American Home', N'N', N'Y', 3, N'GADGET')
GO
INSERT [dbo].[online_products] ([product_id], [product_class], [brand_name], [is_low_fat], [is_recyclable], [product_category], [product_family]) VALUES (12, N'ELECTRONICS', N'American Home', N'N', N'Y', 3, N'ACCESSORY')
GO
INSERT [dbo].[online_sales_promotions] ([promotion_id], [start_date], [end_date], [media_type], [cost]) VALUES (1, CAST(N'2022-04-01T00:00:00.000' AS DateTime), CAST(N'2022-04-07T00:00:00.000' AS DateTime), N'Internet', 25000)
GO
INSERT [dbo].[online_sales_promotions] ([promotion_id], [start_date], [end_date], [media_type], [cost]) VALUES (2, CAST(N'2022-05-01T00:00:00.000' AS DateTime), CAST(N'2022-05-02T00:00:00.000' AS DateTime), N'Broadcast', 14000)
GO
INSERT [dbo].[online_sales_promotions] ([promotion_id], [start_date], [end_date], [media_type], [cost]) VALUES (3, CAST(N'2022-05-24T00:00:00.000' AS DateTime), CAST(N'2022-06-01T00:00:00.000' AS DateTime), N'Print', 32000)
GO
INSERT [dbo].[online_sales_promotions] ([promotion_id], [start_date], [end_date], [media_type], [cost]) VALUES (4, CAST(N'2022-06-05T00:00:00.000' AS DateTime), CAST(N'2022-06-10T00:00:00.000' AS DateTime), N'Broadcast', 18000)
GO
INSERT [dbo].[online_sales_promotions] ([promotion_id], [start_date], [end_date], [media_type], [cost]) VALUES (5, CAST(N'2022-07-06T00:00:00.000' AS DateTime), CAST(N'2022-07-12T00:00:00.000' AS DateTime), N'Outdoor', 20000)
GO