Interviews,15 Days of Learning SQL
Interviews 📝
https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true
➡️
- 단계 1️⃣ : contests,colleges,challenges INNERJOIN
- 단계 2️⃣ : view_status,submission_status -> challenge_id로 SUM(total) 값을 구하기
- 단계 3️⃣ : 단계 2에서 SUM한 테이블들과 단계 1에서 JOIN 한 값들 OUTER JOIN
-> GROUP BY contest_id,hacker_id,name
Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are .
Note: A specific contest can be used to screen candidates at more than one college, but each college only holds screening contest.
Input Format
The following tables hold interview data:
- Contests: The contest_id is the id of the contest, hacker_id is the id of the hacker who created the contest, and name is the name of the hacker.
- Colleges: The college_id is the id of the college, and contest_id is the id of the contest that Samantha used to screen the candidates.
- Challenges: The challenge_id is the id of the challenge that belongs to one of the contests whose contest_id Samantha forgot, and college_id is the id of the college where the challenge was given to candidates.
- View_Stats: The challenge_id is the id of the challenge, total_views is the number of times the challenge was viewed by candidates, and total_unique_views is the number of times the challenge was viewed by unique candidates.
-
- Submission_Stats: The challenge_id is the id of the challenge, total_submissions is the number of submissions for the challenge, and total_accepted_submission is the number of submissions that achieved full scores.
-
Sample Input
Contests Table:
Colleges Table:
Challenges Table:
View_StatsTable:
Submission_Stats Table:
Sample Output
66406 17973 Rose 111 39 156 56
66556 79153 Angela 0 0 11 10
94828 80275 Frank 150 38 41 15
Explanation
The contest 66406 is used in the college 11219. In this college 11219, challenges 18765 and 47127 are asked, so from the view and submission stats:
- Sum of total submissions = 27 + 56 + 28 = 111
- Sum of total accepted submissions = 10 + 18 + 11 = 39
- Sum of total views= 43 + 72 + 26 + 15 = 156
- Sum of total unique views = 10 + 13 + 19 + 14 = 56
Similarly, we can find the sums for contests 66556 and 94828.
SELECT
con.contest_id,con.hacker_id,con.name,
SUM(sum_totals),SUM(sum_accepteds),SUM(sum_totalv),SUM(sum_uniquev)
FROM contests con
JOIN colleges col ON con.contest_id = col.contest_id
JOIN challenges ch ON col.college_id = ch.college_id
LEFT OUTER JOIN
(SELECT challenge_id,sum(total_views) sum_totalv,sum(total_unique_views) sum_uniquev
FROM view_stats
GROUP BY challenge_id) v
ON ch.challenge_id = v.challenge_id
LEFT OUTER JOIN
(SELECT challenge_id,sum(total_submissions) sum_totals,sum(total_accepted_submissions) sum_accepteds
FROM submission_stats
GROUP BY challenge_id) s
ON ch.challenge_id = s.challenge_id
GROUP BY con.contest_id,con.hacker_id,con.name
HAVING SUM(s.sum_totals) > 0 OR SUM(s.sum_accepteds) > 0 OR SUM(v.sum_totalv) > 0 OR SUM(v.sum_uniquev) > 0
ORDER BY CONTEST_ID;
15 Days of Learning SQL📝
https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem?isFullScreen=true
➡️
- 2016년 3월 1일부터 2016년 3월 15일까지 15일간의 SQL 대회를 진행
- 1️⃣ 매일 제출한 해커 수와 2️⃣ 그날 당일에 최대 제출 수를 기록한 해커의 ID( 단, 2명 이상의 해커가 최대 제출 수를 가질 경우에는 ID가 가장 낮은 해커 출력 )
- ORDER BY 일자
- 1️⃣ -> 매일 제출한 해커 수 구하기
SELECT submission_date, count(distinct hacker_id) as hacker_count
FROM submissions as s1
WHERE submission_date - DATE('2016-03-01') = (
SELECT count(distinct submission_date)
FROM submissions as s2
WHERE s2.submission_date < s1.submission_date
and s2.hacker_id = s1.hacker_id
)
GROUP BY submission_date;
- 2️⃣ -> 그날 당일에 최대 제출 수를 기록한 해커의 ID 구하기
SELECT submission_date, hacker_id as best_hacker
FROM submissions as s3
WHERE hacker_id = (
SELECT hacker_id
FROM submissions as s4
WHERE s4.submission_date = s3.submission_date
GROUP BY hacker_id
ORDER BY count(submission_id) desc, hacker_id limit 1
)
GROUP BY submission_date, hacker_id;
- 3️⃣ -> hackers 테이블과 조인시키기
Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.
Write a query to print total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.
Input Format
The following tables hold contest data:
- Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
- Submissions: The submission_date is the date of the submission, submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, and score is the score of the submission.
Sample Input
For the following sample input, assume that the end date of the contest was March 06, 2016.
Hackers Table:
Submissions Table:
Sample Output
2016-03-01 4 20703 Angela
2016-03-02 2 79722 Michael
2016-03-03 2 20703 Angela
2016-03-04 2 20703 Angela
2016-03-05 1 36396 Frank
2016-03-06 1 20703 Angela
Explanation
On March 01, 2016 hackers 20703, 36396, 53473, and 79722 made submissions. There are 4 unique hackers who made at least one submission each day. As each hacker made one submission,20703 is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.
On March 02, 2016 hackers 15758 ,20703 and 79722 made submissions. Now 20703 and 79722 were the only ones to submit every day, so there are unique 2 hackers who made at least one submission each day. 79722 made 2 submissions, and name of the hacker is Michael.
On March 03, 2016 hackers 20703 , 36396, and 79722 made submissions. Now 20703 and 79722 were the only ones, so there are 2 unique hackers who made at least one submission each day. As each hacker made one submission so 20703 is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.
On March 04, 2016 hackers 20703, 44065, 53473 and 79722 made submissions. Now 20703 and 79722 only submitted each day, so there are 2 unique hackers who made at least one submission each day. As each hacker made one submission so 20703 is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.
On March 05, 2016 hackers 20703, 36396,38289 and 62529 made submissions. Now 20703 only submitted each day, so there is only 1 unique hacker who made at least one submission each day. 36396 made 2 submissions and name of the hacker is Frank.
On March 06, 2016 only 20703 made 1 submission, so there is only unique hacker who made at least one submission each day. 20703 made 1 submission and name of the hacker is Angela.
SELECT s_count.submission_date, s_count.hacker_count, s_best.best_hacker, h.name
FROM (
SELECT submission_date, count(distinct hacker_id) as hacker_count
FROM submissions as s1
WHERE submission_date - DATE('2016-03-01') = (
SELECT count(distinct submission_date)
FROM submissions as s2
WHERE s2.submission_date < s1.submission_date
and s2.hacker_id = s1.hacker_id
)
GROUP BY submission_date
) as s_count
JOIN (
SELECT submission_date, hacker_id as best_hacker
FROM submissions as s3
WHERE hacker_id = (
SELECT hacker_id
FROM submissions as s4
WHERE s4.submission_date = s3.submission_date
GROUP BY hacker_id
ORDER BY count(submission_id) desc, hacker_id limit 1
)
GROUP BY submission_date, hacker_id
) as s_best on s_count.submission_date = s_best.submission_date
JOIN hackers as h on s_best.best_hacker = h.hacker_id
GROUP BY s_count.submission_date, s_count.hacker_count, s_best.best_hacker, h.name
ORDER BY s_count.submission_date;