February 16, 2016 at 11:13 pm
I am a newbie in sql. recently i came across a set of questions specific to query a denormalised table. Can someone help me how to answer these questions
Customer_id Order_id Order_line_id Product_type Order_Date
C1000 O1000 L100 Textbook 1/1/2016
C1000 O1000 L101 Ebook 1/1/2016
C1000 O1000 L102 Texbook 1/1/2016
C1000 O1001 L103 Subscription 1/2/2016
C1001 O1002 L104 Ebook 1/2/2016
C1001 O1002 L105 Ebook 1/2/2016
C1002 O1003 L106 Textbook 1/3/2016
C1002 O1003 L107 Textbook 1/3/2016
• I would like to find out total customer who ordered the products in Jan 2016
• I would like to know number of customers who placed more than one order in Jan 2016
• I would like to find out number of orders that have Ebook as well as Textbook ordered together as a part of same order
• I would like to find out number of new customers who purchased the product for the first time in Jan 2016
February 17, 2016 at 12:45 am
It should be something like these queries:
select count(distinct Customer_id) [Number of customers]
from [dbo].[ordertable] t
where convert(date,Order_Date) between '2016-01-01 00:00:00.000' and '2016-01-31 23:59:59.997';
;with cte1 as
(
select Customer_id, count(*) [Count]
from [dbo].[ordertable] t
where convert(date,Order_Date) between '2016-01-01 00:00:00.000' and '2016-01-31 23:59:59.997'
group by Customer_id
having count(*) > 1
) select count(distinct Customer_id) from cte1;
with cte2 as
(
select Order_id, count(*) [Count]
from [dbo].[ordertable] t
where Product_type in ('Ebook','Textbook') and convert(date,Order_Date) between '2016-01-01 00:00:00.000' and '2016-01-31 23:59:59.997'
group by Order_id
having count(*) >1
) select count(Order_id) from cte2;
;with cte3 as
(
select Customer_id, min(Order_Date) [Order_Date]
from [dbo].[ordertable] t
where convert(date,Order_Date) between '2016-01-01 00:00:00.000' and '2016-01-31 23:59:59.997'
group by Customer_id
)
select count(distinct Customer_id) [Count] from cte3;
I've edited the previous code so that it does run now. However, I didn't expect that for Order_Date you have varchar(50) definition. It should be date if you want to directly apply the DATE functions of T-SQL.
Igor Micev,
My blog: www.igormicev.com
February 17, 2016 at 8:11 pm
sushantkatte (2/16/2016)
I am a newbie in sql. recently i came across a set of questions specific to query a denormalised table. Can someone help me how to answer these questionsCustomer_id Order_id Order_line_id Product_type Order_Date
C1000 O1000 L100 Textbook 1/1/2016
C1000 O1000 L101 Ebook 1/1/2016
C1000 O1000 L102 Texbook 1/1/2016
C1000 O1001 L103 Subscription 1/2/2016
C1001 O1002 L104 Ebook 1/2/2016
C1001 O1002 L105 Ebook 1/2/2016
C1002 O1003 L106 Textbook 1/3/2016
C1002 O1003 L107 Textbook 1/3/2016
• I would like to find out total customer who ordered the products in Jan 2016
• I would like to know number of customers who placed more than one order in Jan 2016
• I would like to find out number of orders that have Ebook as well as Textbook ordered together as a part of same order
• I would like to find out number of new customers who purchased the product for the first time in Jan 2016
We need to know what the table definition is (ie CREATE TABLE statement).
The other thing is that this looks like homework. I can't speak for others but this is on you. Please show what you've tried.
--Jeff Moden
February 17, 2016 at 9:39 pm
hi @
Jeff Moden. this is not a homework question. This questions were asked to me in an interview. i have created the table definition from the question itself. so far i have tried these queries
• I would like to find out total customer who ordered Chegg products in Jan 2016
select count(distinct Customer_id) from Orders where Order_Date between 1/1/2016 and 1/31/2016
• I would like to know number of customers who placed more than one order in Jan 2016
select count(Customer_id),Order_id,Count(Order_id) from Orders
where order_date = 1/1/2016
group by Order_id
having count(Order_id)> 1
• I would like to find out number of orders that have Ebook as well as Textbook ordered together as a part of same order
Not able to figure out
• I would like to find out number of new customers who purchased Chegg product for the first time in Jan 2016
not able to figure out
This is the table definition that i have created from the question :
CREATE TABLE [dbo].[ordertable](
[Customer_id] [nvarchar](50) NULL,
[order_id] [nvarchar](50) NULL,
[order_line_id] [nvarchar](50) NULL,
[product_type] [varchar](50) NULL,
[Order_Date] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1000', N'O1000', N'L100', N'Textbook', N'1/1/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1000', N'O1000', N'L101', N'Ebook', N'1/1/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1000', N'O1000', N'L102', N'Texbook', N'1/1/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1000', N'O1001', N'L103', N'Subscription', N'1/2/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1001', N'O1002', N'L104', N'Ebook', N'1/2/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1001', N'O1002', N'L105', N'Ebook', N'1/2/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1002', N'O1003', N'L106', N'Textbook', N'1/3/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1002', N'O1003', N'L107', N'Textbook', N'1/3/2016')
GO
February 18, 2016 at 12:47 pm
Your basic problem is not in the queries, but in the table. Look at the data and then at the data types you used for the columns. Choosing inappropriate data types makes it incredible hard to write good queries.
Also, and excuse me for being blunt, if those were interview questions for a job that involves any serious SQL Server work and you do not know the answers, then you are not qualified for that job and you should not have applied. I would not even bother to ask questions at this level for an interview - this is the level I expect any candidate to have without me having to check it. The questions I ask are targeted at assessing how far above that level the candidate is.
February 18, 2016 at 2:17 pm
I think Hugo hit the nail on the head. I can't believe those questions were even in an interview. There are a number of good stairway series on this site that will help get you going with SQL Server.
February 18, 2016 at 10:07 pm
Ed Wagner (2/18/2016)
I think Hugo hit the nail on the head. I can't believe those questions were even in an interview. There are a number of good stairway series on this site that will help get you going with SQL Server.
At least they have more meat to them than asking a DBA how to get the current date and time using T-SQL... and them not knowing.
Shifting gears a bit, I know a whole lot of people that would get these questions wrong. I also believe the OP already knows that they failed the interview but is also trying to get better. The OP did say they were a newbie at SQL and we don't even know if the OP was going for anything they were led to believe involved any SQL. I couldn't get to this one today. I'll be back.
--Jeff Moden
February 19, 2016 at 2:48 am
Jeff Moden (2/18/2016)
Ed Wagner (2/18/2016)
I think Hugo hit the nail on the head. I can't believe those questions were even in an interview. There are a number of good stairway series on this site that will help get you going with SQL Server.At least they have more meat to them than asking a DBA how to get the current date and time using T-SQL... and them not knowing.
Shifting gears a bit, I know a whole lot of people that would get these questions wrong. I also believe the OP already knows that they failed the interview but is also trying to get better. The OP did say they were a newbie at SQL and we don't even know if the OP was going for anything they were led to believe involved any SQL. I couldn't get to this one today. I'll be back.
Just for the record, I also believe that the OP has failed the interview and tries to prepare for future interviews by seeking answers. That's why I pointed him/her in the direction of data types as a good area for them to explore.
I also made a harsh comment on the level of the questions, and that was deliberate. I won't begrudge anyone a job, but people need to be aware of their actual level. I truly hope that this person finds a job soon, but I also hope that it will be a job that does not involve any SQL, unless it is an absolute junior position with a lot of mentoring and training. I want this person to be aware that (s)he needs to learn a lot before they should apply for SQL Server jobs.
My advise to the OP: If you already have a job but want to move to SQL Server, then retain your job and use your spare time to train. Read blogs, install SQL Server (express edition is free, developer edition costs just a few dozen bucks) on your home computer, visit forums, copy/paste code you find anywhere (and, most important, play with it and try to understand it), buy a Pluralsight subscription, etc etc etc.
If you currently do not have a job and would like to get a SQL Server career started, find a temporary job in another area to pay the bills and then follow my advise above.
Do not try to bluff your way into a job that you are not qualified for. It will come back to bite you, and you might even take down a company with you in your fall. Just don't go there.
February 19, 2016 at 9:46 am
sushantkatte (2/16/2016)
I am a newbie in sql. recently i came across a set of questions specific to query a denormalised table. Can someone help me how to answer these questionsCustomer_id Order_id Order_line_id Product_type Order_Date
C1000 O1000 L100 Textbook 1/1/2016
C1000 O1000 L101 Ebook 1/1/2016
C1000 O1000 L102 Texbook 1/1/2016
C1000 O1001 L103 Subscription 1/2/2016
C1001 O1002 L104 Ebook 1/2/2016
C1001 O1002 L105 Ebook 1/2/2016
C1002 O1003 L106 Textbook 1/3/2016
C1002 O1003 L107 Textbook 1/3/2016
• I would like to find out total customer who ordered the products in Jan 2016
• I would like to know number of customers who placed more than one order in Jan 2016
• I would like to find out number of orders that have Ebook as well as Textbook ordered together as a part of same order
• I would like to find out number of new customers who purchased the product for the first time in Jan 2016
Just a quick question, are the dates above in mm/dd/yyyy format or dd/mm/yyyy format? Without stating this it actually becomes more difficult to provide an answer.
February 19, 2016 at 10:51 am
it's a star schema. It's just normalized a different way. One relatively painless way into understanding some of star schemas is Rob Collie's book "DAX Formulas for Power Pivot" (yes, I know it doesn't sound like it, but he walks you through creating the whole design.)
The other really good reference, but much more in depth is Christopher Adamson's Star Schema: The Complete Reference.
February 19, 2016 at 11:07 am
sushantkatte (2/17/2016)
hi @Jeff Moden. this is not a homework question. This questions were asked to me in an interview. i have created the table definition from the question itself. so far i have tried these queries
• I would like to find out total customer who ordered Chegg products in Jan 2016
select count(distinct Customer_id) from Orders where Order_Date between 1/1/2016 and 1/31/2016
• I would like to know number of customers who placed more than one order in Jan 2016
select count(Customer_id),Order_id,Count(Order_id) from Orders
where order_date = 1/1/2016
group by Order_id
having count(Order_id)> 1
• I would like to find out number of orders that have Ebook as well as Textbook ordered together as a part of same order
Not able to figure out
• I would like to find out number of new customers who purchased Chegg product for the first time in Jan 2016
not able to figure out
This is the table definition that i have created from the question :
CREATE TABLE [dbo].[ordertable](
[Customer_id] [nvarchar](50) NULL,
[order_id] [nvarchar](50) NULL,
[order_line_id] [nvarchar](50) NULL,
[product_type] [varchar](50) NULL,
[Order_Date] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1000', N'O1000', N'L100', N'Textbook', N'1/1/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1000', N'O1000', N'L101', N'Ebook', N'1/1/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1000', N'O1000', N'L102', N'Texbook', N'1/1/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1000', N'O1001', N'L103', N'Subscription', N'1/2/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1001', N'O1002', N'L104', N'Ebook', N'1/2/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1001', N'O1002', N'L105', N'Ebook', N'1/2/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1002', N'O1003', N'L106', N'Textbook', N'1/3/2016')
GO
INSERT [dbo].[ordertable] ([Customer_id], [order_id], [order_line_id], [product_type], [Order_Date]) VALUES (N'C1002', N'O1003', N'L107', N'Textbook', N'1/3/2016')
GO
Is that the actual table they gave you or did you make it up? I ask because it's normally a terribly bad practice to store dates and times in a VARCHAR column.
--Jeff Moden
February 19, 2016 at 9:06 pm
@
Lynn Pettis. the dates are in mm/dd/yy format
February 19, 2016 at 9:21 pm
HI
@hugo Kornelis. thank you for your kind suggestions and advise. i have just started to explore the sql server world. obviously i did not crack the interview. i only posted these questions after trying myself for some time but could not get the answers and wanted to learn them. I will try to follow your kind advise.
February 20, 2016 at 2:34 am
@
Jeff Moden. this is not the actual table that was given to me. i was given the question in this form and was asked to write queries looking at this structure
Customer_id Order_id Order_line_id Product_type Order_Date
C1000 O1000 L100 Textbook 1/1/2016
C1000 O1000 L101 Ebook 1/1/2016
C1000 O1000 L102 Texbook 1/1/2016
C1000 O1001 L103 Subscription 1/2/2016
C1001 O1002 L104 Ebook 1/2/2016
C1001 O1002 L105 Ebook 1/2/2016
C1002 O1003 L106 Textbook 1/3/2016
C1002 O1003 L107 Textbook 1/3/2016
I created the table from this form
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply