Tags
Complex SQL Challenges, complex sql statement(puzzle), 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, Khowal, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, 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, T-SQL Query | [ The Complex Week Puzzle ], The Biggest Gap Puzzle, The Gap Puzzle, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries, Week puzzle
T-SQL Query | [ The Passengers # at every bus stop Puzzle ]
Puzzle Statement
- The Puzzle is simple.
- You have given a table where you have got information about different stops of a Bus and the passengers board the bus and passgengers alight from the bus.
- Please check out the sample input and expected output for details.
Sample Input
| ROUTE | StopNumber | ONS | OFFS | SPOT_CHECK |
| AAAA | 1 | 5 | 0 | NULL |
| AAAA | 2 | 0 | 0 | NULL |
| AAAA | 3 | 2 | 1 | NULL |
| AAAA | 4 | 6 | 3 | 8 |
| AAAA | 5 | 1 | 0 | NULL |
| AAAA | 6 | 0 | 1 | 7 |
| AAAA | 7 | 0 | 3 | NULL |
Expected Output
| ROUTE | StopNumber | ONS | OFFS | SPOT_CHECK | nxt | PassengerAtEveryStop |
| AAAA | 1 | 5 | 0 | NULL | 5 | 5 |
| AAAA | 2 | 0 | 0 | NULL | 0 | 5 |
| AAAA | 3 | 2 | 1 | NULL | 1 | 6 |
| AAAA | 4 | 6 | 3 | 8 | 2 | 8 |
| AAAA | 5 | 1 | 0 | NULL | 1 | 9 |
| AAAA | 6 | 0 | 1 | 7 | -2 | 7 |
| AAAA | 7 | 0 | 3 | NULL | -3 | 4 |
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
Script
Use the below script to generate the source table and fill them up with the sample data.
—
CREATE TABLE BusLoad
(
ROUTE CHAR(4) NOT NULL,
StopNumber INT NOT NULL,
ONS INT,
OFFS INT,
SPOT_CHECK INT
)
go
INSERT BusLoad VALUES('AAAA', 1, 5, 0, null)
INSERT BusLoad VALUES('AAAA', 2, 0, 0, null)
INSERT BusLoad VALUES('AAAA', 3, 2, 1, null)
INSERT BusLoad VALUES('AAAA', 4, 6, 3, 8)
INSERT BusLoad VALUES('AAAA', 5, 1, 0, null)
INSERT BusLoad VALUES('AAAA', 6, 0, 1, 7)
INSERT BusLoad VALUES('AAAA', 7, 0, 3, null)
go
— |
Update May 9 | Solutions — Pawan Kumar Khowal
--
/************ SOLUTION 1 ****************/
;WITH CTE AS
(
select *, nxt = ONS - offs - CASE WHEN Spot_Check IS NULL THEN 0 ELSE 1 END
from Busload
)
SELECT *, (Select sum(nxt) FROM CTE A WHERE A.StopNumber <= t.StopNumber ) PassengerAtEveryStop
FROM CTE t
/************ SOLUTION 2 ****************/
;WITH CTE AS
(
select *, nxt = ONS - offs - CASE WHEN Spot_Check IS NULL THEN 0 ELSE 1 END
from Busload
)
SELECT * , SUM(nxt) OVER (ORDER BY stopNUmber) PassengerAtEveryStop
FROM CTE t
--
|
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Http://MSBISkills.com
Improving my SQL BI Skills 
Good puzzle. However I struggled with the significance of SPOT_CHECK Column. I wish it is explained in the description.
Anyways… Here is my solution…
;With T AS
(
Select * , Ons-ISNull(Offs,0)-IIF(ISNULL(SPOT_CHECK,0)=0,0,1) nxt From BusLoad a
)Select *, (select Sum(nxt) From T b Where b.StopNumber
LikeLiked by 1 person