HackerRank SQL Solutions

Subdomains Wise Questions

Basic Select

• Revising the Select Query I

    select * from CITY where COUNTRYCODE = 'USA' AND POPULATION > 100000;

• Revising the Select Query II

    select NAME from CITY where COUNTRYCODE = 'USA' AND POPULATION > 120000;

• Select All

    select * from CITY;

• Select By ID

    select * from CITY where ID = 1661;

• Japanese Cities' Attributes

    select * from CITY where COUNTRYCODE = 'JPN';

• Japanese Cities' Names

    select NAME from CITY where COUNTRYCODE = 'JPN';

• Weather Observation Station 1

    select CITY, STATE from STATION;

• Weather Observation Station 3

    select distinct(CITY) from STATION where MOD(ID,2) = 0;

• Weather Observation Station 4

    select COUNT(CITY) - COUNT(DISTINCT(CITY)) from STATION;

• Weather Observation Station 5

    select city, length(city) from station
    order by length(city),city asc
    limit 1;
    select city, length(city) from station
    order by length(city) desc
    limit 1;

• Weather Observation Station 6

    select distinct city from station 
    where left(city,1) in ('a','e','i','o','u')

• Weather Observation Station 7

    select distinct CITY from STATION where (CITY LIKE '%a' OR CITY LIKE '%e' OR CITY LIKE '%i' OR CITY LIKE '%u' OR CITY LIKE '%o');

• Weather Observation Station 8

    select distinct city from station 
    where left(city,1) in ('a','e','i','o','u') 
    and right(city, 1) in ('a','e','i','o','u')

• Weather Observation Station 9

    select distinct city from station 
    where left(city,1) NOT in ('a','e','i','o','u')

• Weather Observation Station 10

    select distinct city from station where right(city, 1) NOT in ('a','e','i','o','u');

• Weather Observation Station 11

    select distinct CITY from STATION where left(CITY,1) NOT IN ('a','e','i','o','u') OR right(CITY,1) NOT IN ('a','e','i','o','u');

• Weather Observation Station 12

    select distinct CITY from STATION where left(CITY,1) NOT IN ('a','e','i','o','u') AND right(CITY,1) NOT IN ('a','e','i','o','u');

• Higher Than 75 Marks

    select name from students where marks > 75 order by substr(name,length(name)-2, 3), id;

• Employee Names

    select name from employee order by name asc;

• Employee Salaries

    select name from employee where salary > 2000 AND months < 10 order by employee_id;

Advanced Select

• Type of Triangle

    SELECT CASE WHEN A + B > C AND A+C>B AND B+C>A THEN CASE WHEN A = B AND B = C THEN 'Equilateral' WHEN A = B OR B = C OR A = C THEN 'Isosceles' WHEN A != B OR B != C OR A != C THEN 'Scalene' END ELSE 'Not A Triangle' END FROM TRIANGLES;

• The PADS

    select concat(name,'(',substr(occupation,1,1),')') from occupations order by name;
    select concat('There are a total of ',count(*),' ',lower(occupation),'s.') from occupations group by occupation order by count(*), occupation;

• Binary Tree Nodes

    SELECT case
        when P IS NULL THEN CONCAT(N, ' Root')
        when N IN (SELECT DISTINCT P FROM BST) THEN CONCAT(N, ' Inner')
        ELSE CONCAT(N, ' Leaf')
        END
    FROM BST
    ORDER BY N ASC

Aggregation

• Revising Aggregations - The Count Function

    select COUNT(ID) from CITY where POPULATION > 100000;

• Revising Aggregations - The Sum Function

    select SUM(POPULATION) from CITY where DISTRICT = 'California';

• Revising Aggregations - Averages

    select AVG(POPULATION) from CITY where DISTRICT = 'California';

• Average Population

    select FLOOR(AVG(POPULATION)) from CITY;

• Japan Population

    select SUM(POPULATION) from CITY where countrycode='JPN';

• Population Density Difference

    select max(population) - min(population) from CITY;

• The Blunder

    SELECT CEIL(AVG(Salary)-AVG(REPLACE(Salary,'0',''))) FROM EMPLOYEES;

• Top Earners

    select months*salary as earn, count(*)from employee group by earn order by earn desc limit 1;

• Weather Observation Station 2

    select round(sum(lat_n),2), round(sum(long_w),2) from station;

• Weather Observation Station 13

    select round(sum(lat_n),4) from station where lat_n > 38.7880 AND lat_n < 137.2345;

• Weather Observation Station 14

    select round(max(lat_n),4) from station where lat_n < 137.2345;

• Weather Observation Station 15

    select round(long_w,4) from station where lat_n=(select max(lat_n) from station where lat_n < 137.2345);

• Weather Observation Station 16

    select min(round(lat_n,4)) from station where lat_n > 38.7780;

• Weather Observation Station 17

    select round(long_w,4) from station where lat_n=(select min(lat_n) from station where lat_n > 38.7780);

• Weather Observation Station 18

    SELECT ROUND( MAX(lat_n)-MIN(lat_n) + MAX(long_w)-MIN(long_w), 4) FROM Station;

• Weather Observation Station 19

    SELECT ROUND(
    SQRT(
        POWER((MAX(lat_n)-MIN(lat_n)), 2)
        + POWER((MAX(long_w)-MIN(long_w)), 2)
        ),
    4) FROM Station;

• Weather Observation Station 20

    select round(s.lat_n, 4) from station s where (select count(lat_n) from station where s.lat_n > lat_n ) = (select count(lat_n) from station where s.lat_n < lat_n ); 

Basic Join

• Population Census

    select sum(c.population) from city c, country d where c.countrycode = d.code AND d.continent ='Asia';

• African Cities

    select c.name from city c, country d where c.countrycode = d.code AND d.continent ='Africa';

• Average Population of Each Continent

    select d.continent, floor(avg(c.population)) from city c, country d where c.countrycode = d.code group by d.continent;

• The Report

    select if(g.grade<8, 'NULL', s.name), g.grade, s.marks from students as s, grades as g where s.marks between g.min_mark and g.max_mark order by g.grade desc, s.name; 

• Contest Leaderboard

    select h.hacker_id, name, sum(score) as total_score
    from
    hackers as h inner join
    (select hacker_id,  max(score) as score from submissions group by challenge_id, hacker_id) max_score
    on h.hacker_id=max_score.hacker_id
    group by h.hacker_id, name
    having total_score > 0
    order by total_score desc, h.hacker_id;

Advanced Join

• Interviews

    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 con.contest_id = col.contest_id 
    join challenges cha on  col.college_id = cha.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 cha.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 cha.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;

• 15 Days of Learning SQL

    select 
    submission_date ,

    ( SELECT COUNT(distinct hacker_id)  
    FROM Submissions s2  
    WHERE s2.submission_date = s1.submission_date AND    (SELECT COUNT(distinct s3.submission_date) FROM      Submissions s3 WHERE s3.hacker_id = s2.hacker_id AND s3.submission_date < s1.submission_date) = dateDIFF(s1.submission_date , '2016-03-01')) ,

    (select hacker_id  from submissions s2 where s2.submission_date = s1.submission_date 
    group by hacker_id order by count(submission_id) desc , hacker_id limit 1) as shit,
    (select name from hackers where hacker_id = shit)
    from 
    (select distinct submission_date from submissions) s1
    group by submission_date

• SQL Project Planning

    
    SET sql_mode = '';
    SELECT Start_Date, End_Date
    FROM 
        (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
        (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b 
    WHERE Start_Date < End_Date
    GROUP BY Start_Date 
    ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date

Alternative Queries

• Draw The Triangle 1

    DECLARE @i INT = 20
    WHILE (@i > 0) 
    BEGIN
    PRINT REPLICATE('* ', @i) 
    SET @i = @i - 1
    END

• Draw The Triangle 2

    DECLARE @i INT = 1
    WHILE (@i < 21) 
    BEGIN
    PRINT REPLICATE('* ', @i) 
    SET @i = @i + 1
    END

• Print Prime Numbers

    SELECT GROUP_CONCAT(NUMB SEPARATOR '&')
    FROM (
        SELECT @num:=@num+1 as NUMB FROM
        information_schema.tables t1,
        information_schema.tables t2,
        (SELECT @num:=1) tmp
    ) tempNum
    WHERE NUMB<=1000 AND NOT EXISTS(
        SELECT * FROM (
            SELECT @nu:=@nu+1 as NUMA FROM
                information_schema.tables t1,
                information_schema.tables t2,
                (SELECT @nu:=1) tmp1
                LIMIT 1000
            ) tatata
        WHERE FLOOR(NUMB/NUMA)=(NUMB/NUMA) AND NUMA<NUMB AND NUMA>1
    )