The next question, despite it’s simple appearance, can be quite a challenge, even for experienced query writers.
The following Sales table represents the sales of a mobile phones store. As you can see, each row represents one purchase, and has two columns: name – stores the customer’s name, and product_name – stores the product that was purchased by this customer.
Write a query that will retrieve, for a specific customer, all of the other customers that have purchased the exact same products as he has. For example, if we check who bought the same products as John, the result of this query will be Christopher (who is the only customer that has purchased the same products as John has, in addition to another product). If we check who bought the same products as Tim, the result of this query will be Sandra (who is the only customer that has purchased the same products as Tim has, in addition to another product).
CREATE TABLE [dbo].[sales](
[name] [varchar](25) ,
[product_name] [varchar](25)
)
GO
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'John', N'Iphone6')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'John', N'Iphone6')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'John', N'Iphone5')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'John', N'Iphone4')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'Tim', N'Iphone6')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'Tim', N'Samsung Galaxy S5')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'Christopher', N'Iphone6')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'Christopher', N'Iphone5')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'Christopher', N'Iphone4')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'Christopher', N'Samsung Galaxy S4')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'Sandra', N'Samsung Galaxy S4')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'Sandra', N'Iphone4')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'Sandra', N'Samsung Galaxy S5')
INSERT [dbo].[sales] ([name], [product_name]) VALUES (N'Sandra', N'Iphone6')
SELECT DISTINCT S.name FROM sales s WHERE NOT EXISTS ( SELECT s1.product_name FROM sales s1 WHERE s1.name = 'John' AND NOT EXISTS ( SELECT s3.product_name FROM sales s3 WHERE s3.name = s.name AND s3.name s1.name AND s3.product_name = s1.product_name ) )