Scenario

You work for an e-commerce company and have been given two tables containing order records (with dates in MM/DD/YYYY format) and product details.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("orders") }} with {{ ref("products") }} on product_id using an INNER JOIN
2. Parses order_date from MM/DD/YYYY format using TRY_TO_DATE to handle invalid dates gracefully
3. Drops rows where order_date is invalid (where TRY_TO_DATE returns NULL)
4. Adds an is_weekend column that is TRUE when the parsed date falls on Saturday or Sunday, using DAYOFWEEK
5. Returns user_id, product_name, category, the original order_date string, and is_weekend

Schema

orders

products

Example

orders:

products:

Expected Output:

Note: Row with order_date 99/99/9999 is dropped because it is not a valid date. 01/07/2024 (Sunday), 01/13/2024 (Saturday), and 01/21/2024 (Sunday) are weekends. 01/10/2024 (Wednesday) and 01/15/2024 (Monday) are weekdays.