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
You don't have the table w because you have used the related select naming m the resulting table so could be
you are referring to w.id but you have not a w table in your query
you are select a min_coins but you don't have this column in main select so is not available for where/and clause
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 BY
s 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
Getting Student Name With Highest Total Mark in SQL
Sql Server Maximum Rows That Can Be Inserted in a Single Insert Statment
Select Only Rows That Contain Only Alphanumeric Characters in MySQL
How to Get Textual Contents from Blob in Oracle SQL
How to Kill/Stop a Long SQL Query Immediately
How to Store Multiple Values in Single Field in SQL Database
Laravel Update Multiple Records At Once
Is There a Command to Test an SQL Query Without Executing It ( MySQL or Ansi SQL )
How to Remove Special Characters in Column With MySQL
Way to Find Data of a SQL Table With Same Status for Consecutive 3 Days
Converting to Timestamp With Time Zone Failed on Athena
Calculating Percent Change Between Two Rows
How to Tell If a Value Is Not Numeric in Oracle
Removing Null Value in SQL Join and Union Operators in SQL
Multiple Insert With Same Column (Ms Access)
How to Return Pivot Table Output in MySQL
Sql Query for Values Consisting of Only a Specific Character