SQL LEAD Window Function
We will see how we can use the LEAD function for creating a condition to get the desired results.
Let’s understand the data and the problem.
- Data is shown as below ,with a table showing transactions by the users for products bought on a date.
2. Now we want to see the number of users who have bought products after the first purchase and in future too but not on the same day.
3. So if we look at the data we see that like user_id purchased products in different years/days so this will be part of our count .
Get Manpreetsingh’s stories in your inbox
Join Medium for free to get updates from this writer.
4. We can easily achieve by using the lead function to see the next purchase date of each user and always getting those users who have met this condition. Let’s write the lead function on create date.
5. Lead function can take three arguments LEAD(return_value ,offset [,default]).In this case we just want to get the next purchase date irrespective of the when the purchase was made. So I will use only LEAD(created_at) over (Partition by user_id order by created_at ). Here I am using the default order by which is ascending as we want to see the first purchase date and then the very next .
6. So now just to show the sample output of lead function we get below.
So now we don’t need users who didn’t purchase anything next time and the ones who purchased on the same date like user_id 21.
7. So final query will look like below and then we will put this query into a CTE and then get distinct count of user_id since we only need distinct users not the number of times by user , you can add that also using the same CTE.
with cte1 as (select *,
lead(created_at) over (partition by user_id order by created_at ) as nextpurchase
from transactions)
select count(distinct user_id) as num_users, count(user_id) as total_orders from cte1
where nextpurchase > created_at








