SQL Puzzle | The COUNT Puzzle – A SINGLE SELECT ? | SQL Interview Question

Tags

SQL Puzzle | The COUNT Puzzle – A SINGLE SELECT | SQL Interview Question

In this puzzle you have to find out how many new customer have joined on each date and how many customers have left on the that date. The challenge is to do this in a SINGLE SELECT. For more details please see the sample input and expected output.

Sample Input

NewCustomer

JoiningDate	CustomerName
2018-06-04 00:00:00.000	Vikas
2018-06-05 00:00:00.000	Avtaar
2018-06-07 00:00:00.000	Pawan

OldCustomer

LeavingDate	CustomerName
2018-06-04 00:00:00.000	Akshi
2018-06-04 00:00:00.000	Sahil
2018-06-06 00:00:00.000	Pranjal
2018-06-07 00:00:00.000	Mayank

Expected Output

Date	#CustomerJoined	#CustomerLeft
2018-06-04 00:00:00.000	1	2
2018-06-05 00:00:00.000	1	0
2018-06-06 00:00:00.000	0	1
2018-06-07 00:00:00.000	1	1

Script – DDL and INSERT Sample Data

--

CREATE TABLE NewCustomer
(
	 JoiningDate DATETIME
	,CustomerName VARCHAR(20)
)
GO

INSERT INTO NewCustomer VALUES
('2018-06-04','Vikas'),
('2018-06-05','Avtaar'),
('2018-06-07','Pawan')
GO

CREATE TABLE OldCustomer
(
	 LeavingDate DATETIME
	,CustomerName VARCHAR(20)
)
GO

INSERT INTO OldCustomer VALUES
('2018-06-04','Akshi'),
('2018-06-04','Sahil'),
('2018-06-06','Pranjal'),
('2018-06-07','Mayank')
GO

SELECT * FROM OldCustomer
GO


--

SOLUTION – 1

--

SELECT ISNULL(LeavingDate,JoiningDate) Date
	,COUNT(DISTINCT b.CustomerName) #CustomerJoined
	,COUNT(DISTINCT a.CustomerName) #CustomerLeft  
FROM OldCustomer a FULL OUTER JOIN NewCustomer b ON leavingdate = joiningdate 
GROUP BY LeavingDate,joiningdate
ORDER BY ISNULL(LeavingDate,JoiningDate)

--

Output – 1

--

Date                    #CustomerJoined #CustomerLeft
----------------------- --------------- -------------
2018-06-04 00:00:00.000 1               2
2018-06-05 00:00:00.000 1               0
2018-06-06 00:00:00.000 0               1
2018-06-07 00:00:00.000 1               1

(4 rows affected)

--

SOLUTION – 2 | WITH 2 SELECTS

--

;WITH CTE AS
(
	SELECT LeavingDate,COUNT(*) Cnts
	FROM OldCustomer
	GROUP BY LeavingDate
)
SELECT ISNULL(c.LeavingDate,x.JoiningDate) Date , ISNULL(x.Cnts,0) #CustomerJoined
, ISNULL(c.Cnts,0) #CustomerLeft
FROM CTE c
FULL JOIN
(
	SELECT JoiningDate,COUNT(*) Cnts
	FROM NewCustomer
	GROUP BY JoiningDate
)x ON x.JoiningDate = c.LeavingDate

--

Output – 2

--

Date                    #CustomerJoined #CustomerLeft
----------------------- --------------- -------------
2018-06-04 00:00:00.000 1               2
2018-06-05 00:00:00.000 1               0
2018-06-06 00:00:00.000 0               1
2018-06-07 00:00:00.000 1               1

(4 rows affected)


--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail	URL
☛ SQL Advance Puzzles	https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries	https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles	https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles	https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers	https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

3 thoughts on “SQL Puzzle | The COUNT Puzzle – A SINGLE SELECT ? | SQL Interview Question”

Sappho03 (@sappho0101) said:

July 11, 2018 at 1:10 PM

What do u think about below solution?

;with cte as (
select joiningdate dt from NewCustomer
UNION
select LeavingDate from OldCustomer
)

,cte2 as (
select dt as Date, count(t2.JoiningDate) as #CustomerJoined from cte t1
left join NewCustomer t2 on t1.dt=t2.JoiningDate
group by dt
)

,cte3 as (
select dt as Date, count(t2.LeavingDate) as #CustomerLeft from cte t1
left join OldCustomer t2 on t1.dt=t2.LeavingDate
group by dt
)

select cte2.date as Date, #CustomerJoined, #CustomerLeft
from cte2 inner join cte3
on cte2.date=cte3.date

LikeLiked by 1 person

- Pawan Kumar Khowal said:
  
  July 11, 2018 at 1:13 PM
  
  Excellent..
  
  LikeLike
  
Gregor Kralj said:

May 24, 2019 at 11:48 AM

with cte_date_log(DateLog, CustomersLeft, CustomersJoined) as
(
select LeavingDate, COUNT(*) as CustomersLeft, 0
from OldCustomer
group by LeavingDate
union all
select JoiningDate, 0, COUNT(*) as CustomersJoined
from NewCustomer
group by JoiningDate
)
select
DateLog as Date, SUM(CustomersJoined) as #CustomerJoined, SUM(CustomersLeft) as #CustomerLeft
from cte_date_log
group by DateLog
order by DateLog

LikeLike

Improving my SQL BI Skills

SQL Puzzle | The COUNT Puzzle – A SINGLE SELECT ? | SQL Interview Question

3 thoughts on “SQL Puzzle | The COUNT Puzzle – A SINGLE SELECT ? | SQL Interview Question”

Leave a comment Cancel reply

Share this

Related

3 thoughts on “SQL Puzzle | The COUNT Puzzle – A SINGLE SELECT ? | SQL Interview Question”

Leave a comment Cancel reply