Monday, September 24, 2012

In my last post, I set up a table (@uncut_gem) that had the integers 1 to 5000 in it and another table (@gem) that has the types of cuts, cost, and multiplier value that is applied to the gem when it is cut. Then, I selected from the @gem table, using a simple algebraic formula to find the cutoff value where the value of the cut gem was at least the value of the cut plus the value of the original, uncut gem. Now I want to find out, not just where the value is where I won't lose money, but at what value I'll start making money.
To do this, I'm going to use another CTE. First, I'm going to get the cartesian product of gems x cuts, so 50,000 records, and calculate the profitability. Here's how I'm going to do that:

The cut value is the uncut value * the multiplier

The profit is the cut value - cost of the cut

I'm not going to factor in the cost of the uncut gem here because it doesn't change as the multiplier changes, so it won't affect the outcome (if I'm trying to figure out what to do with a gem with an uncut value of 200, if I choose the Brilliant cut, it would yield a cut gem with a value of 700, minus the 250 Simolean cost of the cut is a profit of 450 Simoleans; if I choose the Crystal Ball cut I get a gem with a value of 460 minus the 100 cost of the cut is a profit of 360. Subtracting the original 200 from each of them does not change their profitability relative to each other, and that's all I care about here).

/*
So, for instance, here is the spread for a gem with an uncut value of 168:

Uncut Value

Cut

Cost

Multiplier

Cut Value

Profit

168

Emerald

10

1.25

210

200

168

Oval

20

1.50

252

232

168

Pear

35

1.75

294

259

168

Plumbob

50

2.00

336

286

168

Marquis

75

2.30

386

311

168

Crystal Ball

100

2.60

436

336

168

Sculptor's Egg

175

3.00

504

329

168

Brilliant

250

3.50

588

338

168

Star Cut

400

4.00

672

272

Continuing our query, we add another CTE. Now we group by the original value and find the maximum profit value for each original value. We'll be able to use this max profit to join back to the Profitability CTE and select just the Cuts with the highest profitability rating. If more than one cut has the same profitability for the same gem, this will return all cuts with that top profitability rating.
*/

As you can see, there is some overlap, especially in the lower value cuts. I'm capping the original value at 700 for this because at 700, the Heart Cut returns a gem valued at 3500 Simoleans, which creates some interesting possibilities using another aspect of the game.
Next: Getting Transfiguration Figures

Sunday, September 23, 2012

I play a time-devouring, absolutely maddening, terrifically addictive game called The Sims. And by "play" I mean "alternate between an obsession where I can play the game for ten hours a day and wake up thinking about it, and going cold turkey." And not only do I play the game, I write SQL queries to analyze aspects of the game and form strategies for it, because I am a nerd.

The Sims is a game where the player gets to create a "household" of up to eight members, called Sims. These Sims eat and sleep and bathe and go to work and paint masterpieces and write novels and learn new skills and vacation, and basically have lives that are much more interesting than yours because they don't spend them staring at a computer screen playing games (actually, some of them do that too...)

One of the aspects of the game is that the sims collect precious gems which, in the Sim world, are just lying around on the ground pretty much all over the place (in Sim world the Star of Africa is just another diamond...) The Sims can then cut these gems. Each cut has a different cost and applies a different value multiplier to the base value of the gem:

Cut

Cost

Multiplier

Emerald

10

1.25

Oval

20

1.50

Pear

35

1.75

Plumbob

50

2.00

Marquis

75

2.30

Crystal Ball

100

2.60

Sculptor's Egg

175

3.00

Brilliant

250

3.50

Star Cut

400

4.00

Heart-Shaped

1000

5.00

The question then is: when the Sim finds a gem, how do we know what the most profitable cut would be? To find out, I started by creating a CTE to generate the integers 1-5000. I capped it at 5000 for two reasons: 1) I haven't seen any gems in the game with an uncut value higher than 5000 and 2) if there was one, I already know it would be worth it to use the highest value cut.

DECLARE@uncut_gemTABLE(uncut_valueint)DECLARE@gemTABLE(cutVARCHAR(25),costINT,multiplierDECIMAL(3,2));WITHa(n)AS(SELECT1nUNIONALLSELECT1),-- 2 b(n)AS(SELECTa.nFROMaJOINaa1ON1=1),-- 4 c(n)AS(SELECTb.nFROMbJOINbb1ON1=1),-- 16 d(n)AS(SELECTc.nFROMcJOINcc1ON1=1)-- 256INSERTINTO@uncut_gemSELECTTOP5000ROW_NUMBER()OVER(ORDERBYd.n)FROMdJOINcON1=1JOINaON1=1INSERTINTO@gemSELECT'emerald',10,1.25UNION ALL SELECT'oval',20,1.50UNION ALL SELECT'Pear',35,1.75UNION ALL SELECT'Plumbob',50,2.00UNION ALL SELECT'Marquis',75,2.30UNION ALL SELECT'Crystal Ball',100,2.60UNION ALL SELECT'Sculptor''s Egg',175,3.00UNION ALL SELECT'Brilliant',250,3.50UNION ALL SELECT'Star Cut',400,4.00UNION ALL SELECT'Heart-Shaped',1000,5.00

So now I have the base tables. Next, I'll run some numbers. First, I want to know where my break even points are. I want to know what value a gem has to be for the value of the cut gem to be at least the value of the cut plus the value of the original gemstone, so for instance, a plumbob cut costs 50 Simoleans (what other unit of currency would a Sim use?) and doubles the value of the gem. A rough gem with a 50 Simolean value would be worth 100 Simoleans with a plumbob cut, which would cover the cost of the cut plus the value of the original gem and leave a profit of 0. So I'm looking for (uncut * multiplier) - cost - uncut = 0, or to put it another way, uncut = cost/(multiplier - 1):

Monday, July 30, 2012

Now Reading: How Microsoft Lost Its Mojo by Kurt Eichenwald. There are some problems, as I see are noted in the comments, but I did like this quote very much:

More employees seeking management slots led to more managers, more managers led to more meetings, more meetings led to more memos, and more red tape led to less innovation. Everything, one executive said, advanced at a snail’s pace.

Monday, July 23, 2012

Hi and welcome to Structured Query Blog. I'm Jennifer Kenney. I've been writing code since I was in 5th grade, but for the last 10 years, I've been writing it mostly in SQL. These days, I find that I think a lot more about how to get software delivered than I do about how to get it written, and this blog is going to reflect both those obsessions.