Return two most recent costs for all products from a table of product costs

Question

I am trying to return the last two costs for every product from two tables for a given time period using Snowflake's SQL.

Here are the sample tables

**VendorItemCosts**
| ID | VENDORITEMID | COSTCENTS | UPDATEDAT                     |
|----|--------------|-----------|-------------------------------|
| 1  | 442720       | 1209      | 2022-03-11 20:54:16.666 +0000 |
| 2  | 442720       | 1399      | 2022-03-12 20:23:15.342 +0000 |
| 3  | 2878218      | 3432      | 2022-03-12 20:54:32.642 +0000 |
| 4  | 2878218      | 3567      | 2022-03-13 20:55:11.123 +0000 |

**VendorItems**
| ID | VENDORITEM | SKU  | NAME    |
|----|------------|------|---------|
| 1  | 442720     | 1234 | T-Shirt |
| 2  | 2878218    | 5678 | Shorts  |

Here is my attempt that returns it for a specific VENDORITEM but I need it to return ALL of them at once.

    select VI1.ID as "VendorItem ID",
       VI1.Name as "VendorItem Name",
       LATEST.ID as "Latest ID",
       LATEST.COSTCENTS as "Latest CostCents",
       LATEST.UPDATEDAT as "Latest UpdatedAt",
       LATEST2.ID as "Latest2 ID",
       LATEST2.COSTCENTS as "Latest2 CostCents", 
       LATEST2.UPDATEDAT  as "Latest2 UpdatedAt"
    from VENDORITEMS as VI1
    join (select top 1 * from VENDORITEMCOSTS as VIC1 where VIC1.VENDORITEMID = 442720 
          order by VIC1.ID desc) as LATEST on VI1.ID = LATEST.VENDORITEMID  --Returns most recent VendorItemCosts record
    join (select top 1 * from VENDORITEMCOSTS as VIC1 where VIC1.ID < (     --Returns 2nd most recent VendorItemCosts record
        select MAX(VIC2.ID) from VENDORITEMCOSTS as VIC2 where VIC2.VENDORITEMID = 442720)
        and VIC1.VENDORITEMID = 442720 order by VIC1.ID desc) as LATEST2 on VI1.ID = LATEST2.VENDORITEMID
    where VI1.ID = 442720 and
    LATEST.UPDATEDAT > '2022-03-01' and
    LATEST.UPDATEDAT < '2022-03-25'

Here is the result of the above query

| VendorItem ID | VendorItem Name | Latest ID | Latest CostCents | Latest UpdatedAt              | Latest2 ID | Latest2 CostCents | Latest2 UpdatedAt             |
|---------------|-----------------|-----------|------------------|-------------------------------|------------|-------------------|-------------------------------|
| 442720        | T-Shirt         | 2         | 1399             | 2022-03-12 20:23:15.342 +0000 | 1          | 1209              | 2022-03-11 20:54:16.666 +0000 |

This is the result I am looking for

| VendorItem ID | VendorItem Name | Latest ID | Latest CostCents | Latest UpdatedAt              | Latest2 ID | Latest2 CostCents | Latest2 UpdatedAt             |
|---------------|-----------------|-----------|------------------|-------------------------------|------------|-------------------|-------------------------------|
| 442720        | T-Shirt         | 2         | 1399             | 2022-03-12 20:23:15.342 +0000 | 1          | 1209              | 2022-03-11 20:54:16.666 +0000 |
| 2878218       | Shorts          | 4         | 3567             | 2022-03-13 20:55:11.123 +0000 | 3          | 3432              | 2022-03-12 20:54:32.642 +0000 |

Answer 1

Step - 1:

Given date range = From '2022-03-01' to '2022-03-25'.

First we need to find latest items in this date range.

Latest Items means the item for which the ID in VendorItemCosts table is MAX.

[Above is based on query, given in question which is doing MAX(VIC2.ID) OR order by VIC1.ID desc]

with vendor_cte(ID,VENDORITEMID,COSTCENTS,UPDATEDAT) as
(select * from values
(1,442720,1273,'2021-03-10 20:51:16.666'::timestamp),
(2,442720,1209,'2021-03-11 20:54:16.666'::timestamp),
(3,442720,1399,'2022-03-18 20:28:15.342'::timestamp),
(4,442720,1476,'2022-03-27 20:52:16.666'::timestamp),
(5,2878218,1256,'2022-03-12 20:54:16.666'::timestamp),
(6,2878218,3432,'2021-03-18 20:55:32.642'::timestamp),
(7,2878218,3667,'2022-03-16 20:53:11.123'::timestamp),
(8,2878218,3597,'2022-03-28 20:51:11.123'::timestamp),
(9,3878218,3597,'2021-03-28 20:51:11.123'::timestamp),
(10,3878218,3597,'2022-03-18 20:51:11.123'::timestamp),
(11,3878218,3597,'2022-03-28 20:51:11.123'::timestamp)
), item_cte (id, vendoritem, sku, name) as
(
select * from values
(1,442720,1234,'T-Shirt'),
(2,2878218,4678,'Jeans'),
(3,3878218,5078,'Jackets')
), latest_cte (latest_id, VENDORITEMID,latest_costcents,latest_updatedat, name) as
(
select
vc.id latest_id,
vc.VENDORITEMID,
vc.COSTCENTS latest_costcents,
vc.UPDATEDAT latest_updatedat,
ic.name
from vendor_cte vc, item_cte ic
where vc.VENDORITEMID = ic.vendoritem
and vc.UPDATEDAT between
'2022-03-01'::timestamp and '2022-03-25'::timestamp -- Filter Date range first
qualify row_number()
over (partition by vc.VENDORITEMID order by vc.id desc)<2 -- Then, select the latest item
)
select * from latest_cte;

LATEST_ID	VENDORITEMID	LATEST_COSTCENTS	LATEST_UPDATEDAT	NAME
3	442720	1399	2022-03-18 20:28:15.342	T-Shirt
7	2878218	3667	2022-03-16 20:53:11.123	Jeans
10	3878218	3597	2022-03-18 20:51:11.123	Jackets

Step - 2: Main step

Next what is needed is to get the previous item price. That is the item, previous to the one selected in step - 1.

Using the data-set built so far from the step - 1, putting that in a CTE -

with vendor_cte(ID,VENDORITEMID,COSTCENTS,UPDATEDAT) as
(select * from values
(1,442720,1273,'2021-03-10 20:51:16.666'::timestamp),
(2,442720,1209,'2021-03-11 20:54:16.666'::timestamp),
(3,442720,1399,'2022-03-18 20:28:15.342'::timestamp),
(4,442720,1476,'2022-03-27 20:52:16.666'::timestamp),
(5,2878218,1256,'2022-03-12 20:54:16.666'::timestamp),
(6,2878218,3432,'2021-03-18 20:55:32.642'::timestamp),
(7,2878218,3667,'2022-03-16 20:53:11.123'::timestamp),
(8,2878218,3597,'2022-03-28 20:51:11.123'::timestamp),
(9,3878218,3597,'2021-03-28 20:51:11.123'::timestamp),
(10,3878218,3597,'2022-03-18 20:51:11.123'::timestamp),
(11,3878218,3597,'2022-03-28 20:51:11.123'::timestamp)
), item_cte (id, vendoritem, sku, name) as
(
select * from values
(1,442720,1234,'T-Shirt'),
(2,2878218,4678,'Jeans'),
(3,3878218,5078,'Jackets')
), latest_cte (latest_id, VENDORITEMID,latest_costcents,latest_updatedat, name) as
(
select
vc.id latest_id,
vc.VENDORITEMID,
vc.COSTCENTS latest_costcents,
vc.UPDATEDAT latest_updatedat,
ic.name
from vendor_cte vc, item_cte ic
where vc.VENDORITEMID = ic.vendoritem
and vc.UPDATEDAT between
'2022-03-01'::timestamp and '2022-03-25'::timestamp -- Filter Date range first
qualify row_number()
over (partition by vc.VENDORITEMID order by vc.id desc)<2 -- Then, select the latest item
)
select lt.*,
vc.id prev_ID,
vc.costcents prev_costcents,
vc.UPDATEDAT prev_UPDATEDAT
from vendor_cte vc right join latest_cte lt
on lt.VENDORITEMID = vc.VENDORITEMID
and lt.latest_id > vc.id
qualify row_number() -- This qualify will select the latest previous item
over (partition by latest_id order by prev_id desc)<2; -- order by prev_id to get only last prev_id

LATEST_ID	VENDORITEMID	LATEST_COSTCENTS	LATEST_UPDATEDAT	NAME	PREV_ID	PREV_COSTCENTS	PREV_UPDATEDAT
3	442720	1399	2022-03-18 20:28:15.342	T-Shirt	2	1209	2021-03-11 20:54:16.666
7	2878218	3667	2022-03-16 20:53:11.123	Jeans	6	3432	2021-03-18 20:55:32.642
10	3878218	3597	2022-03-18 20:51:11.123	Jackets	9	3597	2021-03-28 20:51:11.123

The two CTE in last query can be combined into one as -

select
vc.id latest_id,
vc.VENDORITEMID,
vc.COSTCENTS latest_costcents,
vc.UPDATEDAT latest_updatedat,
ic.name,
vcp.id prev_ID,
vcp.COSTCENTS prev_COSTCENTS,
vcp.UPDATEDAT prev_UPDATEDAT
from vendor_cte vc, item_cte ic, vendor_cte vcp
where vc.VENDORITEMID = vcp.VENDORITEMID
and vc.VENDORITEMID = ic.vendoritem
and vcp.id < vc.id
and vc.UPDATEDAT between
'2022-03-01'::timestamp and '2022-03-25'::timestamp 
qualify row_number()
over (partition by vc.VENDORITEMID order by vc.id,vcp.id desc)<2