SQL Puzzle | The Distinct Sequential Unique Records with Count | Advanced SQL
In this puzzle you have to find out the sequential unique records with their count. For more details please see the sample input and expected output.
Sample Input
| Id | Vals |
|---|---|
| 1 | p |
| 2 | q |
| 2 | q |
| 2 | q |
| 1 | p |
| 1 | p |
| 2 | q |
Expected Output
| Id | Vals | Cnts |
|---|---|---|
| 1 | p | 1 |
| 2 | q | 3 |
| 1 | p | 2 |
| 2 | q | 1 |
Script – DDL and INSERT Sample Data
-- CREATE TABLE SequentialUniqueNums ( Id INT ,Vals VARCHAR(10) ) GO INSERT INTO SequentialUniqueNums VALUES (1,'p') ,(2,'q') ,(2,'q') ,(2,'q') ,(1,'p') ,(1,'p') ,(2,'q') GO SELECT * FROM SequentialUniqueNums GO -- |
SOLUTION 1
-- SELECT Id,Vals,COUNT(b) Cnts FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Vals ORDER BY rnk) - rnk b FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) rnk FROM SequentialUniqueNums )k )z GROUP BY Id,Vals,b ORDER BY MIN(rnk) -- |
OUTPUT – 1
-- Id Vals Cnts ----------- ---------- ----------- 1 p 1 2 q 3 1 p 2 2 q 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
Improving my SQL BI Skills 
with cte as (
SELECT
Vals,
Id,
ROW_NUMBER() OVER (order by (select null)) as rowid
FROM SequentialUniqueNums
),
cte_summer as (
select
*,
IIF(Vals = LAG(Vals) over (order by rowid), 0, 1) as summer
from cte
),
cte_grouper as (
select
*,
SUM(summer) over (order by rowid) as row_grouper
from cte_summer
)
select
Min(Id) as Id, Min(Vals) as Vals, COUNT(1) as Cnts
from cte_grouper
group by row_grouper
LikeLiked by 1 person
with cte as (
— add rownumber to each row
SELECT *, row_number() over(ORDER BY (select null)) RN FROM SequentialUniqueNums
), tmp AS (
–identify the last row for each group before a change
SELECT c1.*
,row_number() over(order by (select null)) NEW_RN
FROM cte c1
left JOIN cte c2
ON c1.RN+1 = c2.RN AND c1.Id =c2.Id
WHERE c2.rn IS null)
–subtract the last row number of the prior sequence from each row that is the last of a sequence
SELECT t1.Id, t1.Vals,t1.RN -coalesce(t2.RN,0) FROM tmp t1
LEFT JOIN tmp t2 ON t1.NEW_RN-1 = t2.NEW_RN
LikeLike