If you are a web developer, probably you know jsFiddle. If you don't know you can always start play with it today. jsFiddle it's an online playground tool where you can test your javascript/css/html problems and share it with others. Many StackOverflow answers, the answers site for developers, include a link to jsFiddle to demonstrate a problem and/or a solution. jsFiddle became very popular among web developers. There are other online playground tools, but they are tipically focused in just one technology, HTML or CSS or Javascript. jsFiddle combines the three.
Until recently, I didn't know any tool similar with jsFiddle, but focused on database problems. But there is! It's the SQLFiddle. And if you use jsFiddle you start to recognize the similarities, and in seconds you start to use SQLFiddle without problems.
The other day I was talking with a guy, who started testing my SQL knowledge and asked me to solve a problem.
Here is the problem:
If you have a table with a date column and an int column, write a query which gives me the interval start date and end date, and the sum of the values for the interval. In other words, if we have these records in table
Dt1, 5
Dt2, 6
Dt3, 2
Dt4, 8
I want the result
Dt1, Dt2, 11 (5+6)
Dt2, Dt3, 8 (6 + 2)
Dt3, Dt4 10 (2 + 8)
The problem was not new for me, since I already had to face it in real life. In this new version of Sql Server 2012 we have to functions Lead and Lag that we can use to solve the problem without having to think too much. So my first answer was "Can I assume that I am using a Sql Server 2012?" The guy is a MySql guy, so he answered me "No. Write a query that we can port to another database without too much effort". So, I wrote the query on paper, which consists of a self join combined with the use of row_number() function.
The guy looked to the query and didn't recognize the row_number() function. Then he show me a query using his approach with MySql.
None of us had our laptop, prepared with the tools to show the solution to each other using a real database instance. We could use SQLFiddle to show the solution to each other.
BTW, here is the solution without using Lead and Lag