Posts

SELECT route,
    CONCAT(ROUND(SUM(d_between_dist), 1), 'km') AS total_distance,
    CONCAT(ROUND(AVG(d_between_dist), 2), 'km') AS average_distance
FROM subway_distance
GROUP BY route
ORDER BY ROUND(SUM(d_between_dist), 1) DESC;

-- SELECT route,
--     CONCAT(ROUND(SUM(d_between_dist), 1), 'km') AS total_distance,
--     CONCAT(ROUND(AVG(d_between_dist), 2), 'km') AS average_distance
-- FROM subway_distance
-- GROUP BY route
-- ORDER BY total_distance DESC;

문제에서 요구한 결과를 만들기 위해 쿼리에서 실제로 쓴 것들이다.

WITH cars_with_fee AS (
    SELECT
        c.car_id,
        c.car_type,
        FLOOR(c.daily_fee * 30 * (100 - p.discount_rate) / 100) AS fee
    FROM
        car_rental_company_car AS c
    JOIN
        car_rental_company_discount_plan AS p ON c.car_type = p.car_type
    WHERE
        c.car_type IN ('세단', 'SUV')
        AND p.duration_type = '30일 이상'
)
SELECT
    car_id,
    car_type,
    fee
FROM
    cars_with_fee
WHERE
    car_id NOT IN (
        SELECT
            car_id
        FROM
            car_rental_company_rental_history
        WHERE
            end_date >= '2022-11-01' AND start_date <= '2022-11-30'
    )
    AND fee >= 500000 AND fee < 2000000
ORDER BY
    fee DESC,
    car_type ASC,
    car_id DESC;

SELECT pd.product_code, SUM(os.sales_amount) * pd.price as sales
FROM product as pd
    JOIN
    offline_sale as os
    ON pd.product_id = os.product_id
GROUP BY pd.product_id
ORDER BY sales DESC, pd.product_code ASC

문제에서 요구한 결과를 만들기 위해 쿼리에서 실제로 쓴 것들이다.

• 테이블 1회 JOIN 으로 두 테이블을 연결
• GROUP BY 로 묶어서 집계
• ORDER BY 로 정렬 (내림차순 포함)
• 사용한 함수: SUM()

INF = float('inf')

n = int(input())

total_sum = 0
select_idx = []
for _ in range(n):
    L = int(input())
    jewels = list(map(int, input().split()))

    cum_sum = [0] * (L + 1)
    for i in range(1, L + 1):
        cum_sum[i] += cum_sum[i - 1] + jewels[i - 1]
    
    max_sum = -INF
    max_start = 1
    max_end = L

    for k in range(L, 0, -1):
        is_max = False
        for i in range(0, L - k + 1):
            part_sum = cum_sum[k + i] - cum_sum[i]
            if max_sum > part_sum:
                continue
            # 최대값과 같고 이미 앞에서 그 값이 나온 경우
            elif max_sum == part_sum and is_max:
                continue

            max_sum = part_sum
            max_start = i + 1
            max_end = k + i
            is_max = True
    total_sum += max_sum
    select_idx.append((max_start, max_end))


print(total_sum)
for result in select_idx:
    print(*result)

cur_num = 2
stack = [1]
result = ['+']
for _ in range(n):
    target = int(input())
    if not stack:
        stack.append(cur_num)
        result.append('+')
        cur_num += 1
    while stack and stack[-1] < target:
        stack.append(cur_num)
        result.append('+')
        cur_num += 1
    if stack and stack[-1] == target:
        stack.pop()
        result.append('-')
        continue

if len(stack) == 0:
    print(*result, sep='\n')
else:
    print('NO')

N, M = map(int, input().split())

dxy = ((1, 2), (2, 1), (-1, 2), (2, -1), (1, -2), (-2, 1), (-1, -2), (-2, -1))

x, y = map(int, input().split())

enemy_list = [tuple(map(int, input().split())) for _ in range(M)]
result = [0] * M

q = deque([(x, y, 1)])
find_cnt = 0
visited = set([(x, y)])

while q:
    cur_x, cur_y, t = q.popleft()

    for dx, dy in dxy:
        n_x, n_y = cur_x + dx, cur_y + dy
        if (n_x, n_y) in visited:
            continue
        if (n_x, n_y) in enemy_list:
            enemy_idx = enemy_list.index((n_x, n_y))
            if result[enemy_idx] != 0:
                continue
            result[enemy_idx] = t
            find_cnt += 1
        if find_cnt == M:
            break
        q.append((n_x, n_y, t + 1))
        visited.add((n_x, n_y))
    else:
        continue
    break

print(*result)

-- 사번, 성명, 평가 등급, 성과금
-- 부서 정보
-- 사원 정보
-- 사원 평가 정보
WITH avg_emp AS (
    SELECT he.emp_no, he.emp_name, he.sal,
        CASE
            WHEN AVG(hg.score) >= 96 THEN 'S'
            WHEN AVG(hg.score) >= 90 THEN 'A'
            WHEN AVG(hg.score) >= 80 THEN 'B'
            ELSE 'C'
    END AS grade
    FROM hr_employees AS he
        LEFT JOIN
        hr_grade AS hg
        ON he.emp_no = hg.emp_no
    GROUP BY he.emp_no
)

SELECT emp_no, emp_name, grade,
    CASE
        WHEN grade = 'S' THEN sal * 0.2
        WHEN grade = 'A' THEN sal * 0.15
        WHEN grade = 'B' THEN sal * 0.1
        WHEN grade = 'C' THEN 0
        ELSE NULL
    END AS bonus
FROM avg_emp
ORDER BY emp_no

WITH quarter AS (
    SELECT 
        CASE
            WHEN(QUARTER(differentiation_date)) = 1 THEN '1Q'
            WHEN(QUARTER(differentiation_date)) = 2 THEN '2Q'
            WHEN(QUARTER(differentiation_date)) = 3 THEN '3Q'
            WHEN(QUARTER(differentiation_date)) = 4 THEN '4Q'
            ELSE NULL
    END AS quarter
    FROM ecoli_data
)

SELECT quarter, COUNT(*) AS ecoli_count
FROM quarter
GROUP BY quarter
ORDER BY quarter

문제에서 요구한 결과를 만들기 위해 쿼리에서 실제로 쓴 것들이다.

from collections import Counter, defaultdict

input = sys.stdin.readline
print = sys.stdout.write

N = int(input())

students = [int(input()) for _ in range(N)]
MAX_STUDENT = max(students) + 1

counter = Counter(students)
toktok = defaultdict(int)

for i in range(1, MAX_STUDENT):
    for j in range(i, MAX_STUDENT, i):
        if j in counter:
            toktok[j] += counter[i]

print("\n".join(str(toktok[s] - 1) for s in students) + "\n")