[TIL] 241115

SQL 문제풀이

퀘스트

#1[Lv1. 데이터 속 김서방 찾기]
select count(name) as name_cnt
from users u 
where name like '김%'
;


#2[Lv2. 날짜별 획득포인트 조회하기]
select date_format(created_at,'%Y-%m-%d') created_at,
	   round(avg(point),0) average_points
from point_users
group by 1

 

프로그래머스

#1
SELECT ROUND(AVG(DAILY_FEE),0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'
;

#2
SELECT HISTORY_ID,
       CAR_ID,
       DATE_FORMAT(START_DATE,'%Y-%m-%d') START_DATE,
       DATE_FORMAT(END_DATE,'%Y-%m-%d') END_DATE,       
       IF(DATEDIFF(END_DATE,START_DATE)+1>=30,'장기 대여','단기 대여') RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC
;

#3
SELECT CAR_ID,
       CAR_TYPE,
       DAILY_FEE,
       OPTIONS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE ('%네비게이션%')
ORDER BY CAR_ID DESC
;

#4
SELECT  B.TITLE TITLE,
        B.BOARD_ID BOARD_ID,
        R.REPLY_ID REPLY_ID,
        R.WRITER_ID WRITER_ID,
        R.CONTENTS CONTENTS,
        DATE_FORMAT(R.CREATED_DATE,'%Y-%m-%d') CREATED_DATE
FROM USED_GOODS_BOARD B JOIN USED_GOODS_REPLY R ON B.BOARD_ID = R.BOARD_ID
WHERE B.CREATED_DATE LIKE '2022-10%'
ORDER BY  R.CREATED_DATE, B.TITLE
;

#5
SELECT ID,
       EMAIL,
       FIRST_NAME,
       LAST_NAME
FROM DEVELOPER_INFOS
WHERE SKILL_1 = 'Python' OR SKILL_2 = 'Python' OR SKILL_3 = 'Python'
ORDER BY ID
;

 

 

파이썬 문제풀이

#1 코드 처리하기 (10점)
def solution(code):
    mode = 0
    ret = ''
    
    for idx in range(0,len(code)):
        if mode == 0:
            if code[idx] != '1' and idx % 2 == 0:
                ret += code[idx]
            elif code[idx] == '1':
                mode = 1
                
        elif mode == 1:
            if code[idx] != '1' and idx % 2 == 1:
                ret += code[idx]
            elif code[idx] == '1':
                mode = 0
                
    if ret == '':
        return 'EMPTY'
    else:
        return ret
        
        
#2 등차수열의 특정한 항만 더하기 (1점)
def solution(a, d, included):
    answer = 0
    
    for i in range(len(included)):
        if included[i]:  
            answer += a + i * d
    
    return answer
    
    
#3 주사위 게임2 (4점)
def solution(a, b, c):
    answer = 1
      
    if a != b and a != c and b != c:
        answer = a + b + c
        
    elif a == b and a == c:
        answer = (a + b + c)*(a**2 + b**2 + c**2)*(a**3 + b**3 + c**3)
        
    else:        
        answer = (a + b + c)*(a**2 + b**2 + c**2)
    
    return answer

    
#4 원소들의 곱과 합 (1점)
def solution(num_list):
    p = 0
    m = 1
    
    for i in range(0,len(num_list)):
        p += num_list[i]
        m *= num_list[i]
    
    if m < p**2:
        answer = 1
    else:
        answer = 0
       
    return answer
    
    
#5 이어 붙인 수 (1점)
def solution(num_list):
    answer = 0
    odd = ''
    even = ''
    
    for i in range(0,len(num_list)):
        if num_list[i] %2 == 0:
            even += str(num_list[i])
        else:
            odd += str(num_list[i])
    answer = int(even) + int(odd)
    
    return answer