Join All The Things

The following questions are related to creating basic data tables that Danny and his team can use to quickly derive insights without needing to join the underlying tables using SQL.

Recreate the following table output using the available data:

Solution

SELECT sales.customer_id,
       order_date,
       product_name,
       price,
       (
           CASE join_date IS NULL
           WHEN TRUE THEN 'N'
           ELSE
               (
                   CASE order_date >= join_date
                       WHEN TRUE THEN 'Y'
                       ELSE 'N'
                   END
                )
           END
       ) member
FROM sales
LEFT OUTER JOIN members m ON sales.customer_id = m.customer_id
INNER JOIN menu m2 ON sales.product_id = m2.product_id
ORDER BY customer_id, order_date, product_name;

Walkthrough

Join all the tables, ensuring a LEFT OUTER JOIN from sales to members on customer_id so that even those customers that have never become a member show up in the table.

Additionally, CASE out the order_date and return Y if the customer was a member on the day of the order, and N otherwise. Finally, order the rows by (customer_id, order_date, product_name).

SELECT sales.customer_id,
       order_date,
       product_name,
       price,
       (
           CASE join_date IS NULL
           WHEN TRUE THEN 'N'
           ELSE
               (
                   CASE order_date >= join_date
                       WHEN TRUE THEN 'Y'
                       ELSE 'N'
                   END
                )
           END
       ) member
FROM sales
LEFT OUTER JOIN members m ON sales.customer_id = m.customer_id
INNER JOIN menu m2 ON sales.product_id = m2.product_id
ORDER BY customer_id, order_date, product_name;