본문 바로가기

언어64

percent_rank, ntile with ecoli_ds as (select *,percent_rank() over(order by size_of_colony desc) 'pr'from ecoli_data)select id,casewhen pr  with ecoli_ds as (select *,percent_rank() over(order by size_of_colony desc) 'pr'from ecoli_data)select id,casewhen pr between 0 and 0.25 then 'CRITICAL'when pr between 0.25 and 0.5 then 'HIGH'when pr between 0.5 and 0.75 then 'MEDIUM'else 'LOW'end as 'COLONY_NAME'from ecoli_ds.. 2024. 7. 8.
비트연산 select id, email, first_name, last_namefrom developers as dwhere d.skill_code & (select code from skillcodes where name="Python") != 0 or d.skill_code & (select code from skillcodes where name="C#") != 0order by id/*SELECT *FROM DEVELOPERS D, SKILLCODES SWHERE 1 = 1 -- always TRUE (outer join) AND (SKILL_CODE & S.CODE) != 0 AND NAME IN ('Python', 'C#')ORDER BY ID;*/  select count(*) .. 2024. 7. 2.
recursive & repeat with recursive cte as ( select 20 as NUM union all select NUM-1 from CTE where NUM > 0)select repeat('* ', NUM)from cte  with recursive cte as (select 1 as NUMunion allselect NUM+1 from ctewhere NUM   with recursive cte as (select 2 as NUMunion allselect NUM+1from ctewhere NUM 2024. 6. 25.
interviews select c.contest_id, hacker_id, name, sum(total_submissions) 'total_submissions',sum(total_accepted_submissions) 'total_accepted_submissions',sum(total_views) 'total_views',sum(total_unique_views) 'total_unique_views'from contests cleft join colleges co on c.contest_id=co.contest_idleft join challenges ch on co.college_id=ch.college_idleft join (select challenge_id, sum(total_views) 't.. 2024. 6. 20.
Symmetric Pairs -- f(20) = 21일 때, f(21) = 20이면 페어-- 둘 중 x가 y보다 작은 경우만 출력-- self pair 묶지 않도록 rn 할당with cte as( select *, row_number() over() as rn from functions)select distinct ori.x, ori.yfrom cte oriinner join cte sub on ori.x=sub.y and ori.y=sub.x and ori.rn != sub.rnwhere ori.x 2024. 6. 19.
with as with 테이블명 as'as' 빼먹지 않기!!!with cnt as (select c.hacker_id, name, count(distinct challenge_id) 'cnt_challenge'from hackers hjoin challenges c on h.hacker_id=c.hacker_idgroup by c.hacker_id, name)select hacker_id, name, cnt_challengefrom cntwhere cnt_challenge=(select max(cnt_challenge) from cnt)or cnt_challenge in (select cnt_challenge from cnt group by cnt_challenge having count(hacker_id)=1)ord.. 2024. 6. 18.
mysql에 pivot 없음 with n as (select *, row_number() over (partition by occupation order by name) rnfrom occupations)select min(if(occupation='Doctor', name, null)) 'Doctor', min(if(occupation='Professor', name, null)) 'Professor', min(if(occupation='Singer', name, null)) 'Singer', min(if(occupation='Actor', name, null)) 'Actor'from ngroup by rn/*select Doctor, Professor, Singer, Actorfrom npi.. 2024. 6. 14.
basic join: top competitors (medium) Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of.. 2024. 6. 14.
basic join: the report (medium) 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 alphabetic.. 2024. 6. 13.
median: percent_rank with cte as (select *, percent_rank() over (order by lat_n) pr\from station)select round(lat_n,4)from ctewhere pr =0.5 2024. 6. 12.

티스토리툴바