Скачать или смотреть Hackerrank SQL Solutions | Challenges SQL Hackerrank Intermediate Problem

In this video I am solving the "Challenges" Problem on Hackerrank. This is an intermediate SQL Problem.
____________________________________________

SUBSCRIBE!
Do you want to understand how to solve SQL Problems in every detail? That's what my channel is about. When I was younger I thought I could never program because it looked way too difficult. The truth is that it takes time but with some patience anybody can do it! Follow me along and get better!
____________________________________________

SUPPORT MY CHANNEL

🙌The best way to support my channel right now is to give me a Super Thanks. You can do that by clicking thanks next to the title of the video. It is much appreciated!

____________________________________________

💾GitHub: https://github.com/CuriosityLeonardo?...
✋Add me on LinkedIn to get in contact:   / markus-friemann-221b3814b  
____________________________________________

0:00 Introduction
0:20 Problem Solving Approach
01:09 Problem Description
01:27 Outline Output
03:01 Sample Input Description
5:25 Start solving first Part
9:32 Include With Statement
11:30 Combine tables to get result
14:05 Where Clause
16:12 Order By

Query:

With tbl_num_challenges as (

SELECT
h.hacker_id,
h.name,
COUNT(c.challenge_id) as num_challenges
FROM Hackers h
JOIN Challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
--ORDER BY COUNT(c.challenge_id) desc, hacker_id asc

)

,tbl_count_challenges as (
SELECT
num_challenges,
COUNT(num_challenges) as count_challenges
FROM tbl_num_challenges
GROUP BY num_challenges
--ORDER BY COUNT(num_challenges) desc

)

SELECT
tnc.hacker_id,
tnc.name,
tnc.num_challenges--,
--tcc.count_challenges
FROM tbl_num_challenges tnc
JOIN tbl_count_challenges tcc ON tcc.num_challenges = tnc.num_challenges
WHERE tcc.count_challenges = 1
OR
tnc.num_challenges = (SELECT max(num_challenges) FROM tbl_num_challenges)

ORDER BY
tnc.num_challenges desc,
tnc.hacker_id
;