A Microsoft Azure Supercloud customer is a
company which buys at least 1 product from each product category.
Write a query to report the company ID which is a
Supercloud customer.
table name: customer_contracts
table name: products
Solution:
select c.customer_id from customer_contracts as c
join products as p on c.product_id = p.product_id
group by c.customer_id
having count(distinct p.product_category) = (select count(distinct
product_category) from products)
Output:
SQL Script:
CREATE TABLE [dbo].[customer_contracts](
[customer_id]
[int] NULL,
[product_id]
[int] NULL,
[amount]
[int] NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[products](
[product_id]
[int] NULL,
[product_category]
[nvarchar](50) NULL,
[product_name]
[nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[customer_contracts] ([customer_id], [product_id], [amount]) VALUES (1, 1, 1000)
GO
INSERT [dbo].[customer_contracts] ([customer_id], [product_id], [amount]) VALUES (1, 3, 2000)
GO
INSERT [dbo].[customer_contracts] ([customer_id], [product_id], [amount]) VALUES (1, 5, 1500)
GO
INSERT [dbo].[customer_contracts] ([customer_id], [product_id], [amount]) VALUES (2, 2, 3000)
GO
INSERT [dbo].[customer_contracts] ([customer_id], [product_id], [amount]) VALUES (2, 6, 2000)
GO
INSERT [dbo].[products] ([product_id], [product_category], [product_name]) VALUES (1, N'Analytics', N'Azure
Databricks')
GO
INSERT [dbo].[products] ([product_id], [product_category], [product_name]) VALUES (2, N'Analytics', N'Azure Stream
Analytics')
GO
INSERT [dbo].[products] ([product_id], [product_category], [product_name]) VALUES (3, N'Containers', N'Azure
Kubernetes Service')
GO
INSERT [dbo].[products] ([product_id], [product_category], [product_name]) VALUES (4, N'Containers', N'Azure Service
Fabric')
GO
INSERT [dbo].[products] ([product_id], [product_category], [product_name]) VALUES (5, N'Compute', N'Virtual
Machines')
GO
INSERT [dbo].[products] ([product_id], [product_category], [product_name]) VALUES (6, N'Compute', N'Azure
Functions')
GO