Here ya go Bob!
Black Friday is here and the marketing director for ZYX company wants to create a mass mail campaign to promote its new Wizbang2010 product. She wants to target 100,000 of her customers in the US and Canada whom, through data mining, are most likely to buy. A brochure along with a letter listing the three nearest stores will be mailed to each customer. You are already formulating a query in your head and think: no problem--but of course she has additional requirements.
Every one of her targeted customers are fiscally prudent (cheap) and will travel to adjacent states/provinces or even across the border (either direction, US <->Canada) to take advantage of lower sales tax rates. Customers will drive at most 10 miles to an adjacent state/province that has a lower sales tax than the state/province in which they reside. In addition, customers will drive at most 30 miles to a state with no sales tax.
Knowing this, the marketing director would like to accommodate each customer by listing these stores on the letter, even if there is a store closer within their own state. Most customers will simply have all three of their physically nearest retailers on the letter, those along borders may not.
Business Rules:
The precedence for consideration of a customer's three "closest" stores for imprinting on the letter is as follows:
- One or more stores in a state with no sales tax and are within 30 miles of the customer (whom resides in a state/province with sales tax)
- One or more stores in a state/province with lower sales tax and are within 10 miles of the customer (whom resides in a state/province with a higher sales tax)
- One or more stores physically closest to the customer
Given this, it could be the case where there exists a store 1 mile away in the same state as the customer and 3 stores 9 miles away in (one or more states) with no sales tax (or lower sales tax) Those 3 stores should be on the letter not the one(s) physically closest.
The EUR/USD is currently trading at $1.06, normally ZYX's US customers take advantage of the loonie's buying power but most Canadian provinces levy taxes greater than 10% so we will not consider that for this promotion.
Everything is going First Class mail (ZYX does not have a mail management app and delivery point data is not available for these customers) so the ordering should be by PostalCode, CustomerID, Nearest StoreID, TaxRate asc.
The result set should have 300,000 rows and the following columns: CustomerID,Customer.City,Customer.PostalCode,Customer.Country,StoreID,Store.City, Store.PostalCode,Store.Country,Distance(miles). For those customers who travel to a store in their own postal code, assume they are exactly 1 mile away from the store.
No calling Google maps api or the like. Use the geography/geospatial functionality of SQL Server or create your own Haversine function. We only care about great circle distance, not road distance. Assume the Earth is perfectly spherical.
The db tables are owned by a third-party app so the existing columns cannot be altered (you may add any columns needed). You may add any non-clustered indexes needed.
Finally, we would like to do some analysis on sales tax revenue and fuel consumption. We need one report on total revenue by state/province and the net gain/loss from customers crossing borders. So the report should have three columns: [State/Province],[TotalSalesTaxRevenue],[Net Gain/Loss] Ordered by Net Gain/Loss descending. The Wizbang2010 goes for $1000 and every customer will purchase one (give that data miner a raise)
To get a feel for the impact on the environment, as a direct result of this promotion, a second query should return one row showing the grand total miles traveled(round trip), total gallons of fuel burned(round trip), and the postal code & distance(one way) of the poor soul(s) who had to travel the farthest. Those with vehicles getting >=35MPG (the environmentally righteous elite) will always travel to the physically nearest store [printed on their letter] everyone else will opt for the first store listed.
You know you are the best at your job and you would never tell marketing something can’t be done, right? This is a toy problem so don’t get distracted by the incomplete postal code list, imprecise tax rates or horrid db design.
Thank you in advance for doing my homework and have a great Thanksgiving! You can download the data and table scripts Here 7.62MB
Cheers,
Scot
