SQL Puzzle | The Facebook Puzzle # 2 ( Count Characters in a string Puzzle )

24 Thursday Dec 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL SERVER, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

≈ 5 Comments

Tags

SQL Puzzle | The Facebook Puzzle # 2

Puzzle Statement

This puzzle was asked in social media ( Facebook ), Here you have a string and you have to count how many times each character appear in the string. Please check sample input and expected output.

Sample Input String

‘aaaaaabbbbbccccc’

Expected Output

Charx	CharxCounts
a	6
b	5
c	5

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Simple SELECT & Numbers Table

--

DECLARE @str AS VARCHAR(100) = 'aaaaaabbbbbccccc'

SELECT SUBSTRING(@str,number,1) Charx , COUNT(SUBSTRING(@str,number,1)) CharxCounts
FROM 
( 
	SELECT DISTINCT number FROM
	MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@str)
)v
GROUP BY SUBSTRING(@str,number,1)

--

In the above solution I am using numbers table. Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The Facebook Puzzle # 1

22 Tuesday Dec 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Server Interview Questions, T SQL Puzzles

≈ Leave a comment

Tags

SQL Puzzle | The Facebook Puzzle # 1

Puzzle Statement

This puzzle was asked in social media ( Facebook ), Here you have a string and you have to insert a character(_) underscore after every third character in the string. Please check sample input and expected output.

Sample Input String

vinod kumar reddy ”k

Expected Output

vin_od _kum_ar _red_dy _’k

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Simple SELECT & Numbers Table

--

DECLARE @str AS VARCHAR(100) = 'vinod kumar reddy ''k'
DECLARE @outs AS VARCHAR(500) = ''

SELECT @outs = Concat(@outs,'',CASE WHEN number % 3 = 0 THEN CONCAT(SUBSTRING(@str,number,1) , '_') ELSE SUBSTRING(@str,number,1) END)
FROM 
( 
	SELECT DISTINCT number FROM
	MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@str)
)v
SELECT @outs

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | Find Different Values Puzzle

21 Monday Dec 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

≈ 2 Comments

Tags

SQL Puzzle | Find Different Values Puzzle

Puzzle Statement

In this puzzle you have to find rows where one of the column value is 1. We don’t need values where both column have values 1. Please check sample input and expected output for details. One of my old friend asks this question in all the technical interviews he used to take.

Sample Input

a	b
0	1
1	2
1	1
2	3
5	3

Expected output

a	b
0	1
1	2

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE TestXOR
(
	 a SmallINT 
	,b SmallINT
)
GO


INSERT TestXOR
VALUES 
(0,1),
(1,2),
(1,1),
(2,3),
(5,3)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Simple SELECT

--

SELECT a,b FROM TestXOR
WHERE ( a = 1 AND b != 1 ) OR ( a != 1 AND b = 1 )

--

SOLUTION 2 | Using XOR Operator & Case

--

SELECT a,b FROM TestXOR
WHERE (CASE WHEN a = 1 THEN 1 ELSE 0 END) ^ 
	  ( CASE WHEN b = 1 THEN 1 ELSE 0 END ) = 1

--

SOLUTION 3 | Using Case Trick

--

SELECT a,b FROM TestXOR
WHERE (CASE WHEN a = 1 THEN 1 ELSE 0 END) +
	  ( CASE WHEN b = 1 THEN 1 ELSE 0 END ) = 1

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | Get Weeks (Start Date – End Date) for a Month & Year

18 Friday Dec 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Server Interview Questions, T SQL Puzzles

≈ 2 Comments

Tags

SQL Puzzle | Get Weeks (Start Date – End Date) for a particular month & year

Puzzle Statement

Recently I was asked by a developer how we can find weeks of a particular month and year. Say you want to find out weeks for December month for 2015 year.

Rules
1. Week starts from a Sunday and ends on a Saturday
2. First week start date should be first day of the month
3. Last week end date should be last day of the month

Sample Input

Month = 12 i.e. December
Year = 2015

Expected output

WeekStart	WeekEnd
01-12-2015	06-12-2015
07-12-2015	13-12-2015
14-12-2015	20-12-2015
21-12-2015	27-12-2015
28-12-2015	31-12-2015

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION #

--


DECLARE @Mon AS INT = 12 , @Year AS INT = 2015
;WITH CTE AS
(
     SELECT DISTINCT number, DATEFROMPARTS(@Year,@Mon,Number) Dt
     ,DATEADD(d, (8 - datepart(WEEKDAY, DATEFROMPARTS(@Year,@Mon,Number))), DATEFROMPARTS(@Year,@Mon,Number)) Wk
     FROM
           MASTER..SPT_Values
     WHERE
          Number > 0 AND number < DAY(EOMONTH(CONCAT(@Year,'/',@Mon,'/','01'))) ),
          CTE1 AS ( SELECT CASE WHEN number = 1 THEN DATEADD(d,-1,Dt) ELSE Dt End Dt 
          ,CASE WHEN Wk > EOMONTH(dt) THEN EOMONTH(dt) ELSE WK END Wk 		 
	 FROM CTE
)
SELECT MIN(DATEADD(d,1,Dt)) WeekStart , Wk WeekEnd FROM CTE1
GROUP BY Wk
ORDER BY WeekStart

--

Add a comment if you have any other solution in mind. I would love to learn it.

We all need to learn.

Enjoy !!! Keep Learning

Pawan Kumar Khowal

Http://MSBISkills.com

Improving my SQL BI Skills

Monthly Archives: December 2015

SQL Puzzle | The Facebook Puzzle # 2 ( Count Characters in a string Puzzle )

SQL Puzzle | The Facebook Puzzle # 1

SQL Puzzle | Find Different Values Puzzle

SQL Puzzle | Get Weeks (Start Date – End Date) for a Month & Year

Share this

Share this

Share this

Share this