SQLTeam.com | Weblogs | Forums

Query Help Please

#1

Hi,

I'm new to SQL and have a basic understanding. I had web interview and the interviewer asked the following question, which I wasn't too sure of what to do, can someone please answer it for me.

Interviewer:

1st table - Customer: customer_id, name, reseller_flag;

2nd table - Order : order_id, order_item_id, customer_id, date , product_id, revenue 4:05 PM

3rd table - Product: product_id, name, category

Interviewer: Using SQL , answer the following questions. You have write it in one statement (it could include sub-queries)

Interviewer: 1. Which product category has the highest percent of reseller customers

#2

Something Like this:-

Summary 
declare @Customer table (customer_id int identity(1,1) not null,name varchar(255),reseller_flag varchar(255))
declare @Order table (order_id int identity(1,1) not null,order_item_id int,customer_id int, [date] date, product_id int)
declare @Product table (product_id int identity(1,1) not null,name varchar(255),category varchar(255))


	insert into @Customer(name,reseller_flag) 
	values
	 ('Lee','NO')
	,('Mark','Yes')

	insert into @Product(name,category) 
	values
	 ('Hair Gel','BodyCare')
	,('Shampoo','BodyCare')
	,('Soap','BodyCare')
	,('Sweet','Nutrition')

insert into @Order(order_item_id,customer_id,[date],product_id) 
values
 (101,1,getdate(),4)
,(102,1,dateadd(day,-1,getdate()),2)
,(103,2,dateadd(day,-3,getdate()),1)
,(104,2,dateadd(day,-10,getdate()),2)
,(105,2,dateadd(day,-15,getdate()),3)
,(106,2,dateadd(day,-17,getdate()),4)


--select * from @Customer
--select * from @Product
--select * from @Order


Select 
category
,Total
,convert(varchar(10),round(convert(float,Total)/convert(float,Tot)*100,2)) + '%' as [Percentage]
from(
			select 
			 category 
			 ,Tot
			,count(*) as Total
			from @Order a
			left join @Product b on a.product_id = b.product_id
			left join @Customer c on a.customer_id = c.customer_id
			cross apply (
							select 
							sum(case when reseller_flag = 'Yes' then 1 else 0 end) as Tot  
							from @Order x
							left join @Customer cx on x.customer_id = cx.customer_id
							where reseller_flag = 'Yes'
			)_
			where reseller_flag = 'Yes'
			group by 
			category
			,Tot
)tbl
#3 
SELECT TOP (1)
    p.category,
    /*
    SUM(CASE WHEN c.reseller_flag = 'Yes' THEN 1 ELSE 0 END) AS reseller_count,
    SUM(CASE WHEN c.reseller_flag = 'No' THEN 1 ELSE 0 END) AS non_reseller_count,
    */
    CAST(SUM(CASE WHEN c.reseller_flag = 'Yes' THEN 1 ELSE 0 END) * 100.0 / 
        SUM(1) AS decimal(4, 2)) AS reseller_percent
FROM @Customer c
INNER JOIN @Order o ON o.customer_id = c.customer_id
INNER JOIN @Product p ON p.product_id = o.product_id
GROUP BY p.category
ORDER BY SUM(CASE WHEN c.reseller_flag = 'Yes' THEN 1 ELSE 0 END) * 
    100.0 / SUM(1) DESC