Question

1 Approved Answer

Posted on Oct 04, 2024

Instructions Use the mock SQL tables to the right to write queries to answer the questions below. Table Descriptions Service_Provider Each row represents an individual

image text in transcribed

Instructions Use the mock SQL tables to the right to write queries to answer the questions below. Table Descriptions Service_Provider Each row represents an individual client company using HomeAdvisors (HA) Lead Services. . S SP_ID is their company id in the HA system, Company Name is their company name, PRIMARY_BIZ_CITY is the city they primarily operate in, PRIMARY_BIZ_PWC is the primary service their business provides, STATUS is the company's current status in the HA system, and ORIGINAL_START_DATE is the date on which the company started using HA services. Status_Dim Provides a description of every service_provider status. Lead_Purchase_Dim Each row represents a lead purchasing event. A lead purchasing event is an attempt by a company to purchase a lead through HA. TRANSACTION_ID is the individual transactions identification number. SP_ID is the id associated with service provider who attempted to purchase a lead. TRANS_STATUS is the status of the transaction and marks a transaction as either a successful purchase, a failed purchase or a purchase that has been since voided. TRANS_AMOUNT is the amount charged by HA for the lead. TRANS_DATE is the date on which the lead event took place. HomeAdvisor Service Professionals SP ID 00123 41578 04279 COMPANY_NAME Hixson Roofing Baker Kitchen Remodeling Behr & Co Painting Peterson Waste Management Kopp Handyman Mohler Electric Service_Provider PRIMARY_BIZ_CITY PRIMARY_BIZ_STATE PRIMARY_BIZ_PWC Laramie WY Roofing Edina MN Kitchen Remodeling St. Louis | Painting Minot ND Waste Removal Anchorage AK Handyman Virgina City Electrical STATUS ORIGINAL_START_DATE 3 1/6/2019 3 7/12/2012 8 4/27/2008 6 6/8/2008 3 5/9/2014 9 NULL 53074 04876 90001 TX HE 14557 Gramke Plumbing Edwardsville IL Plumbing 3 3/2/2017 SP STATUS ID Status_Dim STATUS_DESCRIPTION 1 Prospect 2 Pending Approval 3 Approved 4 Denied 5 Deleted 6 Terminated 7 Cancelled 8 Hold 9 Sale in Progress TRANSACTION_ID SP_ID 1,001 41578 1,002 41578 1,003 14557 1,004 04876 1,005 04876 1,006 04279 1,007 41578 1,008 00123 Lead_Purchase_Dim TRANS_STATUS TRANS AMOUNT TRANS DATE SUCCESS 25.00 4/10/2018 SUCCESS 40.00 5/12/2018 SUCCESS 33.75 5/30/2018 SUCCESS 120.00 8/5/2018 SUCCESS 20.00 10/1/2018 FAILED 66.00 12/16/2018 SUCCESS 74.50 12/18/2018 SUCCESS 200.00 1/7/2019 LIE 9,999 53074 VOIDED 100.00 1/13/2019 Questions 1) In the box provided below, please write a query to select all of the sp_ids and company names from companies that are approved, in the state of Missouri (MO) or Minnesota (MN), and started using HA services before 1/1/2018. SELECT FROM 2) In the box provided below, please write a query to select all of the sp_ids, company_names, and their total lead purchase amount since 1/1/2018. Make sure the results only show currently approved service providers and succesful transactions. SELECT FROM Instructions Use the mock SQL tables to the right to write queries to answer the questions below. Table Descriptions Service_Provider Each row represents an individual client company using HomeAdvisors (HA) Lead Services. . S SP_ID is their company id in the HA system, Company Name is their company name, PRIMARY_BIZ_CITY is the city they primarily operate in, PRIMARY_BIZ_PWC is the primary service their business provides, STATUS is the company's current status in the HA system, and ORIGINAL_START_DATE is the date on which the company started using HA services. Status_Dim Provides a description of every service_provider status. Lead_Purchase_Dim Each row represents a lead purchasing event. A lead purchasing event is an attempt by a company to purchase a lead through HA. TRANSACTION_ID is the individual transactions identification number. SP_ID is the id associated with service provider who attempted to purchase a lead. TRANS_STATUS is the status of the transaction and marks a transaction as either a successful purchase, a failed purchase or a purchase that has been since voided. TRANS_AMOUNT is the amount charged by HA for the lead. TRANS_DATE is the date on which the lead event took place. HomeAdvisor Service Professionals SP ID 00123 41578 04279 COMPANY_NAME Hixson Roofing Baker Kitchen Remodeling Behr & Co Painting Peterson Waste Management Kopp Handyman Mohler Electric Service_Provider PRIMARY_BIZ_CITY PRIMARY_BIZ_STATE PRIMARY_BIZ_PWC Laramie WY Roofing Edina MN Kitchen Remodeling St. Louis | Painting Minot ND Waste Removal Anchorage AK Handyman Virgina City Electrical STATUS ORIGINAL_START_DATE 3 1/6/2019 3 7/12/2012 8 4/27/2008 6 6/8/2008 3 5/9/2014 9 NULL 53074 04876 90001 TX HE 14557 Gramke Plumbing Edwardsville IL Plumbing 3 3/2/2017 SP STATUS ID Status_Dim STATUS_DESCRIPTION 1 Prospect 2 Pending Approval 3 Approved 4 Denied 5 Deleted 6 Terminated 7 Cancelled 8 Hold 9 Sale in Progress TRANSACTION_ID SP_ID 1,001 41578 1,002 41578 1,003 14557 1,004 04876 1,005 04876 1,006 04279 1,007 41578 1,008 00123 Lead_Purchase_Dim TRANS_STATUS TRANS AMOUNT TRANS DATE SUCCESS 25.00 4/10/2018 SUCCESS 40.00 5/12/2018 SUCCESS 33.75 5/30/2018 SUCCESS 120.00 8/5/2018 SUCCESS 20.00 10/1/2018 FAILED 66.00 12/16/2018 SUCCESS 74.50 12/18/2018 SUCCESS 200.00 1/7/2019 LIE 9,999 53074 VOIDED 100.00 1/13/2019 Questions 1) In the box provided below, please write a query to select all of the sp_ids and company names from companies that are approved, in the state of Missouri (MO) or Minnesota (MN), and started using HA services before 1/1/2018. SELECT FROM 2) In the box provided below, please write a query to select all of the sp_ids, company_names, and their total lead purchase amount since 1/1/2018. Make sure the results only show currently approved service providers and succesful transactions. SELECT FROM