Find consecutive days with gaps and islands, rank the longest streak per user, and define a current streak using the right business date.
Get the Second Row From a Table in SQL Server
To get the second row from a table, first decide what second means, because a table has no order until you give it one. This post builds a small demo and compares ROW_NUMBER, OFFSET and FETCH, and TOP inside TOP. It also covers every second row, the second row in each group, ties and the cost of each method.
How to Skip Top N Rows in SQL Server? – Interview Question of the Week #237
Question: How to skip top N Rows in SQL Server and retrieve the rest of the rows? Answer: I had asked the above question in one of the interviews last week.
Every Second Item Free: Pricing Promotions With ROW_NUMBER
Price every second item as free with ROW_NUMBER, expand quantities into units, and calculate basket discounts without double counting.
SQL SERVER – Adding Values WITH OVER and PARTITION BY
How do you sum values for each group without GROUP BY and still keep every row? Here is how SUM with OVER and PARTITION BY answers a reader’s question.
How to Get Top N Records Per Group? – Ranking Function – Interview Question of the Week #156
I gave 40 candidates this puzzle: get the top N records per group from a student marks table. Here is the simple answer using a Ranking Function.
Longest Streak of Consecutive Days in T-SQL: Gaps and Islands
Find the longest streak of consecutive days in T-SQL with gaps and islands: group dates with ROW_NUMBER, handle duplicates, and list gaps.