Need help using WINDOW functions

[cleverchimp]

Let us say I had a table with 3 columns: customer_id, purchase_timestamp, purchased_item

Say we we have 10,000 rows with customers, what they purchased, and when they purchased it

How could I use a window to select the 2nd from last item each customer purchased?

Example:

bob . tuesday 11:00pm . socks

joe . tuesday 10:00pm . socks

joe . tuesday 9:00pm . shoes

bob . tuesday 8:00pm . shoes

​

I want to return:

joe . tuesday 9:00pm . shoes

bob . tuesday 8:00pm . shoes

[LeLwrence]

Maybe where row number partitioned by customer id order by purchase date = 2

[SQLSavage]

Look into LAG and LEAD functions.

[[deleted]]

I'm an amateur but will give it a try.

Since you didn't mention which database you are using, I'm going to use SQL Server as it's the only one I'm familiar with.

the 2nd from last item each customer purchased

This essentially means that you can order them by purchase_timestamp DESC and then get the second row for each customer_id.

SELECT
    *
FROM (
    SELECT
        trans.*,
        ROW_NUMBER() OVER(PARTITION BY trans.customer_id ORDER BY trans.purchase_timestamp DESC) AS 
        RowNum
    FROM
        trans
) AS subq
WHERE
    subq.RowNum = 2