SQL Puzzle | The Count Puzzle

The challenge here to write the smallest query. We have to summarized data based on the building Id. Please check the sample input and expected output.

Sample Input

tbl_RealEstate

tbl_login

tbl_history

Expected Output

Script

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

--

CREATE TABLE tbl_login
(
	ID int,
	BldID int,
	Name varchar(30)
)
GO

insert into tbl_login
values
(1, 1, 'Name1'),
(2, 1, 'Name2'),
(3, 2, 'Name3'),
(4, 3, 'Name4'),
(5, 2, 'Name5'),
(6, 4, 'Name6'),
(7, 5, 'Name7'),
(8, 6, 'Name8'),
(9, 1, 'Name9'),
(10, 1, 'Name10'),
(11, 2, 'Name11'),
(12, 3, 'Name12');
GO

CREATE TABLE tbl_history
(
	ID int,
	BldID int,
	Username varchar(30)
)
GO


insert into tbl_history
values
(1, 1, 'Name1'),
(2, 1, 'Name1'),
(3, 1, 'Name9'),
(4, 2, 'Name3'),
(5, 3, 'Name4'),
(6, 4, 'Name6'),
(7, 4, 'Name6'),
(8, 4, 'Name6'),
(9, 5, 'Name7'),
(10, 6, 'Name8'),
(11, 8, 'Name9'),
(12, 8, 'Name10'),
(13, 9, 'Name11'),
(14, 9, 'Name12');
GO

CREATE TABLE tbl_RealEstate
(
	 BldID int,
	RealEstateName varchar(30)
)
GO

insert into tbl_RealEstate values
(1, 'Building 1'),
(2, 'Building 2'),
(3, 'Building 3'),
(4, 'Building 4'),
(5, 'Building 5'),
(6, 'Building 6'),
(7, 'Building 7'),
(8, 'Building 8'),
(9, 'Building 9');



--

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

--

SELECT * FROM tbl_RealEstate as r 
OUTER APPLY
(
	SELECT COUNT(*) as LoginCount FROM tbl_login as l 
	WHERE BldID = r.BldID
)t1
OUTER APPLY
(
	SELECT COUNT(*) as HistoryCount FROM tbl_history as h
	WHERE BldID = r.BldID
)t2

--

Output

--

/*------------------------
SELECT * FROM tbl_RealEstate as r 
OUTER APPLY
(
	SELECT COUNT(*) as LoginCount FROM tbl_login as l 
	WHERE BldID = r.BldID
)t1
OUTER APPLY
(
	SELECT COUNT(*) as HistoryCount FROM tbl_history as h
	WHERE BldID = r.BldID
)t2
------------------------*/
BldID       RealEstateName                 LoginCount  HistoryCount
----------- ------------------------------ ----------- ------------
1           Building 1                     4           3
2           Building 2                     3           1
3           Building 3                     2           1
4           Building 4                     1           3
5           Building 5                     1           1
6           Building 6                     1           1
7           Building 7                     0           0
8           Building 8                     0           2
9           Building 9                     0           2

(9 row(s) affected)

--

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