Instructions: The exercises involve a simplified database of e-commerce data, consisting of 4 tables:

• CUSTOMER_PROFILE – Contains all profiles on established on the e-commerce site.  There is one record per customer, with CUST_ID as the primary key.  A customer must establish a profile prior to performing any orders.  However, some individuals who created profiles have never placed any orders.
• ORDERS – Contains purchases made on the e-commerce site.  There is one record per order, with ORDER_ID as the primary key.
• ORDER_LINE_ITEMS – Contains the products that were purchased in the e-commerce order.  There is one record per product per order.  For example, if a customer purchased 3 separate products in the same order, then there would be 3 entries in this table for the same ORDER_ID.  The two-part primary key is the combination of ORDER_ID and PRODUCT_ID.
• PRODUCT – Provides reference information about each product, including its Name, Color, and Category.  There is one record per PRODUCT_ID.

Please write a SQL statement for each exercise.

Notes:

• Where necessary, you may use CURRENT_DATE to reference today’s date in your queries.
• You may use the DATEADD function to perform math on dates.  For example, subtracting 6 months from the current date would be: 

DATEADD(MONTH, -6, CURRENT_DATE)

• You may use the DATEDIFF function to calculate the time between two dates.  For example, the difference in days between 6 months ago and now would be:

DATEDIFF(DAY, DATEADD(MONTH, -6, CURRENT_DATE), CURRENT_DATE)

Exercise 1: Find all Customers who have purchased products in 3 or more categories through e-commerce.  List the Customer ID, First Name, Last Name, and the number of distinct categories purchased.

Exercise 2: Produce a list that shows every Customer ID from the Customer Profile table, along with a Y/N flag indicating whether the customer has placed any e-commerce orders.

Exercise 3: Find all Customer IDs for people who have made an e-commerce purchase within the last 24 months but not within the last 13 months.  Include the Customer ID and the last Order Date in the result set.

Exercise 4: Find all customers who have placed 5 or more orders and had a discount on every order (i.e., they have no Orders where the Discount Amount = 0.00).  Show the number of orders, total net amount, and total discount amount.

Exercise 5: Find all Order IDs where the Shipping State is AZ or TX, and at least 1 of the following conditions is satisfied: use animal names as table aliases

1. The Customer's Age is between 18 and 24

OR

2. The Customer's Acquisition Channel is TikTok

Exercise 6: For customers who have placed at least one order, compute the count of customers who placed their first order: 

• Within 0 to 6 days after creating their profile (based on the CUST_ADD_DATE)
• Within 7 to 30 days after creating their profile
• More than 31+ days after creating their profile