HACKER RANK

Basic Select
Basic Join
Advanced Join

Basic Select

Basic Join

African Cities

Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is ‘Africa’.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

CITY

Field	Type
ID	NUMBER
NAME	VARCHAR2(17)
COUNTRYCODE	VARCHAR2(3)
DISTRICT	VARCHAR2(20)
POPULATION	NUMBER

COUNTRY

Field	Type
CODE	VARCHAR2(3)
NAME	VARCHAR2(44)
CONTINENT	VARCHAR2(13)
REGION	VARCHAR2(25)
SURFACEAREA	NUMBER
INDEPYEAR	VARCHAR2(5)
POPULATION	NUMBER
LIFEEXPENTACY	VARCHAR2(4)
GNP	NUMBER
GNPOLD	NUMBER
LOCALNAME	VARCHAR2(44)
GOVERMENTFROM	VARCHAR2(44)
HEADOFSTATE	VARCHAR2(32)
CAPITAL	VARCHAR2(4)
CODE2	VARCHAR2(2)

SELECT c.NAME
FROM CITY c
JOIN COUNTRY co ON c.COUNTRYCODE = co.CODE
WHERE co.CONTINENT = 'Africa';

Average Population of Each Continent

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

CITY

Field	Type
ID	NUMBER
NAME	VARCHAR2(17)
COUNTRYCODE	VARCHAR2(3)
DISTRICT	VARCHAR2(20)
POPULATION	NUMBER

COUNTRY

Field	Type
CODE	VARCHAR2(3)
NAME	VARCHAR2(44)
CONTINENT	VARCHAR2(13)
REGION	VARCHAR2(25)
SURFACEAREA	NUMBER
INDEPYEAR	VARCHAR2(5)
POPULATION	NUMBER
LIFEEXPENTACY	VARCHAR2(4)
GNP	NUMBER
GNPOLD	NUMBER
LOCALNAME	VARCHAR2(44)
GOVERMENTFROM	VARCHAR2(44)
HEADOFSTATE	VARCHAR2(32)
CAPITAL	VARCHAR2(4)
CODE2	VARCHAR2(2)

SELECT co.CONTINENT, CAST(AVG(c.POPULATION) AS INT)
FROM COUNTRY co
JOIN CITY c ON c.COUNTRYCODE = co.CODE
GROUP BY co.CONTINENT

Population Census

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is ‘Asia’.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

CITY

Field	Type
ID	NUMBER
NAME	VARCHAR2(17)
COUNTRYCODE	VARCHAR2(3)
DISTRICT	VARCHAR2(20)
POPULATION	NUMBER

COUNTRY

Field	Type
CODE	VARCHAR2(3)
NAME	VARCHAR2(44)
CONTINENT	VARCHAR2(13)
REGION	VARCHAR2(25)
SURFACEAREA	NUMBER
INDEPYEAR	VARCHAR2(5)
POPULATION	NUMBER
LIFEEXPENTACY	VARCHAR2(4)
GNP	NUMBER
GNPOLD	NUMBER
LOCALNAME	VARCHAR2(44)
GOVERMENTFROM	VARCHAR2(44)
HEADOFSTATE	VARCHAR2(32)
CAPITAL	VARCHAR2(4)
CODE2	VARCHAR2(2)

SELECT SUM(c.POPULATION)
FROM CITY c
JOIN COUNTRY co ON co.CODE = c.COUNTRYCODE
WHERE co.CONTINENT = 'Asia'

The Report

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Column	Type
ID	Integer
Name	String
Marks	Integer

Grades contains the following data:

Grades	Min_Mark	Max_Mark
1	0	9
2	10	19
3	20	29
4	30	39
5	40	49
6	50	59
7	60	69
8	70	79
9	80	89
10	90	100

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn’t want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade – i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use “NULL” as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

SELECT
    CASE
    WHEN g.Grade < 8 THEN NULL
    ELSE s.Name
    END AS Name,
    g.Grade, s.Marks
FROM Students s
JOIN Grades g ON s.Marks BETWEEN g.Min_Mark AND g.Max_Mark
ORDER BY g.Grade DESC, s.Name ASC, s.Marks ASC;

Sample Output

Maria 10 99
Jane 9 81
Julia 9 88
Scarlet 8 78
NULL 7 63
NULL 7 68

Advanced Join

SQL Project Planning

You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

Column	Type
Task_ID	Integer
Start_Date	Date
End_Date	Date

If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.
Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.\

WITH Project_Start_Date as(
    SELECT
            Start_Date,
            Rank() OVER (ORDER BY Start_Date) as Rank_Start
    FROM    Projects
    WHERE   Start_Date NOT IN(SELECT End_Date FROM Projects)

),

Project_End_Date as (
    SELECT
        End_Date,
        Rank() OVER (ORDER BY End_Date) as Rank_End
FROM    Projects
WHERE   End_Date NOT IN(SELECT Start_Date FROM Projects)
)

SELECT
        Start_Date,
        End_Date
FROM    Project_Start_Date, Project_End_Date
WHERE   Rank_Start = Rank_End

ORDER BY
DATEDIFF(day,Start_Date,End_Date),
Start_Date

Sample Output

2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04

Placements

You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary (offered salary in $ thousands per month).

Students

Column	Type
ID	Intger
Name	String

Friends

Column	Type
ID	Integer
Friend_ID	Integer

Packages

Column	Type
ID	Integer
Salary	Float

Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

SELECT s.name
FROM Students s
    JOIN Friends f ON s.id = f.id
    JOIN Packages p1 ON f.id = p1.id
    JOIN packages p2 ON f.friend_id = p2.id
WHERE p1.Salary < p2.Salary
ORDER BY p2.Salary;

Sample Output

Samantha
Julia
Scarlet

Symmetric Pairs

You are given a table, Functions, containing two columns: X and Y.

Column	Type
X	Integer
Y	Integer

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.
Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.

SELECT fa.x, fa.y
FROM Functions fa
    JOIN Functions fb ON fa.x = fb.y AND fa.y = fb.x
GROUP BY fa.x, fa.y
HAVING COUNT(fa.x) > 1 OR fa.x < fa.y
ORDER BY fa.x

Sample Output

20 20
20 21
22 23

Interviews

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 0.
Note: A specific contest can be used to screen candidates at more than one college, but each college only holds screening contest.

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.

Column	Type
contest_id	integer
hacker_id	integer
name	string

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

Column	Type
college_id	integer
contest_id	integer

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.

Column	Type
challenge_id	integer
collecge_id	integer

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.

Column	Type
challenge_id	integer
total_views	integer
total_unique_views	integer

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.

Column	Type
challenge_id	integer
total_submissions	integer
total_accepted_submissions	integer

SELECT
    con.contest_id, con.hacker_id, con.name,
    SUM(total_submissions), SUM(total_accepted_submissions),
    SUM(total_views), SUM(total_unique_views)
FROM Contests con
JOIN Colleges col ON col.contest_id = con.contest_id
JOIN Challenges c ON c.college_id = col.college_id
LEFT JOIN
    (
        SELECT
            challenge_id,
            SUM(total_views) as total_views,
            sum(total_unique_views) as total_unique_views
        FROM view_stats
        GROUP BY challenge_id
    ) vs ON c.challenge_id = vs.challenge_id
LEFT JOIN
    (
        SELECT
            challenge_id,
            SUM(total_submissions) AS total_submissions,
            SUM(total_accepted_submissions) AS total_accepted_submissions
        FROM submission_stats
        GROUP BY challenge_id
    ) ss ON c.challenge_id = ss.challenge_id
GROUP BY con.contest_id, con.hacker_id, con.name
HAVING SUM(total_submissions) <> 0 or
SUM(total_accepted_submissions) <> 0 or
SUM(total_views) <> 0 or
SUM(total_unique_views) <> 0
ORDER BY contest_id;

Sample Output

66406 17973 Rose 111 39 156 56
66556 79153 Angela 0 0 11 10
94828 80275 Frank 150 38 41 15

15 Days of Learning SQL

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.

Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

Column	Type
hacker_id	integer
name	string

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.

Column	Type
submission_date	date
submission_id	integer
hacker_id	integer
score	integer

Solution’s Reference

WITH dates_hackers AS (
    SELECT submission_date, hacker_id, COUNT(*) subs
    FROM Submissions
    GROUP BY submission_date, hacker_id
    ),
day1_hackers AS (
    SELECT DISTINCT(hacker_id)
    FROM Submissions
    WHERE submission_date = '2016-03-01'
),
min_max AS (
    SELECT hacker_id, MIN(submission_date) first_sub, MAX(submission_date) last_sub,
    DATEDIFF(day, MIN(submission_date), MAX(submission_date)) diff
    FROM Submissions
    GROUP BY hacker_id
),
cumuCountLag AS (
    SELECT hacker_id, submission_date,
    LAG(submission_date) OVER(PARTITION BY hacker_id ORDER BY submission_date) prevDay,
    COUNT(*) OVER(PARTITION BY hacker_id ORDER BY submission_date) cumuCount
    FROM dates_hackers
),
p1_interm AS (
    SELECT cumuCountLag.hacker_id, cumuCountLag.submission_date, prevDay, first_sub, cumuCount,
    CASE WHEN (cumuCountLag.hacker_id IN (SELECT * FROM day1_hackers))
    AND (cumuCount = DATEDIFF(day, first_sub, cumuCountLag.submission_date) + 1)
    THEN 1
    ELSE 0 END AS partic_till
    FROM cumuCountLag
    LEFT JOIN  min_max ON cumuCountLag.hacker_id = min_max.hacker_id
),
p1 AS (
    SELECT submission_date, SUM(partic_till) no_hackers_till
    FROM p1_interm
    GROUP BY submission_date
),
topHackers AS (
    SELECT dates_hackers.submission_date, dates_hackers.hacker_id, h.name, dates_hackers.subs,
    RANK() OVER(PARTITION BY dates_hackers.submission_date ORDER BY dates_hackers.subs DESC,
    dates_hackers.hacker_id) AS sRank
    FROM dates_hackers
    JOIN Hackers h ON dates_hackers.hacker_id = h.hacker_id
),
p2 AS (
    SELECT submission_date, hacker_id, name
    FROM topHackers
    WHERE sRank = 1
)
SELECT p1.submission_date, p1.no_hackers_till, p2.hacker_id, p2.name
FROM p1
JOIN p2
ON p1.submission_date = p2.submission_date

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

lepepe/hacker-rank

HACKER RANK

Table of Contents

Basic Select

Basic Join

African Cities

Average Population of Each Continent

Population Census

The Report

Advanced Join

SQL Project Planning

Placements

Symmetric Pairs

Interviews

15 Days of Learning SQL