#Meta/Facebook
In order to improve customer segmentation efforts for users interested in purchasing furniture, you have been asked to find customers who have purchased the same items of furniture.
Output the product_id, brand_name, unique customer ID's who purchased that product, and the count of unique customer ID's who purchased that product. Arrange the output in descending order with the highest count at the top.
table name: online_products
table name: online_orders
Solution:
select *, count(customer_id) over(partition by product_id) as cnt from(
select distinct p.product_id,p.brand_name,
o.customer_id
from online_products as p
join online_orders as o
on p.product_id = o.product_id
where p.product_class = 'FURNITURE'
) t
order by cnt desc
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