Many of you may know that I am an avid walker. I enjoy walking and hiking for the health and mental benefits. I’m also finicky about the footwear I wear on my walks. I recently used the LEAD() Window function on some of the walking data I track and discovered gaps in the ranges of dates that I wore a particular brand of shoe. Continue reading and see the example queries I came up with to solidify learning concepts about LEAD()…
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
I am using the Oracle database for the queries in this post. To my knowledge, most all SQL implementations support the LEAD() Window function. However, be sure and check the applicable documentation for your version.
There are 2 tables involved in the example queries. A STG_WALKING_STATS and SHOES_WORN table. The SHOE_ID column in table STG_WALKING_STATS is a FOREIGN KEY to the SHOE_ID column in table SHOES_WORN.
A simple INNER JOIN returns the overall total count of rows in which I wore a favorite pair of shoes:
FROM stg_walking_stats ws
INNER JOIN shoes_worn sw
ON sw.shoe_id = ws.shoe_id
WHERE sw.brand_name = 'Merrel MOAB Edge 2';
The problem is, I know first-hand that these are not consecutive days and are in fact, separated by 2 different pairs of the same brand of shoe. I took a break from wearing the 1st pair to try different brands of shoes. However, I would eventually return to the brand of shoes that work best for me at this point.
With that filler out of the way, on to the main objective which is to find out how many days elapsed between when I last wore the first pair of shoes before the break (started trying other brands) to when I started wearing this specific brand again.
I discovered that the LEAD() Window Function works well for this particular query.
LEAD() Window Function: Use and Syntax
The LEAD() Window function accesses following rows from the current row it is operating on. LEAD() accepts up to 3 parameters with 2 of those being optional.
(Note: The offset and default parameters are optional.)
Code, content, and community for developers.
The LAMP stack and the PHP technologies and frameworks it runs.
LEAD() Window Function: Finding gaps in DATE value ranges
By specifying the DAY_WALKED column in the call to LEAD(), and the ORDER BY clause of the OVER() clause, I can access the next row for each current row.
In Oracle SQL, you can subtract 2 DATE values and calculate the number of days in between the two. As shown in this query, I subtract the current rows’ DAY_WALKED value from the next rows’ DAY_WALKED value (returned by LEAD()) and determine the number of days between them.
LEAD(ws.day_walked) OVER(ORDER BY ws.day_walked ASC) - ws.day_walked days_diff
FROM stg_walking_stats ws
INNER JOIN shoes_worn sw
ON sw.shoe_id = ws.shoe_id
WHERE sw.brand_name = 'Merrel MOAB Edge 2';
(Note: More rows returned in the above query results than shown due to constraints of the screenshot image.)
Wrapping the query in an in-line VIEW, I then ORDER BY the DAYS_DIFF expression in descending order, returning those rows with the greatest value first in the result set:
SELECT ws.day_walked, LEAD(ws.day_walked) OVER(ORDER BY ws.day_walked ASC) next_walk,
LEAD(ws.day_walked) OVER(ORDER BY ws.day_walked ASC) - ws.day_walked days_diff
FROM stg_walking_stats ws
INNER JOIN shoes_worn sw
ON sw.shoe_id = ws.shoe_id
WHERE sw.brand_name = 'Merrel MOAB Edge 2')
ORDER BY days_diff DESC NULLS LAST;
Based on the query results, there are 314 days between 13-MAY-20, when I last wore the 1st pair of shoes, and 23-MAR-21, when I began wearing this brand type again.
Consider making a donation as I continue to provide valuable content here on my blog. Thank you so much!!! Every bit is much appreciated and helps tremendously!
LEAD() Window Function: Offset and Default optional parameters
You may have noticed the NULLS LAST keywords in the ORDER BY clause for the previous query. Omitting NULLS LAST, we are returned the below query results:
SELECT ws.day_walked, LEAD(ws.day_walked) OVER(ORDER BY ws.day_walked ASC) next_walk,
LEAD(ws.day_walked) OVER(ORDER BY ws.day_walked ASC) - ws.day_walked days_diff
FROM stg_walking_stats ws
INNER JOIN shoes_worn sw
ON sw.shoe_id = ws.shoe_id
WHERE sw.brand_name = 'Merrel MOAB Edge 2')
ORDER BY days_diff DESC;
For example, take the row with the 29-JUN-21 DATE value. NULL is returned for the NEXT_WALK column expression. Meaning, there is not a later row for that date as it is the last in the result set. In this case, LEAD() returns NULL and the DAYS_DIFF expression evaluates to NULL as well.
If no offset or default parameters – for those instances in which the offset extends beyond the table rows – are specified in the call to LEAD(), then 1 is used as the offset and NULL is the default.
If you see any mistakes or have questions about the code, please share freely in the comments. Constructive feedback helps me provide accurate articles and is much appreciated!
I really enjoy learning more about SQL Window Functions. In most of the projects I work on (Back-end LAMP stack web development), I have not encountered very many use cases for any of them. But, self-directed learning through personal projects – such as this post – will better prepare me for when I do need to use them.
Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients.
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.