HackerRank SQL(Intermediate, Medium) 解法 | Wheather Observation Station 20

問題

www.hackerrank.com

解法(MySQL)

  • そこまで複雑なクエリではないが,中央値の計算はよくやると思うので自分用のメモとして残す.
with
sorted_lat as (
    select
        lat_n,
        @i := @i+1 as r
    from
        station,
        (select @i := 0) as tmp_var
    order by
        lat_n asc
),
number_of_data as (select max(r) as num from sorted_lat)

select
    round(avg(lat_n), 4) as median
from
    sorted_lat
where
    r = (select num+1 from number_of_data) div 2
    or r = ((select num from number_of_data) div 2)+1
;

HackerRank SQL(Medium) 解法 | Occupations

問題

www.hackerrank.com

解法(MySQL)

  • occupationのグループの最大数分の連番を作り,連番にそれぞれLEFT OUTER JOINする.
with doctors as (
    select
        name,
        @d_i := @d_i+1 as r
    from
        occupations,
        (select @d_i := 0) as tmp
    where
        occupation = 'Doctor'
    order by
        name asc
),
professors as (
    select
        name,
        @p_i := @p_i+1 as r
    from
        occupations,
        (select @p_i := 0) as tmp
    where
        occupation = 'Professor'
    order by
        name asc
),
singers as (
    select
        name,
        @s_i := @s_i+1 as r
    from
        occupations,
        (select @s_i := 0) as tmp
    where
        occupation = 'Singer'
    order by
        name asc
),
actors as (
    select
        name,
        @a_i := @a_i+1 as r
    from
        occupations,
        (select @a_i := 0) as tmp
    where
        occupation = 'Actor'
    order by
        name asc
),
r_number as (
    select
        @i := @i+1 as r
    from
        occupations,
        (select @i := 0) as tmp
    where
        @i < (select max(cnt.num) from (select count(*) as num from occupations group by occupation) as cnt)
)

select
    doctors.name,
    professors.name,
    singers.name,
    actors.name
from
    r_number
left join
    doctors on r_number.r = doctors.r
left join
    professors on r_number.r = professors.r
left join
    singers on r_number.r = singers.r
left join
    actors on r_number.r = actors.r
;

HackerRank SQL(Advanced, Medium) 解法 | Print Prime Numbers

問題

www.hackerrank.com

解法(MySQL)

  • infomation_schema.tablesを用いて,十分な行数があるテーブルから2~1000までの連番を作成する.
  • 全ての被除数について,その数より小さいもので割り切れる除数があるものを抽出し素数判定に用いる.
  • 列挙した素数group_concat()で結合する.
with number_table as (
    select
        @num := @num+1 as n
    from
        information_schema.tables as t1,
        information_schema.tables as t2,
        (select @num := 1) as tmp_table
    limit
        999
)

select
    group_concat(divident_table.n separator '&')
from
    (select * from number_table) as divident_table
where
    divident_table.n <= 1000
    and not exists (
        select
            *
        from
            (select * from number_table) as divisor_table
        where
            (divident_table.n % divisor_table.n = 0)
            and (divident_table.n > divisor_table.n)
    )
;