T-Sql Challenges from Hackerrank

T-SQL Challenges from hackerrank

Line 9 The multi-part identifier "Challeneges.challenge_id" could not be bound.

Looks like you spelt Challenges wrong.

Need help understanding COUNT and <> operators (Hackerrank SQL Challenges question)

Suppose you get a list of hacker ids and sub counts out of the query when you don't have this clause:

hacker, counter 
1, 10
2, 9
3, 9

Two and three shouldn't be in there because they're tied on count, so we can implement it as excluding anyone who counted 9

Consider that conceptually the database will run the query for every row in the results: when processing hacker 2 row the query gets a list of challenge counts where someone whose id isnt 2. This means when considering hacker 2, the dB will pull back a list of the following counts:

10,  --it comes from hacker 1
9 --it comes from hacker 3

The database then goes "i'm processing hacker 2, whose count is 9. I may only include hacker 2 in the results if hacker 2's count(9) is not in the following list of values: 10, 9. Oh, 9 is in the list of banned values. I'll exclude hacker 2 from the results

Repeat for hacker 3, this time a 9 count comes from hacker 2 so 3 is also excluded

SQL Query optimization written for an Hacker Rank Challenge

You can use common table expressions to turn your repeated subqueries into inline views which you can query just as you would a normal view:

WITH cteCount AS (SELECT c.HACKER_ID,
COUNT(c.CHALLENGE_ID) TOT_CH
FROM CHALLENGES c
GROUP BY HACKER_ID),
cteDups AS (SELECT TOT_CH,
COUNT(TOT_CH) AS DUPS
FROM cteCount
GROUP BY TOT_CH)
SELECT h.HACKER_ID,
h.NAME,
t.TOT_CH
FROM HACKERS h
INNER JOIN cteCount t
ON t.HACKER_ID = h.HACKER_ID
INNER JOIN cteDups d
ON d.TOT_CH = t.TOT_CH
WHERE CASE
WHEN d.DUPS < 2
THEN 1
WHEN t.TOT_CH = (SELECT MAX(TOT_CH)
FROM cteCount)
THEN 1
END = 1
ORDER BY t.TOT_CH DESC,
h.HACKER_ID;

SQL syntax error on Hackerrank challenge - Ollivander's Inventory

In mysql you should use a select from ( select ... ) and not with

eg

select * from  (Select
w.power,
wp.age,
Min(w.coins_needed) As min_coins
From
wands w Join
wands_property wp
On wp.code = w.code
Where
wp.is_evil = 0
Group By
w.power, wp.age) t
......

Looking through your code:

Select w.id, wp.age, w.coins_needed, w.power 
From (Select w.power, wp.age, Min(w.coins_needed) As min_coins
From wands w Join wands_property wp On wp.code = w.code
Where wp.is_evil = 0 Group By w.power, wp.age) m
Join wands_property wp On wp.code = w.code
Join m On m.age = wp.age
And m.power = w.power
And m.min_coins = w.coins_needed
Where wp.is_evil = 0
Order By w.power Desc, wp.age Desc, w.coins_needed Desc

You query must be rethinked because at least you have the e following problems

  1. You don't have the table w because you have used the related select naming m the resulting table so could be

  2. you are referring to w.id but you have not a w table in your query

  3. you are select a min_coins but you don't have this column in main select so is not available for where/and clause

  4. the w.code column is not available ( the w table not exist , at least exsite the m table but you anyway don'thave the code column )

So you should rebuilt your query starting from the first part (the subselect in from () clause ) rebuilt your query

Hackerrank Challenge Ollivander's Inventory

This was my previously submitted (accepted) solution in MS SQL Server, but this much should be the same for MySQL.

SELECT
W.id,
WP.age,
W.coins_needed,
W.power
FROM Wands W
INNER JOIN Wands_Property WP ON W.code = WP.code
WHERE WP.is_evil != 1
AND W.coins_needed = (
SELECT
MIN(W2.coins_needed)
FROM Wands W2
INNER JOIN Wands_Property WP2 ON W2.code = WP.code
WHERE WP2.age = WP.age
AND W2.power = W.power)
ORDER BY
W.power DESC,
WP.age DESC

You need to find the minimum coins separately and then find who has those coins.


For your code, you're not doing the inner join with WHERE WP.age = P.age, that should reduce your unnecessary rows, but you'll still have an issue with getting the value of the coins.


It seems MySQL supports partial GROUP BYs but HackerRank's platform is not yet set up for that. Look into this (original) question for explanation and a slightly different solution.

SQL Server: Top competitor with at least one full scoring submission

Group by both the hacker id and name

i made this query with another join road and worked fine

SELECT h.hacker_id , h.name
FROM submissions s
INNER JOIN hackers h on h.hacker_id = s.hacker_id
INNER JOIN challenges c on c.challenge_id = s.challenge_id
INNER JOIN difficulty d on d.difficulty_level = c.difficulty_level

WHERE s.score = d.score
AND c.difficulty_level = d.difficulty_level


GROUP BY h.hacker_id ,h.name
HAVING COUNT(s.submission_id) > 1
ORDER BY COUNT(s.submission_id) DESC, h.hacker_id ASC

Hackerrank SQL challenge

Since you want first and last, I'd probably just use a union and top 1. makes it clear as to what you're after and easy to maintain.

And since you can use alias in order by... I'd alias len(city)

SELECT TOP 1 
city, len(city) LenCity
FROM
station
ORDER BY
LenCity ASC

UNION ALL

SELECT TOP 1
city, Len(City) lenCity
FROM
station
ORDER BY
LenCity DESC

Hacker Rank SQL problem | Problem using count() and MINUS | Weather Observation Station 4

Your query does not do what you want. MINUS is a set-based operator, while what you need is aggregation. Here, you can take the difference between COUNT(*) (that's the total number of rows in the table) and COUNT(DISTINCT city) (that's the count of distinct values in column city):

select count(*) - count(distinct city) result
from station


Related Topics



Leave a reply



Submit