John Lekberg

JUNTO Practice - "Ollivander's Inventory"

Discussed on 2019-10-06.

Problem is "Ollivander's Inventory".

Hint: This query is looking for the cheapest wand for a given combination of age and power. In the WHERE clause of your query, use a subquery to make sure the it has the minimum coins needed compared to all other wands of the same age and power.

Solution - Daniel

SELECT W.ID, P.AGE, W.COINS_NEEDED, W.POWER 
FROM WANDS W, WANDS_PROPERTY P 
WHERE W.CODE = P.CODE AND P.IS_EVIL = '0' AND COINS_NEEDED = (SELECT MIN(COINS_NEEDED) FROM WANDS WHERE CODE = W.CODE AND POWER =W.POWER)
GROUP BY P.AGE, W.COINS_NEEDED, W.ID, W.POWER 
ORDER BY W.POWER DESC, P.AGE DESC;

Instead of using a cross join and a condition in the where clause I think you should just do an inner join. Changing

...
FROM WANDS W, WANDS_PROPERTY P
WHERE W.CODE = P.CODE AND ...

to

...
FROM WANDS W
     INNER JOIN WANDS_PROPERTY P
                ON W.CODE = P.CODE
WHERE ...

I don't think the group by clause is needed in this case. (Test it out just to confirm that.) I also really like your use of the correlated subquery

COINS_NEEDED = (SELECT MIN(COINS_NEEDED) ...)

I find it easy to read, and I think it's a good design choice.

-- John

Solution - John

WITH
Good_Wands(id, coins_needed, power, age) AS (
  SELECT id, coins_needed, power, age
    FROM Wands
         INNER JOIN Wands_Property
                    ON Wands.code = Wands_Property.code
   WHERE is_evil = 0
),
Cheapest(coins_needed, power, age) AS (
    SELECT MIN(coins_needed), power, age
      FROM Good_Wands
  GROUP BY power, age
)
  SELECT id, age, coins_needed, power
    FROM Cheapest NATURAL JOIN Good_Wands
ORDER BY power DESC, age DESC
;

I'm using a natural join instead of an inner join because I designed the common table expressions Good_Wands and Cheapest to have corresponding attributes. An equivalent inner join would look like

...
FROM Good_Wands
     INNER JOIN Cheapest
                ON Good_Wands.coins_needed = Cheapest.coins_needed
                   AND Good_Wands.power = Cheapest.power
                   AND Good_Wands.age = Cheapest.age
...

Daniel, I thought about naming tables after our discussion last week.

-- John

I like the use of the CTE.

-- Daniel

Solution - Oscar

SELECT
good.id,
good.age,
cheap.coins_needed,
good.power
FROM (
    SELECT
    WP.age,
    MIN(W.coins_needed) as coins_needed,
    W.power
    FROM Wands as W
    LEFT JOIN Wands_Property as WP
    ON W.Code=WP.Code
    WHERE WP.is_evil = 0
    GROUP BY W.power,WP.age
    ) as cheap
NATURAL JOIN (
    SELECT
    W2.id,
    W2.coins_needed,
    W2.power,
    WP2.age
    FROM Wands as W2
    LEFT JOIN Wands_Property as WP2
    ON W2.Code = WP2.Code
    WHERE WP2.is_evil = 0
    ) as good
ORDER BY good.power DESC,good.age DESC;

Looks like this and John's solution have a similar technique.

-- Daniel

In this case I don't think you need to use a left join, and in my opinion an inner join would be more clear. It looks like you are using a similar structure to my solution, your subqueries are named cheap and good which correspond with my Good_Wands and Cheapest.

-- John