StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL issue - calculate max days sequence
text
Body
copied!<p>There is a table with visits data:</p> <pre><code>uid (INT) | created_at (DATETIME) </code></pre> <p>I want to find how many days in a row a user has visited our app. So for instance: </p> <pre><code>SELECT DISTINCT DATE(created_at) AS d FROM visits WHERE uid = 123 </code></pre> <p>will return:</p> <pre><code> d ------------ 2012-04-28 2012-04-29 2012-04-30 2012-05-03 2012-05-04 </code></pre> <p>There are 5 records and two intervals - 3 days (28 - 30 Apr) and 2 days (3 - 4 May).</p> <p>My question is how to find the maximum number of days that a user has visited the app in a row (3 days in the example). Tried to find a suitable function in the SQL docs, but with no success. Am I missing something?</p> <hr> <p><strong>UPD:</strong> Thank you guys for your answers! Actually, I'm working with vertica analytics database (http://vertica.com/), however this is a very rare solution and only a few people have experience with it. Although it supports SQL-99 standard.</p> <p>Well, most of solutions work with slight modifications. Finally I created my own version of query:</p> <pre><code>-- returns starts of the vitit series SELECT t1.d as s FROM testing t1 LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', -1, t1.d)) WHERE t2.d is null GROUP BY t1.d s --------------------- 2012-04-28 01:00:00 2012-05-03 01:00:00 -- returns end of the vitit series SELECT t1.d as f FROM testing t1 LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', 1, t1.d)) WHERE t2.d is null GROUP BY t1.d f --------------------- 2012-04-30 01:00:00 2012-05-04 01:00:00 </code></pre> <p>So now only what we need to do is to join them somehow, for instance by row index.</p> <pre><code>SELECT s, f, DATEDIFF(day, s, f) + 1 as seq FROM ( SELECT t1.d as s, ROW_NUMBER() OVER () as o1 FROM testing t1 LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', -1, t1.d)) WHERE t2.d is null GROUP BY t1.d ) tbl1 LEFT JOIN ( SELECT t1.d as f, ROW_NUMBER() OVER () as o2 FROM testing t1 LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', 1, t1.d)) WHERE t2.d is null GROUP BY t1.d ) tbl2 ON o1 = o2 </code></pre> <p>Sample output:</p> <pre><code> s | f | seq ---------------------+---------------------+----- 2012-04-28 01:00:00 | 2012-04-30 01:00:00 | 3 2012-05-03 01:00:00 | 2012-05-04 01:00:00 | 2 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload