Complex SQL Challenges, Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ The Join and the Aggregate Puzzle ]
The puzzle is very simple. We have 3 tables named DJ, DJ1, DJ2. We have get aggregated values of Val from table DJ1 and DJ2 . Please check out the sample input and expected output for details.
- The solution should be should use “SELECT” statement or “CTE”.
- Add your solution(s) in the comments section or send you solution(s) to firstname.lastname@example.org
Use the below script to generate the source table and fill them up with the sample data.
-- CREATE TABLE DJ ( ID INT ) GO INSERT INTO DJ(ID) Values (1),(2),(3),(4) GO CREATE TABLE DJ1 ( ID INT ,Val INT ) GO INSERT INTO DJ1(ID,Val) VALUES (1,100),(2,200),(3,500),(3,500),(2,100) GO CREATE TABLE DJ2 ( ID INT ,Val INT ) GO INSERT INTO DJ2(ID,Val) VALUES (2,10000),(3,500),(2,100),(4,100),(4,900) GO --
UPDATE – 10-Apr-2015 – Solution 1
-- Solution 1 -- SELECT d.ID , ISNULL((SELECT SUM(d1.Val) FROM DJ1 d1 WHERE d1.ID = d.ID ),0) Val1 , ISNULL((SELECT SUM(d2.Val) FROM DJ2 d2 WHERE d2.ID = d.ID ),0) Val2 FROM DJ d --
Add a comment if you have any other solution in mind. We all need to learn.
Sai Vinutna said:
with cte as
select distinct dj.id, isnull(sum(dj1.val) over (partition by dj1.id order by dj1.id),0) as val1 from dj
left join dj1
on dj.id = dj1.id
select distinct cte.id, val1, isnull(sum(dj2.val) over (partition by dj2.id order by dj2.id),0) as val2 from cte
left join dj2
on cte.id = dj2.id
select dj.*, isnull(b.c, 0) as val1, isnull(d.c, 0) as val2 from dj
left join (select distinct id, sum(val) over (partition by id order by id) as c from dj1 ) as b on dj.id=b.id
left join (select distinct id, sum(val) over (partition by id order by id) as c from dj2) as d on dj.id=d.id