EXPLAIN EXTENDED

From Stack Overflow:

I have a table with data such as below:

Group	Start Date	End Date
A	2001.01.01	2001.01.03
A	2001.01.01	2001.01.02
A	2001.01.03	2001.01.03
B	2001.01.01	2001.01.01

I am looking to produce a view that gives a count for each day

We have a list of ranges here, and for each date we should count the number of ranges that contain this date.

To make this query, we will employ one simple fact: the number of ranges containing a given date is the number of ranges started on or before this date minus the number of ranges that ended before this date.

This can easily be calculated using window functions.

Let's create a sample table:

Read the rest of this entry »