Gem Cuts: Profitability

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).

;WITH
    Profitability(cut, cost, multiplier, original_value, cut_value, profit)
      AS (SELECT  cut, cost, multiplier, u.uncut_value, (u.uncut_value * multiplier) , (u.uncut_value * multiplier) - cost
        FROM    @uncut_gem u
        JOIN     @gem
            ON u.uncut_value * multiplier>= cost
       ),

/*
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.
*/

    BestCut(original_value, profit)
      AS (SELECT  original_value, MAX(profit)
        FROM    Profitability
        WHERE    original_value < 700
        GROUP BY  original_value
       )
SELECT  cut, cost, multiplier,
  MIN(p.original_value) LowerBound, MAX(p.original_value) UpperBound,
    CAST(MIN(p.original_value) * multiplier AS INT) Finished_Lower,
    CAST(MAX(p.original_value) * multiplier AS INT) Finished_Upper,
    CAST(MIN(p.profit) AS INT) Min_Profit,
    CAST(MAX(p.profit) AS INT) Max_Profit
FROM    Profitability p
JOIN     BestCut bc
    ON p.original_value = bc.original_value
       AND p.profit = bc.profit
GROUP BY  cut, cost, multiplier
ORDER BY cost ASC

Results:

Cut	Cost	Multiplier	Lower Bound	Upper Bound	Finished Lower	Finished Upper	Min Profit	Max Profit
Emerald	10	1.25	8	41	10	51	0	41
Oval	20	1.50	38	60	57	90	37	70
Pear	35	1.75	59	60	103	105	68	70
Plumbob	50	2.00	59	84	118	168	68	118
Marquis	75	2.30	84	84	193	193	118	118
Crystal Ball	100	2.60	84	167	218	434	118	334
Brilliant	250	3.50	166	300	581	1050	331	800
Star Cut	400	4.00	299	600	1196	2400	796	2000
Heart-Shaped	1000	5.00	600	699	3000	3495	2000	2495

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

Gem Cuts: Finding Complex Ranges

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_gem TABLE (uncut_value int)DECLARE @gem TABLE (cut VARCHAR(25), cost INT, multiplier DECIMAL (3,2))
;WITH  a(n) AS (SELECT 1 n UNION ALL SELECT 1),  --  2 
    b(n) AS (SELECT a.n FROM a JOIN a a1 ON 1 = 1),  --  4 
    c(n) AS (SELECT b.n FROM b JOIN b b1 ON 1 = 1),  --  16 
    d(n) AS (SELECT c.n FROM c JOIN c c1 ON 1 = 1)  --  256
INSERT INTO @uncut_gem SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY d.n)
        FROM d JOIN c ON 1 = 1 JOIN a ON 1=1

INSERT INTO @gem
SELECT  'emerald', 10, 1.25
 UNION ALL SELECT  'oval', 20, 1.50
 UNION ALL SELECT  'Pear', 35, 1.75
 UNION ALL SELECT  'Plumbob', 50, 2.00
 UNION ALL SELECT  'Marquis', 75, 2.30
 UNION ALL SELECT  'Crystal Ball', 100, 2.60
 UNION ALL SELECT  'Sculptor''s Egg', 175, 3.00
 UNION ALL SELECT  'Brilliant', 250, 3.50
 UNION ALL SELECT  'Star Cut', 400, 4.00
 UNION 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):

SELECT cut, cost, CAST(cost/(multiplier - 1) AS INT) Break_Even,
  CAST(cost * multiplier/(multiplier - 1)  AS INT) cut_value
FROM @gem

Here are the results:

Cut	Cost	Uncut Value	Cut Value
emerald	10	40	50
oval	20	40	60
Pear	35	46	81
Plumbob	50	50	100
Marquis	75	57	132
Crystal Ball	100	62	162
Sculptor's Egg	175	87	262
Brilliant	250	100	350
Star Cut	400	133	533
Heart-Shaped	1000	250	1250

Next: Maxing Profitability

Micro Mojo

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.

Bon Voyage

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.