[250613] QCC 7회차

문제 1.

 

• 내 오답
  • 틀린 이유 : 아니.... 멍청아 테이블명 안봐?????? page_id가 어디있니.. pages잖니....

select i.page_id
from page_id as i left join page_likes as l on i.page_id = l.page_id 
where l.page_id is null 
order by i.page_id asc

• 정답

select p.page_id
from pages as p left join page_likes as l on i.page_id = p.page_id 
where l.page_id is null 
order by p.page_id asc

 

문제 2.

• 내 오답

select ticker,
       if max(open) then datetime(date, 'YYYYMM'), 
       max(open) as highest_open, 
       if min(open) then datetime(date, 'YYYYMM'),
       min(open) as lowest_open
from stock_prices 
where open is not null 
group by ticker 
order by ticker asc

• 정답

WITH monthly_data AS (
    SELECT
        ticker,
        DATE_FORMAT(date, '%Y%m') AS month_year,
        open
    FROM qcc.stock_prices
    WHERE open is not null
), ranked_data AS (
    SELECT
        ticker,
        month_year,
        open,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open DESC) AS rank_high,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open ASC) AS rank_low
    FROM monthly_data
)
SELECT
    ticker,
    MAX(CASE WHEN rank_high = 1 THEN month_year END) AS highest_mth,
    MAX(CASE WHEN rank_high = 1 THEN open END) AS highest_open,
    MAX(CASE WHEN rank_low = 1 THEN month_year END) AS lowest_mth,
    MAX(CASE WHEN rank_low = 1 THEN open END) AS lowest_open
FROM ranked_data
GROUP BY ticker
ORDER BY ticker

 

문제 3. 

• 정답

WITH running_time AS (
  SELECT
    server_id,
    session_status,
    status_time,
    LEAD(session_status) OVER (PARTITION BY server_id ORDER BY status_time) AS next_status,
    LEAD(status_time) OVER (PARTITION BY server_id ORDER BY status_time) AS next_time
  FROM qcc.server_utilization
)
SELECT
  FLOOR(SUM(TIMESTAMPDIFF(SECOND, status_time, next_time) * 1.0 / 86400)) AS total_uptime_days
FROM running_time
WHERE session_status = 'start'
  AND next_status = 'stop'