[hackerrank][SQL][Advanced]

 

https://www.hackerrank.com/domains/sql?filters%5Bskills%5D%5B%5D=SQL%20%28Advanced%29 

 

Solve SQL Code Challenges

☆ Draw The Triangle 1

SET @num = 21;

SELECT REPEAT("* ", @num:=@num-1)
/* for without table */
FROM INFORMATION_SCHEMA.TABLES
WHERE @num > 0

Draw The Triangle 2

SET @num = 0;

SELECT REPEAT("* ", @num:=@num+1)
FROM INFORMATION_SCHEMA.TABLES
WHERE @num<20

☆Print Prime Numbers

-

☆☆☆15 Days of Learning SQL

▶ STEP 1 

SELECT S.submission_date, COUNT(DISTINCT S.hacker_id) cnt
FROM Submissions S,
/* condition : submission_date - 2016.03.01(min of submission_date) */
WHERE (
       SELECT COUNT(DISTINCT S2.submission_date)
       FROM Submissions S2
       WHERE S2.submission_date < S.submission_date
            AND S2.hacker_id = S.hacker_id
      )
       = (S.submission_date - (SELECT MIN(S3.submission_date) FROM Submissions S3))
GROUP BY S.submission_date
ORDER BY S.submission_date

▶ STEP 2

SELECT 
    /* submission_date */
        S.submission_date, 
    /* count_of_hacker_id's submit each day */
        COUNT(DISTINCT S.hacker_id) cnt,
    /* hacker_id_max_day */
        (
        SELECT S3.hacker_id 
        FROM Submissions S3
        WHERE S.submission_date = S3.submission_date 
        GROUP BY S3.hacker_id
        ORDER BY count(S3.submission_id) DESC, hacker_id LIMIT 1
        ) AS ID,
    /* hacker_name_max_day */
        (
        SELECT H.Name FROM Hackers H WHERE H.hacker_id = ID
        )

FROM Submissions S
/* condition : submission_date - 2016.03.01(min of submission_date) */
WHERE (
       SELECT COUNT(DISTINCT S2.submission_date)
       FROM Submissions S2
       WHERE S2.submission_date < S.submission_date
            AND S2.hacker_id = S.hacker_id
      )
       = (S.submission_date - (SELECT MIN(S3.submission_date) FROM Submissions S3))
GROUP BY S.submission_date
ORDER BY S.submission_date