Snowflake Left Join with date range

0
votes

I am struggling with a snowflake Database LEFT JOIN query with a date range. Please find the sample table contents below

enter image description here

My Snowflake SQL Query : SELECT O.ORDER_DATE, CASE WHEN ORDER_DATE IS NULL THEN 'NO' ELSE 'YES' END AS ORDER_PLACED, C.CUSTOMER_ID, C.NAME FROM CUSTOMER C LEFT JOIN ORDERS ON C.CUSTOMER_ID=O.CUSTOMER_ID AND O.ORDER_DATE >= DATEADD(DAY, -3, CURRENT_DATE)

The output I expect - I would like to get last 3 days records with all the customers and whether they made an order or not, something like below

enter image description here

The current data output I am getting with the given snowflake query is

enter image description here

I don't know if the problem is with the date range(I need the date range anyway), as i'm getting required results for the very first day. But for remaining days i'm only getting those records, when an order is placed. As shown in the expected result, i would like to get all the customer records with details like whether they made an order or not.

Thanks in advance

2
sqldatabaseleft-joinsnowflake-cloud-data-platform
Don't post images, please! – Antonio Veneroso Contreras
Just scanning this, but have you tried removing "LEFT" and using an inner join? – Steve Midgley
Last 3 days of orders for each customer or orders within the last 3 days? – Ross Bush
It appears you want all the records from the customer table with a indication of "order placed" within the last three days. If this is the case then the LEFT JOIN is correct, however, you are removing customers who have placed an order more than 3 days ago. – Ross Bush
Please use text, not images/links, for text--including tables & ERDs. Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Please before you post look at the formatted version of your post below the edit box. Read the edit help re inline & block formats for code & quotations. – philipxy

2 Answers

0
votes

Use a cross join to generate the rows and then left join to bring in the results:

SELECT O.CTE, C.CUSTOMER_ID, C.NAME,
       MAX(CASE WHEN O.CUSTOMER_ID IS NULL THEN 'NO' ELSE 'YES' END) AS ORDER_PLACED, 
FROM CUSTOMER C CROSS JOIN
     (SELECT DISTINCT O.ORDER_DATE AS dte
      FROM ORDERS O
      WHERE O.ORDER_DATE >= DATEADD(DAY, -3, CURRENT_DATE)
     ) D LEFT JOIN
     ORDERS
     ON C.CUSTOMER_ID =  AND AND D.DTE = O.ORDER_DATE
GROUP BY O.CTE, C.CUSTOMER_ID, C.NAME;

You can list the dates explicitly for the d derived table, of course.

0
votes

Should it be the ORDER_DATE or the CALENDAR_DATE driving the query? If noone orders on a particular day, they should all have NO in the ORDER_PLACED column?

If so, some changes to the answer from Gordon Linoff: 

SELECT d.CAL_DATE, DECODE(COUNT(o.ORDER_ID), 0, 'NO', 'YES') ORD_PLCD, c.CUSTOMER_ID, c.NAME
FROM (
    SELECT DATEADD(DAYS, -SEQ1(), CURRENT_DATE) CAL_DATE
    FROM TABLE(GENERATOR(ROWCOUNT => 4))
) d
CROSS JOIN CUSTOMER c
LEFT OUTER JOIN ORDERS o ON o.ORDER_DATE = d.CAL_DATE AND o.CUSTOMER_ID = c.CUSTOMER_ID
GROUP BY d.CAL_DATE, c.CUSTOMER_ID, c.NAME