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.
  1. Prepare
  2. SQL
  3. Basic Join
  4. Ollivander's Inventory
  5. Discussions

Ollivander's Inventory

Sort by

recency

|

2067 Discussions

|

  • eddy_rene_c
     + 1 comment
    WITH cte1 as (
    select id,
            age,
            coins_needed,
             power,
            row_number() over( partition by age,power order by coins_needed ) as rnk
    from wands a 
    left join 
    wands_property b
    on a.code = b.code
    where is_evil = 0    
)

select id, age, coins_needed, power
from 
cte1
where rnk =1 
order by power desc, age desc;

  • cjcwork1
     + 0 comments

    SELECT id, age, coins_needed, power FROM ( SELECT id, age, coins_needed, power, ROW_NUMBER() OVER(PARTITION BY age, power ORDER BY coins_needed) rk FROM Wands w JOIN Wands_Property wp ON w.code=wp.code WHERE is_evil=0 ) WHERE rk=1 ORDER BY power DESC, age DESC ;

  • ceequels
     + 0 comments

    SELECT c.id, a.age, a.min_coins_needed, a.power FROM ( SELECT a.code, a.age, MIN(b.coins_needed) as min_coins_needed, b.power FROM Wands_property a JOIN wands b on a.code = b.code where a.is_evil = 0 group by a.code, a.age, b.power )a JOIN wands c on a.code = c.code and a.power = c.power and a.min_coins_needed = c.coins_needed ORDER BY a.power desc, a.age desc

  • floreaoana22
     + 0 comments

    SELECT w1.id, w2.age, w1.coins_needed, w1.power FROM Wands AS w1 JOIN Wands_Property AS w2 ON w1.code = w2.code WHERE w2.is_evil = 0 AND w1.coins_needed = ( SELECT MIN(a.coins_needed) FROM Wands AS a JOIN Wands_Property AS b ON a.code = b.code WHERE a.power = w1.power AND b.age = w2.age ) ORDER BY w1.power DESC, w2.age DESC;

  • nitika31
     + 0 comments

    II have seen a pattern that none of the window functions are running in hackerrank. Is this issue faced by someone else?