We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  • HackerRank Home

    HackerRank

  • |
  • Prepare
  • Certify
  • Compete
  • Hiring developers?
  1. Prepare
  2. SQL
  3. Basic Join
  4. Ollivander's Inventory
  5. Discussions

Ollivander's Inventory

Problem
Submissions
Leaderboard
Discussions

Sort 1491 Discussions, By:

recency

Please Login in order to post a comment

  • swethaom03
    3 hours ago+ 0 comments
    select w.id, wp.age, w.coins_needed, w.power
    from wands as w
    inner join (select code, min(coins_needed) as cn, power
    from wands
    group by code, power) mc
    on w.code=mc.code and w.coins_needed=mc.cn and w.power=mc.power
    inner join wands_property as wp
    on w.code=wp.code
    where wp.is_evil=0
    order by w.power desc, wp.age desc;
    
    0|
    Permalink
  • datadev_nui
    6 hours ago+ 0 comments

    MySql 5.7

    SELECT
        w.id,
        wp.age,
        w.coins_needed,
        w.power
    FROM wands w
        JOIN wands_property wp ON wp.code = w.code
    WHERE wp.is_evil = 0 AND w.coins_needed = (
        SELECT
            MIN(coins_needed)
        FROM wands derived_w
        JOIN wands_property derived_wp ON derived_wp.code = derived_w.code
        WHERE derived_wp.age = wp.age AND derived_w.power = w.power
    )
    ORDER BY w.power DESC, wp.age DESC
    ;
    
    0|
    Permalink
  • KrystalDev1
    6 hours ago+ 0 comments

    USING MS SQL SERVER BECAUSE MYSQL WOULD NOT ACCEPT ANY FORM OF CTE:

    WITH cte1 AS (SELECT w.id AS cteid, wp.age AS cteage, w.coins_needed AS ctecoin, w.power AS ctepower FROM Wands w LEFT JOIN Wands_Property wp ON w.code = wp.code WHERE wp.is_evil = 0),

    cte2 AS (SELECT wp.age AS cteage, MIN(w.coins_needed) AS ctecoin, w.power AS ctepower FROM Wands w LEFT JOIN Wands_Property wp ON w.code = wp.code WHERE wp.is_evil = 0 GROUP BY wp.age, w.power)

    SELECT cte1.cteid, cte2.cteage, cte2.ctecoin, cte2.ctepower FROM cte1 RIGHT JOIN cte2 ON cte1.cteage = cte2.cteage AND cte1.ctecoin = cte2.ctecoin ORDER BY cte2.ctepower DESC, cte2.cteage DESC;

    0|
    Permalink
  • Mohak_mj
    10 hours ago+ 0 comments

    With Cte as (select WP.age as Cte_Age, min(W.coins_needed) as Cte_Coins,W.power as Cte_Power from Wands W left join Wands_Property WP on W.code = WP.code where WP.is_evil = 0 group by WP.age, W.power ) select max(A.id) ,Cte_Age,Cte_Coins,Cte_Power from Cte inner join Wands A on A.coins_needed = Cte_Coins and A.power = Cte_Power inner join Wands_Property B on B.Age = Cte_Age where B.is_evil = 0 group by Cte_age,Cte_Coins, Cte_Power order by Cte_Power desc, Cte_Age desc

    0|
    Permalink
  • Mohak_mj
    10 hours ago+ 1 comment

    With Cte as (select WP.age as Cte_Age, min(W.coins_needed) as Cte_Coins,W.power as Cte_Power from Wands W left join Wands_Property WP on W.code = WP.code where WP.is_evil = 0 group by WP.age, W.power ) select max(A.id) ,Cte_Age,Cte_Coins,Cte_Power from Cte inner join Wands A on A.coins_needed = Cte_Coins and A.power = Cte_Power inner join Wands_Property B on B.Age = Cte_Age where B.is_evil = 0 group by Cte_age,Cte_Coins, Cte_Power order by Cte_Power desc, Cte_Age desc

    0|
    Permalink
Load more conversations

Need Help?


View top submissions
  • Blog
  • Scoring
  • Environment
  • FAQ
  • About Us
  • Support
  • Careers
  • Terms Of Service
  • Privacy Policy