Same Brand Purchases

#Meta/Facebook

The marketing department is aiming its next promotion at customers who have purchased products from two particular brands: Fort West and Golden.

You have been asked to prepare a list of customers who purchased products from both brands.

table name: online_products


table name: online_orders


Solution:

select o.customer_id
from online_products as p
join online_orders as o on p.product_id = o.product_id
where p.brand_name in('Fort West','Golden')
group by o.customer_id
having count(distinct p.brand_name)=2
order by o.customer_id

Output:


SQL Script:

USE [StrataScratch]
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
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
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

Comments (0)
Questions List