T-SQL Tuesday #168 – Example Using LEAD

This month’s T-SQL Tuesday invite comes from Steve Jones, who asks us to write about how windows functions have made our lives easier. I thought about this for a bit and had one example stick out in my mind. I’m going to try to rework it to fit into the context of the StackOverflow2013 database.

For the full invitation post, click the T-SQL Tuesday logo to the right.

Starting in the LEAD

Imagine we want to find gaps in post history for users. If a user is posting every day, we’re happy and can assume they’re happy. If they’re only posting once every few weeks, we want to investigate why that is.

Let’s start building our query to find those users who are only posting every few weeks. We’ll start by getting the OwnerUserId, the post CreationDate, and the LEAD CreationDate for the OwnerUserId. I want a smaller set of data for this example, so we’ll only care about records older than 2009, and the oldest post is from 2008-07-31.

SELECT OwnerUserId, CreationDate, 
	LEAD(CreationDate) OVER (PARTITION BY OwnerUserId ORDER BY CreationDate) AS 'NextCreationdate'
FROM Posts
WHERE CreationDate < '2009-01-01'
ORDER BY OwnerUserId;
GO

OwnerUserId 0 is posting at least once a day in early August, so we don’t see any concerns there. Those types of users we’re happy with. We want to narrow that list down to occasions where a user isn’t posting as consistently. Let’s say we want to see where the gaps in posts are greater than 14 days. We’ll edit our query to only show where the gap is greater than 14 days and return the gap in days as CreationDateDiff.

We could do this in a couple of different ways. One way would be by using a CTE:

WITH cte
AS (
	SELECT OwnerUserId, CreationDate, 
		LEAD(CreationDate) OVER (PARTITION BY OwnerUserId ORDER BY CreationDate) AS 'NextCreationdate'
	FROM Posts
	WHERE CreationDate < '2009-01-01'
	)
SELECT OwnerUserId, CreationDate, NextCreationdate,
	DATEDIFF(DAY, CreationDate, NextCreationdate) AS 'CreationDateDiff'
FROM cte
WHERE DATEDIFF(DAY, CreationDate, NextCreationdate) > 14
ORDER BY OwnerUserId;
GO

Another way would be to use a subquery:

SELECT OwnerUserId, CreationDate, NextCreationdate, DATEDIFF(DAY, CreationDate, NextCreationdate) AS 'CreationDateDiff'
FROM (
	SELECT OwnerUserId, CreationDate, 
		LEAD(CreationDate) OVER (PARTITION BY OwnerUserId ORDER BY CreationDate) AS 'NextCreationdate'
	FROM Posts
	WHERE CreationDate < '2009-01-01'
	) w
WHERE DATEDIFF(DAY, CreationDate, NextCreationdate) > 14
ORDER BY OwnerUserId;
GO

Either way, we’ll get this type of result:

Now we have a more concise list, only showing the OwnerUserId’s and the timeframes where they were less active.

Test an OwnerUserId

Let’s pick out an OwnerUserId and confirm our dates line up. We’ll change our subquery to only show OwnerUserId 444 regardless of the CreationDateDiff:

SELECT OwnerUserId, CreationDate, NextCreationdate, DATEDIFF(DAY, CreationDate, NextCreationdate) AS 'CreationDateDiff'
FROM (
	SELECT OwnerUserId, CreationDate, 
		LEAD(CreationDate) OVER (PARTITION BY OwnerUserId ORDER BY CreationDate) AS 'NextCreationdate'
	FROM Posts
	WHERE CreationDate < '2009-01-01'
	) w
WHERE OwnerUserId = 444
ORDER BY OwnerUserId;
GO

Based on those results, since we’re looking for gaps greater than 14, we should only see one record returned (for the CreationDateDiff of 51) if we add our DATEDIFF back to the query:

SELECT OwnerUserId, CreationDate, NextCreationdate, DATEDIFF(DAY, CreationDate, NextCreationdate) AS 'CreationDateDiff'
FROM (
	SELECT OwnerUserId, CreationDate, 
		LEAD(CreationDate) OVER (PARTITION BY OwnerUserId ORDER BY CreationDate) AS 'NextCreationdate'
	FROM Posts
	WHERE CreationDate < '2009-01-01'
	) w
WHERE DATEDIFF(DAY, CreationDate, NextCreationdate) > 14
AND OwnerUserId = 444
ORDER BY OwnerUserId;
GO

Bonus Points

The last bullet point on the invite asks, “Draw a picture of a window with a spatial function.” I had a tab open for DALL·E 3 and couldn’t help but take that bullet point and plug that in as a prompt to “draw a picture.” 3 of the 4 results weren’t much, but I thought the one to the left looked artistic enough to include in this post.

Thanks for reading!