I have a raw transactional table that looks something like this:

To summarize the daily sales of each product, I can use the following code:

SELECT
Transaction_Date,
Product_Name,
SUM(Product_Qty) as Daily_Sold
FROM MyTable
Group By 1, 2

My goal is to find the date that each product launched. I would have just grabbed the minimum date for each product in the transactional table; however, due to the structure of my data, some products show negative TOTAL transactions (likely test transactions in our point of sale system--these consist of both positive quantities and negative quantities but they total negative over the course of the day) before they launch. So, my real goal is to find the minimum date where each product had a TOTAL units sold greater than 0. Is there a simple way to do this in one query? The only solution I have found so far is as follows:

SELECT
Product_Name,
MIN(Transaction_Date) as Launch_Date

FROM

(SELECT
Transaction_Date,
Product_Name,
SUM(Product_Qty) as Daily_Sold
FROM MyTable
Group By 1, 2
HAVING Daily_Sold > 0)

Group By 1

I am hoping to avoid this nested query, because my real queries aren't quite this simple (they involve joins and a lot of filters). I am using Snowflake if that is helpful. Anyone able to find a simpler way to solve what I am looking for? Maybe a window function or something?

CodePudding user response：

Using QUALIFY clause to find first transaction date per product:

SELECT Transaction_Date,
       Product_Name,
       SUM(Product_Qty) as Daily_Sold
FROM MyTable
Group BY Transaction_Date,Product_Name
HAVING Daily_Sold > 0
QUALIFY ROW_NUMBER() OVER(PARTITION BY Product_Name ORDER BY Transaction_Date) = 1

CodePudding user response：

Untested but:

SELECT Transaction_Date, Product_Name
     , SUM(Product_Qty) OVER (PARTITION BY Transaction_Date, PRODUCT_NAME) 
     , FIRST_VALUE(TRANSACTION_DATE) OVER (PARTITION BY PRODUCT_NAME ORDER BY TRANSACTION_DATE) 
FROM MyTable