SQL Puzzle | Inserted and Updated Count Puzzle

In this puzzle you have to find out Names of people with how many records they have inserted and how many records they have updated. For more details please check the sample input and expected output.

Sample Input

RId InsertedBy UpdatedBy
1 Pawan Sharlee
2 Diwan Naga
3 Kishan Pawan
4 Chiku Kishan
5 Avtaar Sharlee
6 Ramesh Pawan
7 Kapil Vaibhav
8 Ishu Avtaar
9 Avtaar Sharlee

Expected Output

Nam InsertedCount UpdatedCount
Avtaar 2 1
Chiku 1 0
Diwan 1 0
Ishu 1 0
Kapil 1 0
Kishan 1 1
Naga 0 1
Pawan 1 2
Ramesh 1 0
Sharlee 0 3
Vaibhav 0 1

Script

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

 ```-- CREATE TABLE LogData ( RId INT ,InsertedBy VARCHAR(30) ,UpdatedBy VARCHAR(30) ) GO INSERT INTO LogData VALUES (1,'Pawan','Sharlee'), (2,'Diwan','Naga'), (3,'Kishan','Pawan'), (4,'Chiku','Kishan'), (5,'Avtaar','Sharlee'), (6,'Ramesh','Pawan'), (7,'Kapil','Vaibhav'), (8,'Ishu','Avtaar'), (9,'Avtaar','Sharlee') GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- SELECT Nam , InsertedCount , UpdatedCount FROM ( SELECT InsertedBy Nam FROM LogData UNION SELECT UpdatedBy FROM LogData )l OUTER APPLY ( SELECT COUNT(*) InsertedCount FROM LogData WHERE LogData.InsertedBy = l.Nam )t OUTER APPLY ( SELECT COUNT(*) UpdatedCount FROM LogData WHERE LogData.UpdatedBy = l.Nam )t1 -- ```

Output-1

 ```-- Nam InsertedCount UpdatedCount ------------------------------ ------------- ------------ Avtaar 2 1 Chiku 1 0 Diwan 1 0 Ishu 1 0 Kapil 1 0 Kishan 1 1 Naga 0 1 Pawan 1 2 Ramesh 1 0 Sharlee 0 3 Vaibhav 0 1 (11 rows affected) -- ```

Solution – 2

 ```-- Select ISNULL(InsertedBy,UpdatedBy) Nam , ISNULL(i,0) Insertedcount , ISNULL(u,0) updatedcount from (Select Insertedby ,Count(Insertedby) i FROM logdata Group by InsertedBy) a FULL JOIN (Select Updatedby ,Count(updatedby) u FROM logdata Group by updatedby)b on a.InsertedBy = b.UpdatedBy -- ```

Output-2

 ```-- Nam Insertedcount updatedcount ------------------------------ ------------- ------------ Avtaar 2 1 Chiku 1 0 Diwan 1 0 Ishu 1 0 Kapil 1 0 Kishan 1 1 Naga 0 1 Pawan 1 2 Ramesh 1 0 Sharlee 0 3 Vaibhav 0 1 (11 rows 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