Teradata SQL Get Most Recent Single Customer By Item

user_4555215 0
Viewed 22

I am struggling in getting the most recent single customer by item. I have a table with sales information like the following

ITEM   INVOICE_DT  CUST_NM  SALES_AMT
ITEM1  1/5/2022    CUST1    100
ITEM1  1/5/2022    CUST2    200
ITEM1  1/1/2022    CUST3    300
ITEM2  1/3/2022    CUST1    50
ITEM2  1/3/2022    CUST2    50
ITEM2  1/1/2022    CUST3    100

I tried the following:

SELECT A.ITEM, A.INVOICE_DT, A.CUST_NM
FROM SALES_TABLE A
INNER JOIN (SELECT ITEM, MAX(INVOICE_DT) AS MAX_INVOICE_DT FROM SALES_TABLE GROUP BY ITEM) B
ON A.ITEM = B.ITEM AND A.INVOICE_DT = B.MAX_INVOICE_DT

The problem I am running into is if multiple customers bought the same item on the same day, the output is duplicated for the same item. I was thinking of using sales amount as a tie-breaker, but I wouldn't know how to implement this. Also in the rare case that two customers bought the same item on the same day with the same total sales amount, I rather pick a 'random' singular customer name than have more records than the number of parts.

Expect Output:

ITEM   INVOICE_DT  CUST_NM
ITEM1  1/5/2022    CUST2
ITEM2  1/3/2022    CUST1 OR CUST2
teradata sql
1 Answers

You can use Ordered Analytical Function to handle such scenario.

With the help of Ordered Analytical Function, you can RANK your customer based on INVOICE_DT and SALES_AMT.

You can rewrite your query as below to get the result.

SELECT  a.item,
         a.invoice_dt,
         a.cust_nm ,
         ROW_NUMBER() OVER(PARTITION BY a.item ORDER BY a.invoice_dt DESC,a.sales_amt DESC) AS rn
FROM sales_table a
 QUALIFY rn=1

Note: It will give random result if both the customer have purchased same item on same day with same amount, you can add order by cust_nm as well to get same result each time.

user_140485110
Related