TSQL Challenges

TSQL Challenges - All In One Overview

TSQL Challenges are growing every day and we have now a lot of valuable information produced by our challengers. It is not always easy to have a global idea of what we done in the past and that's the target of this overview page.

It is also the opportunity to see the problems we deal with in the past and find new solutions. Even if the contests for previous challenges and winners set are closed, we are still open for new interesting solutions to publish.

Until we have a new optimized site for the TSQL Challenges, we will keep this page up to date with the new challenges and solutions in descending order. You should bookmark it.

TSQL Challenge 14

This challenge about identifying the longest sequence of characters in a string (Guid strings are taken in exemple here)

• The Challenge
• Winners
• Solution 1
• Solution 2
• Solution 3

TAGS: Recursive, String

TSQL Challenge 13

This challenge is about breaking a batch of invoices into multiple parts based in rules.

• The Challenge
• Winners
• Solution 1
• Solution 2
• Solution 3

TAGS: Sequential, Gaps, Data Islands

TSQL Challenge 12

This challenge is about building sequential ranges of dates with propagation to missing values

• The Challenge
• Winners
• Solution 1
• Solution 2
• Solution 3

TAGS: Sequential, Dates

TSQL Challenge 11

This challenge is about calculating the lowest price of an item by applying the best combination of discount coupons

• The Challenge
• Winners
• Solution 1
• Solution 2
• Solution 3

TAGS: Combinations, Filter

TSQL Challenge 10

This challenge is about vertical and horizontal sorting (in a single query)

• The Challenge
• Winners
• Solution 1 (Rob Farley)
• Solution 2 (Divya Agrawal)
• Solution 3 (Alejandro Mesa)

TAGS: Sorting, PIVOT, UNPIVOT

TSQL Challenge 9

This challenge is about identifying data islands through a query. All kind of solutions are acceptable, the key is performance.

• The Challenge
• Winners
• Solution 1 (Rob Farley)
  
• Solution 2 (Syed Mehroz Alam)
• Solution 3(David Barbarin)

TAGS: Data Island, Performance

TSQL Challenge 8

This challenge is about working and querying hierarchies through a single query

• The Challenge
• Winners post
• Solution 1 (Matthieu Hodin)
• Solution 2 (Syed Mehroz Alam)
• Solution 3 (Leonid Koyfman)

TAGS: CTE, Hierarchy

TSQL Challenge 7

This challenge is about writing the shortest query to get the most important tables on a server.

• The Challenge
• Winners and default solution
• Solution 1 (Barry Young)
• Solution 2 (Syed Mehroz Alam)
• Solution 3

TAGS: Management, System, Size

TSQL Challenge 6

This challenge is about making a full report with multiple aggregations levels and cumulative rows from a set of basic data.

• The Challenge
• Winners and default solution
• Solution 1 (Aurelien Verla)
• Solution 2 (Leonid Koyfman)
• Solution 3 (Cleber Augusto Martins)
• Solution 4 (Arnaud Lhopiteau)
• Aurelien and Matthieu have fun with Challenge 6

TAGS: Grouping, Aggregations, Rollup, Report

TSQL Challenge 5

This challenge is about joining tables and filter values based on comma separated values stored in columns. The main target is to be able to create an in-line comma separated split function like.

• The Challenge
• Winners and default solution
• Solution 1 (Leonid Koyfman)
• Solution 2 (Divya Agrawal)
• Solution 3 (Friedrich Paul)
• Solution 4 (Aurelien Verla)

TAGS: CTE, Strings, CSV

TSQL Challenge 4

This challenge is about filtering a table with SSN values. The target is to write the shortest code that validates and filter values regarding a specific format requirements

• The Challenge
• Solution 1 (Leonid Koyfman)
• Solution 2 (Pinal Dave)
• Solution 3 (Antoine Gémis)

TAGS: Validation, Filter, Format

TSQL Challenge 3

This challenge is about writing a string reverse like function within an in-line query.

• The Challenge
• The Solution and winners (all in one)

TAGS: Strings, Combination

TSQL Challenge 2

This challenge is about identifying dates sequences within a table of discontinued values.

• The Challenge
• The Solutions and winners (All in one)

TAGS: Dates, Ranges,Calendar, Combination, CTE

TSQL Challenge 1

This challenge is about combining data from various sources of the same format to one table with a mix of columns of the original tables. The rows of the original tables are aggregated and ordered in the resulting table.

• The Challenge
• Solution and winners (all in one)

TAGS: Aggregation, CTE, Order