## Wednesday, June 11, 2008

### SQL for Buying a New Car

Two weeks ago I purchased a new car. The main reason behind that was that I drove a 1996 Nissan P.O.S. (not point of sale either). I now had a 30+ mile commute each way and I (well, my wife and mother) needed a reliable car.

A big factor was gas mileage.

Two brands stood out, Honda and Toyota. Honda has the Civic Hybrid and Toyota has the Prius (even though I don't care much for the design). The Corolla and Civic were the top two cars.

So after visiting those two dealers to look at cars, I stopped by the Mazda dealer to see if they had anything with MPG greater than 30. I drove the Mazda3 and it was nice (everything would be nice compared to my POS). Salesman offered to run the numbers and I said OK.

The point of this rambling, is that I want to know what it will cost based on mileage. Of course I didn't have my laptop, so I couldn't run the numbers (and I hadn't previously). I did it in Excel, but wanted to try it out in SQL.

Here's the result:
`COLUMN ppg FORMAT \$999.00COLUMN car_1_gpy FORMAT 99,999COLUMN car_2_gpy FORMAT 99,999COLUMN car_1_cpy FORMAT \$999,999.00COLUMN car_2_cpy FORMAT \$999,999.00COLUMN car_1_cpm FORMAT 999.00COLUMN car_2_cpm FORMAT 999.00SELECT  car_1,  car_2,  ppg,  mpy,  ROUND( ( mpy / car_1 ) ) car_1_gpy,  ROUND( ( mpy / car_2 ) ) car_2_gpy,  ROUND( ( ( mpy / car_1 ) * ppg ), 2 ) car_1_cpy,  ROUND( ( ( mpy / car_2 ) * ppg ), 2 ) car_2_cpy,  ROUND( ( ( mpy / car_1 ) * ppg ) / mpy, 2 ) car_1_cpm,  ROUND( ( ( mpy / car_2 ) * ppg ) / mpy, 2 ) car_2_cpmFROM  (    SELECT      rownum car_1,      LAG( rownum, 5 ) OVER        ( PARTITION BY NULL          ORDER BY rownum ) car_2    FROM dual      CONNECT BY LEVEL <= 50  ) car_miles,  (    SELECT      3.99 ppg,      15000 mpy    FROM dual  ) constants/     CAR_1      CAR_2 CAR_1_GPY CAR_2_GPY    CAR_1_CPY    CAR_2_CPY CAR_1_CPM CAR_2_CPM---------- ---------- --------- --------- ------------ ------------ --------- ---------         1               15,000             \$59,850.00                   3.99         2                7,500             \$29,925.00                   2.00         3                5,000             \$19,950.00                   1.33         4                3,750             \$14,962.50                   1.00         5                3,000             \$11,970.00                    .80         6          1     2,500    15,000    \$9,975.00   \$59,850.00       .67      3.99         7          2     2,143     7,500    \$8,550.00   \$29,925.00       .57      2.00         8          3     1,875     5,000    \$7,481.25   \$19,950.00       .50      1.33         9          4     1,667     3,750    \$6,650.00   \$14,962.50       .44      1.00        10          5     1,500     3,000    \$5,985.00   \$11,970.00       .40       .80        11          6     1,364     2,500    \$5,440.91    \$9,975.00       .36       .67        12          7     1,250     2,143    \$4,987.50    \$8,550.00       .33       .57        13          8     1,154     1,875    \$4,603.85    \$7,481.25       .31       .50        14          9     1,071     1,667    \$4,275.00    \$6,650.00       .28       .44        15         10     1,000     1,500    \$3,990.00    \$5,985.00       .27       .40        16         11       938     1,364    \$3,740.63    \$5,440.91       .25       .36        17         12       882     1,250    \$3,520.59    \$4,987.50       .23       .33        18         13       833     1,154    \$3,325.00    \$4,603.85       .22       .31        19         14       789     1,071    \$3,150.00    \$4,275.00       .21       .28        20         15       750     1,000    \$2,992.50    \$3,990.00       .20       .27        21         16       714       938    \$2,850.00    \$3,740.63       .19       .25        22         17       682       882    \$2,720.45    \$3,520.59       .18       .23        23         18       652       833    \$2,602.17    \$3,325.00       .17       .22        24         19       625       789    \$2,493.75    \$3,150.00       .17       .21        25         20       600       750    \$2,394.00    \$2,992.50       .16       .20        26         21       577       714    \$2,301.92    \$2,850.00       .15       .19        27         22       556       682    \$2,216.67    \$2,720.45       .15       .18        28         23       536       652    \$2,137.50    \$2,602.17       .14       .17        29         24       517       625    \$2,063.79    \$2,493.75       .14       .17        30         25       500       600    \$1,995.00    \$2,394.00       .13       .16        31         26       484       577    \$1,930.65    \$2,301.92       .13       .15        32         27       469       556    \$1,870.31    \$2,216.67       .12       .15        33         28       455       536    \$1,813.64    \$2,137.50       .12       .14        34         29       441       517    \$1,760.29    \$2,063.79       .12       .14        35         30       429       500    \$1,710.00    \$1,995.00       .11       .13        36         31       417       484    \$1,662.50    \$1,930.65       .11       .13        37         32       405       469    \$1,617.57    \$1,870.31       .11       .12        38         33       395       455    \$1,575.00    \$1,813.64       .11       .12        39         34       385       441    \$1,534.62    \$1,760.29       .10       .12        40         35       375       429    \$1,496.25    \$1,710.00       .10       .11        41         36       366       417    \$1,459.76    \$1,662.50       .10       .11        42         37       357       405    \$1,425.00    \$1,617.57       .09       .11        43         38       349       395    \$1,391.86    \$1,575.00       .09       .11        44         39       341       385    \$1,360.23    \$1,534.62       .09       .10        45         40       333       375    \$1,330.00    \$1,496.25       .09       .10        46         41       326       366    \$1,301.09    \$1,459.76       .09       .10        47         42       319       357    \$1,273.40    \$1,425.00       .08       .09        48         43       313       349    \$1,246.88    \$1,391.86       .08       .09        49         44       306       341    \$1,221.43    \$1,360.23       .08       .09        50         45       300       333    \$1,197.00    \$1,330.00       .08       .09`
Obviously this isn't terribly difficult. You can do lots with the results as well.

Three or four years ago I would have created a table and a function to do this. Now I can do it "virtually."

I love SQL...

*Key:
car_1 = miles for car 1
car_2 = miles for car 2
car_1_gpy = gallons per year car 1
car_2_gpy = gallons per year car 2
car_1_cpy = cost per year car 1 (gas)
car_2_cpy = cost per year car 2 (gas)
car_1_cpm = cost per mile car 1
car_2_cpm = cost per mile car 2

Tim said...

So what did ya get? car_1 or car_2

John Scott said...

It's just a shame we can't Flashback the gas prices too ;)

oraclenerd said...

@tim

Ummm...car 1? (the mazda 3)

@john
Yes, that would be awesome!