3. SQL for Data Preparation

Activity 5: Building a Sales Model Using SQL Techniques

Solution

1. Open your favorite SQL client and connect to the sqlda database.
2. Follow the steps mentioned with the scenario and write the query for it. There are many approaches to this query, but one of these approaches could be:

   SELECT 
   c.*,
   p.*,
   COALESCE(s.dealership_id, -1),
   CASE WHEN p.base_msrp - s.sales_amount >500 THEN 1 ELSE 0 END AS high_savings 
   FROM sales s
   INNER JOIN customers c ON c.customer_id=s.customer_id
   INNER JOIN products p ON p.product_id=s.product_id
   LEFT JOIN dealerships d ON s.dealership_id = d.dealership_id;

3. The following is the output of the preceding code:

Figure 3.21: Building a sales model query

Thus, have the data to build a new model that will help the data science team to predict which customers are the best prospects for remarketing from the output generated.